将子函数名称作为函数中的参数传递

I have a userform that has a listbox of sheet names in the workbook and has three buttons: one deletes all worksheets selected in the listbox, one just clears those sheets, and the other removes charts from that sheet. I currently have three sub functions that look nearly identical, as each just runs a single subfunction on a loop, based on the selected button:

我有一个userform,它在工作簿中有一个工作表名称列表框,有三个按钮:一个删除列表框中选中的所有工作表,一个只清除那些工作表,另一个删除该工作表中的图表。我目前有三个看起来几乎完全相同的子函数,因为每个函数只根据所选按钮在循环上运行一个子函数:

Sub DeleteSheets() 'Removes sheets selected by user in ListBox

    For k = 0 To sheetNameListBox.ListCount - 1      
     If sheetNameListBox.Selected(k) = True Then     
      DeleteSheet sheetNameListBox.List(k)           
     End If
    Next k

End Sub

Sub ClearSheets() 'Clears sheets selected by user in ListBox

    For k = 0 To sheetNameListBox.ListCount - 1       
     If sheetNameListBox.Selected(k) = True Then      
      ClearSheet sheetNameListBox.List(k)              
     End If
    Next k

End Sub

Sub DeleteCharts() 'Removes charts from sheets selected by user

    For k = 0 To sheetNameListBox.ListCount - 1   
     If sheetNameListBox.Selected(k) = True Then  
      DeleteChart sheetNameListBox.List(k)        
     End If
    Next k

End Sub

To solve this problem, I decided to develop a subfunction like this:

为了解决这个问题,我决定开发一个这样的子功能:

Sub RemoveSheetData(removeType As String)

        For k = 0 To sheetNameListBox.ListCount - 1   
         If sheetNameListBox.Selected(k) = True Then  
          If removeType = "DeleteSheet" Then DeleteSheet sheetNameListBox.List(k)
          If removeType = "ClearSheet" Then ClearSheet sheetNameListBox.List(k)
          If removeType = "DeleteChart" Then DeleteChart sheetNameListBox.List(k)
         End If
        Next k

End Sub

This seems also to be inefficient as I have to have three cases for each. Is there a way that I can directly input the name of the function I wish to call, and have it run?

这似乎也是低效的,因为我必须为每个案例分三个案例。有没有办法可以直接输入我想要调用的函数的名称,并让它运行?

1 个解决方案

#1

Here is an answer blatantly stolen from here

这是从这里公然偷走的答案

Sub test() 
    Dim ftnName As String 
    Dim argument As String 
    Dim result As String 

    ftnName = "myFunction" 
    argument = "cat" 

    result = Application.Run(ftnName, argument) 

    MsgBox result 
End Sub 

Function myFunction(inString As String) As String 
    myFunction = inString & " has " & Len(inString) & " letters." 
End Function