vba 从两个不同的 VB (excel) 模块调用子函数/模块

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

提问by Asher

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 DONTwant to have to do this or do I??

我DONT希望有做到这一点还是我？

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?

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

回答by Jon Crowell

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

不，您不必这样做，您可以从任何地方调用您的函数。

Try this:

尝试这个：

Put this code in Module1:

将此代码放在模块 1 中：

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

And this in Module2:

这在模块 2 中：

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.

编辑：我看到来自不同模块的调用仍然不适合你。完全按照这些步骤来设置一个测试工作簿，它应该可以帮助您理解问题。

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

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

7. Paste this sub into CallMe:

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

8. Paste this into CallMeBack:

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

9. Paste this into CallMeAgain:

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

10. Press F5 to run the code from within CallMe. You should see the following messagebox:

11. Run the code from any of the 3 "Call" modules and you should see the corresponding messagebox.

1. 创建一个新的 Excel 工作簿
2. 打开 VBA 编辑器 (Alt-F11)
3. 右键单击项目并选择插入模块。重复此 4 次以获得 4 个模块。
4. 如果尚未打开，请按 F4 打开属性窗口
5. 将您的模块名称更改为以下内容：CallMe、CallMeAgain、CallMeBack、Validation

6. 在验证模块中，粘贴以下函数：

   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
   

7. 将此子粘贴到 CallMe 中：

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

8. 将其粘贴到 CallMeBack 中：

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

9. 将其粘贴到 CallMeAgain 中：

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

10. 按 F5 从 CallMe 中运行代码。您应该看到以下消息框：

11. 从 3 个“调用”模块中的任何一个运行代码，您应该会看到相应的消息框。

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

我从 Tim Williams ( https://stackoverflow.com/a/6688482/138938)那里得到了 SheetExists 函数并一直使用它。

回答by Hugh Dempsey

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。在普通模块中声明的函数具有一般作用域。

回答by prime135

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

另外，如果你碰巧用下划线命名你的子程序，VBA 不喜欢它。

"Subroutine_Name" won't work, but

“Subroutine_Name”不起作用，但是

"SubroutineName" will work.

“子程序名称”将起作用。

回答by Asher

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() 而不是 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")

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

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