从两个不同的VB（excel）模块调用子功能/模块

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

我有一个函数，我想从各种模块调用。什么是在VB（excel）中执行此操作的最佳方法。

module "SheetExists"

模块“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?

这是从另一个模块调用函数的唯一方法吗？我是否必须将其声明为Public函数或其他内容？

4 个解决方案

#1

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

不，你不必这样做，你可以从任何地方调用你的功能。

Try this:

尝试这个：

Put this code in Module1:

将此代码放在Module1中：

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

And this in Module2:

这在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
创建一个新的Excel工作簿
Open the VBA Editor (Alt-F11)
打开VBA编辑器（Alt-F11）
Right-click on the project and select insert module. Repeat this 4x to get 4 modules.
右键单击项目，然后选择插入模块。重复此4x以获得4个模块。
Press F4 to open the properties window, if it isn't already open
按F4打开属性窗口（如果尚未打开）
Change your module names to the following: CallMe, CallMeAgain, CallMeBack, Validation
将模块名称更改为以下内容：CallMe，CallMeAgain，CallMeBack，验证

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:

将此子句粘贴到CallMe中：

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

Paste this into CallMeBack:

将其粘贴到CallMeBack中：

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

Paste this into CallMeAgain:

将其粘贴到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:

按F5键从CallMe中运行代码。您应该看到以下消息框：
Run the code from any of the 3 "Call" modules and you should see the corresponding messagebox.

从任何3个“呼叫”模块运行代码，您应该看到相应的消息框。

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

我从Tim Williams（https://*.com/a/6688482/138938）获得了SheetExists功能，并且一直使用它。

#2

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

在类模块中声明的函数必须以类名开头，例如class.function。在普通模块中声明的函数具有一般范围。

#3

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

问题是excel没有说明函数具有全局范围。

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

AND模块名称不能与函数名称相同（显然）。

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

看来excel VB会让你从任何其他模块调用一个函数，只要模块名称与任何其他函数名称不相似，有效地赋予所有函数全局范围......？！？这与大多数编程语言非常不同，因为通常你调用模块（或类）.function（）而不是函数（）。 excel中的所有函数都具有全局范围，这是真的吗？那有点不同......

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

模块“SheetChecker”（名称不能等于函数名）

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"

模块“anyOtherModule”

SheetExists("mysheet")

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

注意模块中声明的函数（子块的外部）在VB Excel中具有全局范围（似乎）。

#4

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

此外，如果您碰巧用下划线命名您的sub，VBA不喜欢它。

"Subroutine_Name" won't work, but

“Subroutine_Name”不起作用，但是

"SubroutineName" will work.

“SubroutineName”将起作用。

#1