1

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

Community
  • 1
  • 1
Absinthe
  • 3,258
  • 6
  • 31
  • 70
  • Works fine for me. Is the file path a network path? – PankajR Feb 23 '16 at 13:31
  • It is, but I've just tried with a folder on the C: drive & still the same problem. Oddly enough it's not just my current place of work, I've never managed to do this. I think I'm jinxed! – Absinthe Feb 23 '16 at 14:02

3 Answers3

3

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
Community
  • 1
  • 1
marg
  • 2,817
  • 1
  • 31
  • 34
  • Thanks for the reply. I wrote directly onto the webpage rather than copied from Excel so no autocorrect. It's pseudo-code for something I've tried hundreds of times over the years. I've never tried specifying the class (Excel.Workbook) until now but that didn't help. – Absinthe Feb 23 '16 at 13:54
  • I've checked the non-standard add-ins & there are no modules etc like that. All the others are Microsoft (e.g analysis tool pack) – Absinthe Feb 23 '16 at 14:00
  • Ok that explains the lowercase problem. The code you posted works for me (using Excel 2010). I rarely used ByRef in VBA since Objects are always passed by reference. The parameter just defines if the reference is passed by reference or by value [more information here](https://msdn.microsoft.com/en-us/library/ddck1z30.aspx). The compile error should highlight a specific row. I assume it's the definition of SubTwo. Is that correct? – marg Feb 23 '16 at 14:09
  • Also please try replacing `Call SubTwo(wb)` with just `SubTwo wb` – marg Feb 23 '16 at 14:18
  • The compiler highlights the object name in the arguments of the calling sub, in this example the "wb" of Call SubTwo(wb). If I remove the ByRef specifier from SubTwo I get exactly the same error. If I pass ByVal it works so it's not a game breaker, but it is frustrating! I'm using Excel 2007. – Absinthe Feb 23 '16 at 14:24
  • "Also please try replacing Call SubTwo(wb) with just SubTwo wb" Yes - that did it! Many thanks, I can finally put this one to rest after all these years :) Any idea why that works?! Please post as an answer if you like. – Absinthe Feb 23 '16 at 14:26
  • In general I try to avoid using the Call keyword in VBA since it is unnecessary [more information](http://stackoverflow.com/questions/2573597/should-i-use-call-keyword-in-vb-vba). The problem is probably related to the fact that in VBA you can use parentheses to evaluate values. For example `SubTwo wb`works fine but `SubTwo (wb)` should give you an error. I will update my answer :) – marg Feb 23 '16 at 14:33
0

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
Stefan Schulz
  • 533
  • 3
  • 8
-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.

FelixSFD
  • 6,052
  • 10
  • 43
  • 117
Rajiv Manickam
  • 1