CODEWITHSUNDEEP
excelpass-by-referencebyrefvba
Absinthe
Absinthe

Reputation: 3391

Excel VBA pass object ByRef never sems to work

I've been using VBA for many years but in all that time I've never managed to pass a workbook or sheet ByRef, I've had to use string names & set the objects in the partner sub or function... so finally it's time to get some help!

Sub SubOne()

Dim wb as workbook
Dim filepath as string

filepath = "//somepath/somebook.xlsx"

Set wb = application.workbooks.open(filepath)

Call SubTwo(wb)

End Sub



Sub SubTwo(ByRef wb as workbook)

debug.print wb.name

End Sub

Can anyone see why this would trigger a ByRef type mismatch compile error? What am I missing?

Many thanks

Upvotes: 1

Views: 8447

Answers (3)

Rajiv Manickam
Rajiv Manickam

Reputation: 1

I had the same issue. After lot of try and fail, I added option Explicit on top. When I executed the code it showed me that the declaration of the Worksheet variable had a name mismatch with the variable which was passed. That is, shtFSheet was declared and strFSheet was passed. Changing this solved my problem. Hope this helps somebody.

Upvotes: -1

marg
marg

Reputation: 2827

You can avoid problems like these by not using the Call Keyword. Instead of Call SubTwo(wb) use SubTwo wb

Related information: Should I use Call keyword in VB/VBA?

Your original code worked for me but there might have been minor differences in white space or parentheses that caused the problem. VBA uses parentheses not only to pass arguments to subs / functions but also to evaluate data.

Another point to mention is that ByVal and ByRef should both work for what you are trying to do since Objects are always passed by reference in VBA. ByVal / ByRef only define if the reference itself is passed by value or reference: https://msdn.microsoft.com/en-us/library/ddck1z30.aspx

I want to leave my previous answer here because it is still a valid answer for the posted error message and might help someone in search of a solution.

Previous answer:

My guess is that one of your loaded AddIns is using a Module, ClassModule, Enum etc. named workbook and this causes the compile error.

If you look at your code you will also see that workbook is written lowercase. Usually the VBA Editor would autocorrect this to Workbook unless some other type name is interfering.

To avoid this replace workbook with Excel.Workbook and please try again.

Your code should then look like this:

Sub SubOne()

Dim wb as Excel.Workbook
Dim filepath as string

filepath = "//somepath/somebook.xlsx"

Set wb = application.workbooks.open(filepath)

Call SubTwo(wb)

End Sub



Sub SubTwo(ByRef wb as Excel.Workbook)

debug.print wb.name

End Sub

Upvotes: 3

Stefan Schulz
Stefan Schulz

Reputation: 529

This works for me:

Sub SubOne()

 Dim wb as workbook
 Set wb = This.ActiveWorkbook
 Call SubTwo(wb)
End Sub

Sub SubTwo(ByRef wb As Workbook)

 Debug.Print(wb.Name)
End Sub

Upvotes: 0

Related Questions