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
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.
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