Call a VBA sub using a string value [duplicate]

Question

This is my test code

Sub dotask()

    Dim qusub As String
    qusub = Worksheets("Task List").Range("C2").Value

    MsgBox qusub
    Application.Run qusub

End Sub

Sub msg1()

    MsgBox "sub msg1"

End Sub

Sub msg2()

    MsgBox "sub msg2"

End Sub

Sub msg3()

    MsgBox "sub msg3"

End Sub

Sub msg4()

    MsgBox "sub msg4"

End Sub

All of this is contained in a single standard module. I've read Trying to call a Sub with a String - VBA and wrote my code according to what I found there (i.e. using Application.Run). Cell C2 of Task List worksheet contains "msg3" at the moment. When I execute sub "dotask" I first get a message box saying "msg3" as I want but then I get the following error message:

Run-time error '1004':

Cannot run the macro 'msg3'. The macro may not be available in this workbook or all macros may be disabled.

I'm working on Excel 2010 and the file is .xlsm - what should I do to get my code to execute as I want it?

Yes, all of this is contained in a single module and it is the only module in this file — jacek_wi
– jacek_wi, Commented Jun 10, 2015 at 10:47
See this previous question: stackoverflow.com/questions/19845944/… — Rory
– Rory, Commented Jun 10, 2015 at 11:54

tea_pea · Accepted Answer · 2015-06-10 10:49:20Z

3

just ran it over here. msg1 seems to be a reserved word... change it to something else and it works fine =)

answered Jun 10, 2015 at 10:49

tea_pea

1,54015 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick McDonald Over a year ago

Application.Run "Module1.msg1" worked too. Weirdly, I can't even run the msg1 macro from the Macros dialog

Community · Accepted Answer · 2017-05-23 12:33:40Z

0

Using GetRef, you give the reference to the sub. See my question here for example

EDIT: following the suggestions in the comments, here part of the solution to this question.

sub one(para)
  WScript.Echo para & " from one"
end sub

sub two(para)
  WScript.Echo para & " from two"
end sub

sub main(subname, para)
  Dim f : Set f = GetRef(subname)
  f para
end sub

main "one", "test" '=>test from one

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Jun 10, 2015 at 10:48

peter

42.3k7 gold badges67 silver badges110 bronze badges

2 Comments

Almo Over a year ago

This popped up in the Low Quality Post queue. I'm hitting skip, but I think this could use a teeny bit more explanation. :)

GSerg Over a year ago

GetRef exists in VBScript, but not in VBA. They are different languages.

Collectives™ on Stack Overflow

Call a VBA sub using a string value [duplicate]

2 Answers 2

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Linked

Related