"ByRef argument type mismatch" error

[Robert Crandal]

I get the above stated compile error when I run the following code:

Dim myfile as String
myfile = "Book1.xlsm"

If Module1.IsWbOpen (myfile) Then
' Do stuff
End If

------------------[ IsWbOpen() is in Module1]--------------------

Public Function IsWbOpen(WbName As String) As Boolean
For Each wb In Application.Workbooks
If wb.Name = WbName Then
IsWbOpen = True
Exit For
End If
Next
End Function
--------------------------------------------------------------------


Does anybody know what the heck is wrong here??? I know i am
definitely passing a String variable into "IsWbOpen()", yet I am
seeing a compile error.

Thank you!

 

[OssieMac]

Hi Robert,

I can't fault the code. It works perfectly. What line does the code stop on
when you get the error? You did copy your code from your project and not
re-write it for this post didn't you?

The error msge suggests that myfile is incorrectly declared as something
other than a string.

 

[joel]

The fix is to add ByVal to the subroutinge declaration. The cod
works without the change on My PC with 2003. You omust have a differen
option checked in your PC or using a different version of Excel thenI'
using.

From:

Public Function IsWbOpen(WbName As String) As Boolean

To

Public Function IsWbOpen(Byval WbName As String) As Boolean



The default declaration of a function is ByRef which means if th
variable is changed in the Calling subroutine the change will be seen b
the called function. like this

Sub Main()
Dim MyName As String
MyName = "Joel"
Call ChangeName(MyName)
MsgBox (MyName) 'this will pring Robert
End Sub

Sub ChangeName(Person As String)
Person = "Robert"
End Su