Calling a function with a variable

[JackDanIce]

Hi,

Overview: Macro to open various data source files and create CSV sheets to independently save down
Process: Open source files, extract data, close file

Problem: Some source files contain data not needed or after extracting data, post process is required.

For this example, Timesheet is sheet name and function specific for it is:

Public Function Pre_Timesheet(ByRef wks As Worksheet) As Worksheet
'code to remove unwanted data before importing
End Function

All sheets that require a pre step, I name their specific function (or sub) Pre_<sheet name> (similarly for any sheets requiring post import work, Post_<name>).

In my code, I have

Const PRE_ as String = "Sheet1|Sheet2|Sheet3"
If Instr(PRE_, wks.Name) Then
    Str = Replace("Pre_@1 (wks)", "@1", wks.name)
    Set wks = Application.Run Str
End If

Line in red errors: "Cannot run the macro 'Pre_Timesheet (sWks)'. The macro may not be available in this workbook or.."

Tried searching online, came across Application.Run, CallByName but I can't get them to work for my requirement.

Can anyone suggest how I to indirectly call a function with required argument for example above?

Thank you in advance,
Jack

 

[wideboydixon]

Const PRE_ as String = "Sheet1|Sheet2|Sheet3"
If Instr(PRE_, wks.Name) Then
    Str = Replace("Pre_@1", "@1", wks.name)
    Set wks = Application.Run Str, wks
End If

Arguments are passed separately I believe.

WBD

 

[JackDanIce]

Thanks WBD, got it to work, it was as you suspected, needing to pass arguments separately.

 

[sbp1975]

I am having trouble following this...
I think you are looking to see if the Worksheet name is in the PRE_ constant

If Instr(PRE_, wks.Name) Then

and if it is, then I dont follow what Replace is doing - are you searching some String named Str to replace parts with variable names? Just replacing the '1' in PRE_ with the Worksheet Name? I assume you are putting in the whole string of what procedure to run dynamically...

Str = Replace("Pre_@1", "@1", wks.name)

Then you are setting the worksheet to be the worksheet after the macro in Str was run?

Set wks = Application.Run Str, wks

I am trying to create a workbook from a prototype, then run a macro (called ValidatedLists) to set Data Validation dropdowns of dynamic length for various cells within the created workbook (subject of another future post on how to best do this dynamically)

' ... update Data Validation dropdown lists
  ' trying to use a string where MacroNameToRun & WbName are defined Strings
    MacroNameToRun = WbName & ".xlsm!ValidatedLists"
  Application.Run MacroNameToRun  ' still gives an error message that the macro is not found in the created workbook

     ' Call ValidatedLists  ' wont work because the macro is not in the calling workbook, it is in the dynamically created target workbook nwb, so I need to call it from the created workbook at runtime
     ' Application.Run ("'" & WbName & "'!" & "ValidatedLists")  ' dont know how to get the right escape keys to include single quotes?

  CallByName nwb, ValidatedLists, VbMethod  ' does not work using Workbook nwb
  CallByName WbName, ValidatedLists, VbMethod  ' does not work using String WbName - it is supposed to be a Variant for first input...

I get an error message with CallByName, as well, because I cant figure out how to get the WbName String into a Variant variable - if I start with WbName as a Variant, it wont work to create the Workbook with that variable name

 

[RoryA]

Application.Run "'" & WbName & "'!ValidatedLists"

should work assuming wbname is the correct workbook name, including extension, and that ValidatedLists is not also the name of a module.

 

[JackDanIce]

@sbp1975 I'm working on a project for a company that imports data and creates 6 output sheets, that are then individually saved as csv files for upload to a new system.

You may be struggling with the code because I stripped out anything non needed (there are 5 modules in total Mod_Main, Mod_Common, Mod_CreateOutput, Mod_Pre and Mod_Post) which I'm not going to copy all out and paste here, just enough to explain the code issue I had and hopefully enough info to get a fix, as @wideboydixon was able to suggest and resolve (Thank you again!).

My macro workbook (ThisWorkbook) basically follows this pattern: Open source file, set up a map of headers (source to destination), import data save sheet

Some of the data imports need pre-import work (e.g. delete rows) and one needs post work by inserting rows and transposing some weekly column data into weekly row data.

As I try to use general functions and procedures as much as possible (to reduce the amount of written code in a project), then

If Instr(PRE_, wks.Name) Then

correctly identifies if a sheet needs pre or post work and then if it does, I've given those proecdures specific names with pre or post fix "PRE_" or "POST_"

If sheet name is "Budget1" then

Str = Replace("Pre_@1", "@1", wks.name)

runs macro "Pre_Budget1" from ThisWorkbook so I don't need to specify "ThisWorkbook" like Rory suggests you do here

"'" & WbName & "'!ValidatedLists"

In your case, I believe the macro's are in a different workbook to the workbook that's calling it so specification was needed.

Finally, for this macro project, all my Pre_ and Post_ are actually functions that return a worksheet object, that's why I'm using

Set wks = Application.Run Str, wks

So for "Budget1", the UDF is:

Public Function Pre_Budget1(Byref wks as worksheet, byref col1 as Long, byref col2 as Long) as worksheet
Dim x as Long
Dim y as Long
'some code
Set Pre_Budget1 = wks
End Function

Which returns a worksheet object back to Set wks =

Hope that explains

 

[JackDanIce]

runs macro "Pre_Budget1" from ThisWorkbook so I don't need to specify "ThisWorkbook" like Rory suggests you do here

Meaning Rory's comment is specific for your requirement, my macro file is calling the macro I need with Appication.Run str so didn't need to specifiy it (based on VBA object hirearchy) for my situation.

 

[sbp1975]

Thanks Rory, I must have been missing an apostrophe - I got it to work now.
I will need to further modify the macro to use a named range in a Validated List, so will post a separate thread to ask about how I could do this...