从两个不同的VB（excel）模块调用子功能/模块 [英] Calling a sub Function / Module from two different VB (excel) modules

问题描述

我有一个我想从各种模块调用的功能。在VB（excel）中最好的方式是什么。

模块SheetExists

 函数名称（SheetName As String）As Boolean 
'如果工作表存在于活动工作簿中，则返回TRUE 
 SheetExists = False 
错误GoTo NoSuchSheet 
如果Len（Sheets（SheetName）.Name）> 0然后
 SheetExists = True 
退出函数
结束如果
 NoSuchSheet：
结束函数
  

模块主

 如果不是SheetExists.Name（mySheet）然后
'这样做
 Else 
'否则这个
结束If 
  

我 DONT 想要这样做还是要做

  Call SheetExists.Name（mySheet）
  

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

解决方案

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

尝试这样：

将此代码放在Module1中：

  Sub TestSheetExists（）
如果SheetExists（Sheet1）然后
 MsgBox我存在！ 
 End If 
 End Sub 
  

这在Module2中：

 功能SheetExists（shtName As String，可选wb作为工作簿）As Boolean 
 Dim sht As Worksheet 
 
如果wb是没有，然后设置wb = ThisWorkbook 
错误恢复下一个
设置sht = wb.Sheets（shtName）
错误GoTo 0 
 SheetExists =不sht是没有
结束功能
  

显然，您可以使用任何您想要的模块名称。

---

编辑：我看到来自不同模块的通话仍然不适合您。请按照以下步骤设置测试工作簿，以帮助您了解问题。

1. 创建新的Excel工作簿


2. 打开VBA编辑器（Alt-F11）


3. 右键单击项目并选择插入模块。重复此4x以获取4个模块。


4. 按F4打开属性窗口（如果尚未打开）


5. 将模块名称更改为以下内容：CallMe，CallMeAgain，CallMeBack，验证
   



6. 在验证模块中，粘贴以下功能：

    功能SheetExists（shtName As String，可选wb As工作簿）As Boolean 
    Dim sht As Worksheet 
    
   如果wb是Nothing然后设置wb = ThisWorkbook 
    On Error Resume Next 
   设置sht = wb.Sheets（shtName） 
   错误GoTo 0 
    SheetExists =不是sht是没有
   结束函数
     




  Sub TestSheetExistsFromCallMe（）
如果SheetExists（Sheet1 ）然后
 MsgBox我存在，我从CallMe呼叫！ 
如果
 End Sub 
  




7. 将其粘贴到CallMeBack中： / p>
   
   

     Sub TestSheetExistsFromCallMeBack（）
   如果SheetExists（Sheet1）然后
    MsgBox我存在，而我来自CallMeBack！ 
   如果
    End Sub 
     




8. 将其粘贴到CallMeAgain中： / p>
   
   

     Sub TestSheetExistsFromCallMeAgain（）
   如果SheetExists（Sheet1）然后
    MsgBox我存在，而我来自CallMeAgain！ 
   如果
    End Sub 
     




9. 按F5运行代码来自CallMe。您应该看到以下消息框：
   




10. 从任何3个Call模块运行代码，您应该看到相应的消息框。

我从Tim Williams获得了SheetExists函数（ https://stackoverflow.com/a/6688482/138938），并始终使用它。

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?

解决方案

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.

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.

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

这篇关于从两个不同的VB（excel）模块调用子功能/模块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！