从两个不同的VB(excel)模块调用子功能/模块 [英] Calling a sub Function / Module from two different VB (excel) modules
问题描述
模块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是没有
结束功能
显然,您可以使用任何您想要的模块名称。
编辑:我看到来自不同模块的通话仍然不适合您。请按照以下步骤设置测试工作簿,以帮助您了解问题。
- 创建新的Excel工作簿
- 打开VBA编辑器(Alt-F11)
- 右键单击项目并选择插入模块。重复此4x以获取4个模块。
- 按F4打开属性窗口(如果尚未打开)
- 将模块名称更改为以下内容:CallMe,CallMeAgain,CallMeBack,验证
-
在验证模块中,粘贴以下功能:
功能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是没有
结束函数
-
将其粘贴到CallMeBack中: / p>
Sub TestSheetExistsFromCallMeBack()
如果SheetExists(Sheet1)然后
MsgBox我存在,而我来自CallMeBack!
如果
End Sub
-
将其粘贴到CallMeAgain中: / p>
Sub TestSheetExistsFromCallMeAgain()
如果SheetExists(Sheet1)然后
MsgBox我存在,而我来自CallMeAgain!
如果
End Sub
-
按F5运行代码来自CallMe。您应该看到以下消息框:
-
从任何3个Call模块运行代码,您应该看到相应的消息框。
Sub TestSheetExistsFromCallMe()
如果SheetExists(Sheet1 )然后
MsgBox我存在,我从CallMe呼叫!
如果
End Sub
我从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.
- 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.
这篇关于从两个不同的VB(excel)模块调用子功能/模块的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!