Does anyone have an idea how to return parameter name in VBA?
This is what I have:
Sub Main()
Dim MyString As String
MyString = "Hello World"
MsgBox MyString
End Sub
It shows only "Hello World". I would like to have it "MyString says Hello World", but dynamically, not by entering
MsgBox "MyString says " & MyString
I would prefer something like
MsgBox ParamName(MyString) & " says " & MyString
but it actually won't work... Could anyone help?
I believe I have accomplished what you are looking to do here. However, please note that this will currently only work for your first parameter in a macro assigned to a Form control:
Step 1
Add the following code, adapted from here, to a new Module:
Public Function ExportModules(ModuleName As String) As String
Dim bExport As Boolean
Dim wkbSource As Excel.Workbook
Dim szSourceWorkbook As String
Dim szExportPath As String
Dim szFileName As String
Dim cmpComponent As VBIDE.VBComponent
''' The code modules will be exported in a folder named.
''' VBAProjectFiles in the Documents folder.
''' The code below create this folder if it not exist
''' or delete all files in the folder if it exist.
If FolderWithVBAProjectFiles = "Error" Then
MsgBox "Export Folder not exist"
Exit Function
End If
On Error Resume Next
Kill FolderWithVBAProjectFiles & "\*.*"
On Error GoTo 0
''' NOTE: This workbook must be open in Excel.
szSourceWorkbook = ActiveWorkbook.Name
Set wkbSource = Application.Workbooks(szSourceWorkbook)
If wkbSource.VBProject.Protection = 1 Then
MsgBox "The VBA in this workbook is protected," & _
"not possible to export the code"
Exit Function
End If
szExportPath = FolderWithVBAProjectFiles & "\"
Set cmpComponent = wkbSource.VBProject.VBComponents(ModuleName)
bExport = True
szFileName = cmpComponent.Name
''' Concatenate the correct filename for export.
Select Case cmpComponent.Type
Case vbext_ct_ClassModule
szFileName = szFileName & ".cls"
Case vbext_ct_MSForm
szFileName = szFileName & ".frm"
Case vbext_ct_StdModule
szFileName = szFileName & ".bas"
Case vbext_ct_Document
''' This is a worksheet or workbook object.
''' Don't try to export.
bExport = False
End Select
If bExport Then
''' Export the component to a text file.
cmpComponent.Export szExportPath & szFileName
''' remove it from the project if you want
'''wkbSource.VBProject.VBComponents.Remove cmpComponent
End If
ExportModules = szExportPath & szFileName
End Function
Function FolderWithVBAProjectFiles() As String
Dim WshShell As Object
Dim FSO As Object
Dim SpecialPath As String
Set WshShell = CreateObject("WScript.Shell")
Set FSO = CreateObject("scripting.filesystemobject")
SpecialPath = WshShell.SpecialFolders("MyDocuments")
If Right(SpecialPath, 1) <> "\" Then
SpecialPath = SpecialPath & "\"
End If
If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = False Then
On Error Resume Next
MkDir SpecialPath & "VBAProjectFiles"
On Error GoTo 0
End If
If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = True Then
FolderWithVBAProjectFiles = SpecialPath & "VBAProjectFiles"
Else
FolderWithVBAProjectFiles = "Error"
End If
End Function
Step 2
Add the following code, adapted from answer #7 here, and from here, along with my own function, to a new module (it could be the same module as the first if preferred):
Public Function MyMacroInfo() As String
Dim MacroName$, SubName$, ModArr As Variant
Dim ModName As Object, strModName$, i&, j&
MacroName = ActiveSheet.Buttons(Application.Caller).OnAction
SubName = Application.Replace(MacroName, 1, Application.Search("!", MacroName), "")
ModArr = Array(0, 1, 2, 3)
For Each ModName In ActiveWorkbook.VBProject.VBComponents
For j = LBound(ModArr) To UBound(ModArr)
i = 0
On Error Resume Next
i = ModName.CodeModule.ProcStartLine(SubName, CLng(ModArr(j)))
Err.Clear
If i > 0 Then
strModName = ModName.Name
Exit For
End If
Next j
Next ModName
MyMacroInfo = strModName
End Function
Public Function GetParamName(ModulePath As String) As String
Dim text As String
Dim textline As String
Dim ParamStartLocation As Long
Dim ParamEndLocation As Long
Dim ParamLength As Long
Dim i As Long
Open ModulePath For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
ParamStartLocation = 0
For i = 1 To 3
ParamStartLocation = InStr(ParamStartLocation + 1, text, "Dim ")
Next i
ParamEndLocation = InStr(ParamStartLocation, text, " As ")
ParamLength = ParamEndLocation - ParamStartLocation
GetParamName = Left(Right(text, Len(text) - ParamStartLocation - 3), ParamLength - 4)
End Function
Step 3
Change your sub to the following:
Sub Main()
'--------Leave this section at the top of your sub---------
Dim strExportedModule As String
Dim strParamName As String
strExportedModule = ExportModules(MyMacroInfo)
strParamName = GetParamName(strExportedModule)
'-----------------Start your code here---------------------
Dim MyString As String
MyString = "Hello World"
MsgBox strParamName & " says " & MyString
End Sub
Step 4
Assign Main to a Form Button.
Notes
In the VBE Editor set a reference to "Microsoft Visual Basic For Applications Extensibility 5.3" and to "Microsoft Scripting Runtime" and then save the file.
