Object variable or With block variable not set when returning from function

Question

Why am I receiving an Object variable or With block variable not set error with the following code:

Function GetConnection() As ADODB.Connection
    'Create connection to worksheet
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & "Extended Properties=Excel 8.0;"
    cn.Open
    GetConnection = cn
End Function

I've declared the object as 'cn', initialized it properly, and am then setting some properties and opening it, before returning it.

I get the error at the GetConnection = cn line.

Mike Gasparelli · Accepted Answer · 2013-02-13 05:36:03Z

12

If memory serves me right... you need to use the 'set' keyword when working with reference types (objects) in classic vb

ie:

Set GetConnection = cn

This applies to all assignments, not just function return statements.

answered Feb 13, 2013 at 5:36

Mike Gasparelli

4662 gold badges8 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nightwolf Over a year ago

Perfect, that worked, thank you! I'm a little embarrassed I couldn't figure that out on my own.

Collectives™ on Stack Overflow

Object variable or With block variable not set when returning from function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related