Call a Sub with a String

by

Try this

Replace Call pro with Application.Run pro

Example

Private Sub test_Click()
    Dim i As String
    Dim pro As String

    i = 1
    pro = "sale_call" + i

    '~~> This will run sale_call1
    Application.Run pro

    i = 2
    pro = "sale_call" + i

    '~~> This will run sale_call2
    Application.Run pro
End Sub

Sub sale_call1()
    MsgBox "Hello"
End Sub

Sub sale_call2()
    MsgBox "goodbye"
End Sub

FOLLOWUP

If your code is not in a module but in a Userform or Sheet Code area then Application.Run will not work till the time sale_call1 or sale_call2 is not placed in a module. If you do not wish to move them to a module then you will have to use CallByName. Check Excel’s inbuilt help on this function. Here is an example which assumes that the code is in Userform1

Private Sub CommandButton1_Click()
    Dim i As String
    Dim pro As String

    i = 1
    pro = "sale_call" + i

    '~~> This will run sale_call1
    CallByName UserForm1, pro, VbMethod

    i = 2
    pro = "sale_call" + i

    '~~> This will run sale_call2
    CallByName UserForm1, pro, VbMethod
End Sub

Sub sale_call1()
    MsgBox "Hello"
End Sub

Sub sale_call2()
    MsgBox "goodbye"
End Sub

More Related Contents:

  1. Unicode string literals in VBA
  2. VBA comma separated cells, needt last entry only
  3. How can I find last row that contains data in a specific column?
  4. Open Excel file for reading with VBA without display
  5. Wait for shell command to complete [duplicate]
  6. How to return the number of dimensions of a (Variant) variable passed to it in VBA [duplicate]
  7. How to add a named sheet at the end of all Excel sheets?
  8. Find last used cell in Excel VBA
  9. VBA general way for pulling data out of SAP [closed]
  10. Does Dir() make any guarantee on the order of files returned?
  11. Difference between ‘on error goto 0’ and ‘on error goto -1’ — VBA
  12. Excel: How to check if a cell is empty with VBA? [duplicate]
  13. ShowAllData method of Worksheet class failed
  14. Assign keyboard shortcut to run procedure
  15. Uncheck all checkboxes across entire workbook via CommandButton
  16. return csv file as recordset
  17. VBA workbooks.Close without being prompted to if the user wants to save?
  18. Excel macro -Split comma separated entries to new rows [duplicate]
  19. Get text from clipboard using GetText – avoid error on empty clipboard
  20. Copy a selected range to another worksheet
  21. Benchmarking VBA Code
  22. Excel macro – paste only non empty cells from one sheet to another
  23. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  24. Display milliseconds in Excel
  25. VBA editor auto-deletes spaces at the ends of lines
  26. Application.Quit command not closing the entire Excel Application
  27. Trying to open the workbook in separate instance
  28. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  29. Access files with long paths (over 260)
  30. How to highlight selected text within excel

Leave a Comment