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!

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.

--
Regards,

OssieMac


"Robert Crandal" wrote:

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!


.

The fix is to add ByVal to the subroutinge declaration. The code
works without the change on My PC with 2003. You omust have a different
option checked in your PC or using a different version of Excel thenI'm
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 the
variable is changed in the Calling subroutine the change will be seen by
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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165247

Microsoft Office Help