How to call an Excel VBA function when the function name is in a cell

Question

I have an XLS that retrieves a list of dynamically generated reports:

B | C | D | E | F | G | H | I | J 1 1 | Bob |Jones |bjones| rep 1|Sales Report | Desc.| X | fnGenerateSalesReport 2 1 | Bob |Jones |bjones| rep 2|Revenue Rep. | Desc.| _ | fnGenerateRevenueReport 3 1 | Bob |Jones |bjones| rep 3|Customer List| Desc.| _ | fnGenerateCustReport 4 1 | Bob |Jones |bjones| rep 4|Stock Report | Desc.| _ | fnGenerateStockReport 

The user marks X next to the report (column I) that they want to generate, and clicks the "GENERATE" button. How to get the appropriate function to run based on user selection. The name of the corresponding function (one function for each report) is displayed in a specific column (Col J) in the list of reports (see below).

In the debug.print line debug.print I want it to call a function with a dynamic value contained in c.Offset(0, 1).Value

 Dim ws As Worksheet, rng As Range, stRows As Long Dim c As Range Set ws = Sheets("AVAILABLE REPORTS") Set rng = ws.Range("B12:B12") Set rng = ws.Range(rng, rng.End(xlDown)) stRows = rng.Rows.Count Set rng = ws.Range("I12:I12") Set rng = ws.Range("I12:I" & 11 + stRows) For Each c In rng.Cells If c.Value = "X" Or c.Value = "x" Then Debug.Print "> [" & c.Value & " (" & c.Offset(0, 1).Value & ")]" End If Next 

Answer 1

 For Each c In rng.Cells If c.Value = "X" Or c.Value = "x" Then CallByName myReportGenerator, "GenerateReport", vbMethod, c.Offset(0, 1).Value End If Next 

Note. I assume that myReportGenerator is an instance of a class that contains a GenerateReport method that takes 1 parameter.

EDIT . If it works with you, put the functions inside the sheet (say Sheet1 ).
CallByName Sheet1, c.Offset(0, 1).Value, vbMethod

It is assumed that c.Offset(0, 1).Value contains the method name, which is the public method inside Sheet1 .

EDIT2 . Suppose you put these methods inside a class called class1 . Here is what you will do

 dim reportHelper as Class1 set reportHelper = new Class1 CallByName reportHelper, c.Offset(0, 1).Value, vbMethod 

Answer 2

As an alternative suggestion:

Have you thought about calling the ONE function with the "corresponding function" -name as a parameter?

Then you can simply do a SELECT CASE to call this relevant function, and you can handle input errors if the realevant function name was not working.

 For Each c In rng.Cells If c.Value like "X" Then CallRelevantFunction c.Offset(0, 1).Value End If Next 

CallRelevantFunction may even have several parameters, if necessary.

 CallRelevantFunction ("rep 1", "fnGenerateSalesReport") 

In the end, until you generate your report generation functions dynamically, I would redefine your design approach. Because when the report functions are static, you can only select a specific set of them.

Answer 3

If you have a function name (or sub) stored in a variable, you can use the Run Method - see link

for example, if the variable fncName = ws.Range("J1").value

then you can call the function using

 Application.Run fncName 

If your function / sub arguments require this method to be up to 30. Try specifying the full path to the workbook, module name, and function to avoid potential conflicts. Then you can call functions through any book without fear of conflict.

Answer 4

The EVALUATE function method will probably be useful:

 debug.Print Sheet2.Cells(1,1).Value Day(Now())&Month(Now())&Year(Now()) Debug.Print Evaluate(Sheet2.Cells(1,1).Value) 2792012