CODEWITHSUNDEEP
excelvba
aSystemOverload
aSystemOverload

Reputation: 3074

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

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) they wish to generate and presses a 'GENERATE' button. How do I get the relevant function to run based on the users selection. The name of the relevant function (one function per report) is listed in a specific column (Col J) in the list of reports (see below).

At the debug.print line I want it to call the function with the dynamic value held 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

Upvotes: 4

Views: 5421

Answers (4)

shahkalpesh
shahkalpesh

Reputation: 33476

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 am assuming that myReportGenerator is a instance of a class that holds the method GenerateReport which takes 1 parameter.

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

The assumption is that the c.Offset(0, 1).Value contains the method name which is a public method inside Sheet1.

EDIT2: Assuming you have placed this methods inside a class named class1. Here is what you'll do

dim reportHelper as Class1
set reportHelper = new Class1

CallByName reportHelper, c.Offset(0, 1).Value, vbMethod

Upvotes: 3

Jook
Jook

Reputation: 4682

Just as an alternative suggestion:

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

You could then just make a SELECT CASE to call this relevant function then and you are able to handle input errors, in case the realevant function name was faultive.

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

CallRelevantFunction can even have multiple parameters if necessary.

CallRelevantFunction ("rep 1", "fnGenerateSalesReport")

In the end, as long as you are not creating your report generating functions dynamically, I would reconsider your design approach. Because, when the report functions are static, then you can only choose between a defined set of them.

Upvotes: 2

user857521
user857521

Reputation:

If you have the name of a Function (or a Sub) stored in a variable you can use the Run Method - see this link

eg if variable fncName = ws.Range("J1").value

then you can call the function using

Application.Run fncName

If your function/sub requires arguments this method allows up to 30. Try and give the full path of the workbook, module and function name to avoid any potential conflicts. It's then possible to call functions across any workbook without fear of conflicts.

Upvotes: 1

Kirill Leontev
Kirill Leontev

Reputation: 10931

An EVALUATE function method would probably be of use:

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

Debug.Print Evaluate(Sheet2.Cells(1,1).Value)
2792012

Upvotes: 0

Related Questions