+ Reply to Thread
Results 1 to 9 of 9

Dynamic macro name / Concatenating macro name / Call a string variable as a macro name

  1. #1
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Question Dynamic macro name / Concatenating macro name / Call a string variable as a macro name

    Hi All

    I have a workbook containing some SLA measures & a user form.
    Depending on what options the users picks I'd like to build/concatenate the a macro name to be called

    I've managed to build the macro name & store it in a variable
    But I can't figure out how to call it?

    For instance the macro name is M10, and I have confirmed it is stored in variable 'MacroName' by using a MsgBox to display the contents

    When I use 'Call M10' it executes no problem. But when I call MacroName.Value I get an error - 'Compile error: Invalid qualifier?

    Any help appreciated
    Regards
    Vicky

    Please Login or Register  to view this content.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,513

    Re: Dynamic macro name / Concatenating macro name / Call a string variable as a macro name

    Hi vwhite,

    Read to the bottom of this link and tell us if it works for you.
    https://www.daniweb.com/programming/...name-in-vb-net
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: Dynamic macro name / Concatenating macro name / Call a string variable as a macro name

    Oh gosh, I don't know anything about classes, most of that goes right over my head. But I'm going to go away and swat up, and come back to you if & when I get stuck. Let's be honest, when I get stuck not if! Thank you, I'll report back soon

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Arrow Re: Dynamic macro name / Concatenating macro name / Call a string variable as a macro name


    Hi,

    just use CallByName function rather than Call statement, to see in VBA help …

  5. #5
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: Dynamic macro name / Concatenating macro name / Call a string variable as a macro name

    Hi Marc

    I'm still getting an invalid qualifier error on the call line

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: Dynamic macro name / Concatenating macro name / Call a string variable as a macro name

    Hi Marvin

    So going from that link you provided I've copied my macro to a class module:

    Please Login or Register  to view this content.

    Then I've tried to call sub M10 with the following code:

    Please Login or Register  to view this content.
    But I'm getting a compile error - user defined type not defined?

    Sorry, I'm really not sure what I'm doing with classes. Any help would be appreciated

  7. #7
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: Dynamic macro name / Concatenating macro name / Call a string variable as a macro name

    Hi All

    I figured it might be helpful to upload an example workbook

    So the idea is the user will select an item from the drop down lists in cells C10:C19
    Depending on what options they choose, a macro name will be compiled, stored in a variable and then called

    It's calling the variable where I'm falling down

    Originally I had the majority of the code in the worksheet (sheet8) rather than a module, as it's being triggered by a Worksheet_Change event. But this post on StackExchange would suggest I need to move my code to a module and use Application.Run instead of Call
    https://stackoverflow.com/questions/...-with-a-string

    So I moved my code to a module. Called the sub 'MacroSelector' and called that from Sheet8 instead

    That bit works. But I'm still getting an error when I run MacroSelector from a module?

    Macro.png

    Any help on how to get round this would be massively appreciated
    Thanks all
    Attached Files Attached Files

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,513

    Re: Dynamic macro name / Concatenating macro name / Call a string variable as a macro name

    Hi vwhite and I have an answer.

    This problem reminded me of the old Excel VBA and Access VBA where trial and error and error and error... was the method of discovery.
    CallByName wanted an Object as the first parameter and I tried everything before trying ThisWorkbook as the Object. Then it seemed to work.. No Idea Why..

    See the attached below where you can click the dropdowns in Col C and it will run the code you want.

    CallByName Example from Validation Update.xlsm

  9. #9
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: Dynamic macro name / Concatenating macro name / Call a string variable as a macro name

    Marvin you're a super star! Thank you so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using string variables to call another macro
    By Phillip2 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-04-2019, 03:05 PM
  2. [SOLVED] Define variable in macro than calling that macro inside another macro
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-21-2015, 10:58 AM
  3. Replies: 1
    Last Post: 08-13-2014, 05:46 PM
  4. [SOLVED] Help with Macro to Call Excel File with Variable Names
    By John_Day83 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2013, 05:10 AM
  5. Call Macro from Workbook Variable
    By samshut in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2007, 01:03 PM
  6. [SOLVED] Call VBScript from VBA, pass variable, return result to macro?
    By Ed in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 07-11-2006, 02:15 PM
  7. Define worksheet as variable, then call in macro
    By bugman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2005, 01:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1