Calling a sub Function / Module from two different VB (excel) modules

Question

I have a function that I want to call from a variety of modules. Whats the best way to do this in VB (excel).

module "SheetExists"

Function Name(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
    End If
NoSuchSheet:
End Function

module "Main"

If Not SheetExists.Name("mySheet") Then
    'do this
Else
    ' else do this
End If

I DONT want to have to do this or do I??

Call SheetExists.Name("mySheet")

Is that the only way to call a function from another module? Do I have to declare it as a Public function or something?

VB must have a way to do this... For example... [link]http://stackoverflow.com/questions/11181075/visual-basic-and-modules — Asher, Jul 13 '12 at 13:38

score 9 · Accepted Answer · edited May 23 '17 at 10:30

No, you don't have to do that, and you can call your function from anywhere.

Try this:

Put this code in Module1:

Sub TestSheetExists()
    If SheetExists("Sheet1") Then
        MsgBox "I exist!"
    End If
End Sub

And this in Module2:

Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

     If wb Is Nothing Then Set wb = ThisWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(shtName)
     On Error GoTo 0
     SheetExists = Not sht Is Nothing
 End Function

Obviously you can use whatever names for your modules you want.

EDIT: I see that calling from different modules still isn't working for you. Follow these steps exactly to set up a test workbook that should help you understand the problem.

Create a new Excel workbook
Open the VBA Editor (Alt-F11)
Right-click on the project and select insert module. Repeat this 4x to get 4 modules.
Press F4 to open the properties window, if it isn't already open
Change your module names to the following: CallMe, CallMeAgain, CallMeBack, Validation

In the Validation module, paste the following function:

Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

     If wb Is Nothing Then Set wb = ThisWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(shtName)
     On Error GoTo 0
     SheetExists = Not sht Is Nothing
End Function

Paste this sub into CallMe:

Sub TestSheetExistsFromCallMe()
    If SheetExists("Sheet1") Then
        MsgBox "I exist, and I was called from CallMe!"
    End If
End Sub

Paste this into CallMeBack:

Sub TestSheetExistsFromCallMeBack()
    If SheetExists("Sheet1") Then
        MsgBox "I exist, and I was called from CallMeBack!"
    End If
End Sub

Paste this into CallMeAgain:

Sub TestSheetExistsFromCallMeAgain()
    If SheetExists("Sheet1") Then
        MsgBox "I exist, and I was called from CallMeAgain!"
    End If
End Sub

Press F5 to run the code from within CallMe. You should see the following messagebox:
Run the code from any of the 3 "Call" modules and you should see the corresponding messagebox.

I got the SheetExists function from Tim Williams (https://stackoverflow.com/a/6688482/138938) and use it all the time.

+ 1 Yes this is a nice way to check. Even I use this method :) — Siddharth Rout, Jul 12 '12 at 20:24
Right... however, what if you want to call the function SheetExists() from another module? — Asher, Jul 12 '12 at 20:40
Put the calling sub in whatever module you want. The sub and the function in my example don't have to be in the same module. I edited my answer to specify that the sub is in one module and the function in another. — Jon Crowell, Jul 12 '12 at 20:49
I edited my answer to give you step-by-step instructions to set up a test workbook that calls the SheetExists function from multiple modules. Let me know if it works for you. — Jon Crowell, Jul 13 '12 at 14:51
Thanks... that worked! :) I didn't realize that all functions have global scope in excel even if they are declared inside a module. — Asher, Jul 13 '12 at 15:35

score 1 · Answer 2 · answered Jan 07 '13 at 13:35

1

Functions declared in class modules must be preceded by the class name, e.g. class.function. Functions declared in ordinary modules have general scope.

answered Jan 07 '13 at 13:35

Hugh Dempsey

11
1

score 1 · Answer 3 · answered Oct 17 '13 at 17:07

1

Also, if you happened to name your sub with underscores, VBA doesn't like it.

"Subroutine_Name" won't work, but

"SubroutineName" will work.

answered Oct 17 '13 at 17:07

prime135

19
1

Asher · Answer 4 · 2012-07-13T15:46:20.383

The problem is that excel doesn't make it clear that functions have global scope.

AND module names cant be the same as function names (obviously).

It appears that excel VB will let you call a function from any other module as long as the module name isn't similar to any other function name effectively giving all functions global scope...?!? this is very different then most programming languages since usually you call the module (or class) .function() rather then just function(). Is it really true that all functions in excel have global scope? Thats kinda different...

module "SheetChecker" (and the name can not be equal to the function name)

Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
    End If
NoSuchSheet:
End Function

module "anyOtherModule"

SheetExists("mysheet")

NOTE functions declared in modules (ouside of sub blocks) have global scope in VB Excel (it seems).

Functions can be private. Private Function SheetExists can only be called from within the same module. Here's a good article on Excel VBA scope: http://www.cpearson.com/excel/scope.aspx — Jon Crowell, Jul 13 '12 at 15:37

Calling a sub Function / Module from two different VB (excel) modules

4 Answers4

Linked