Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 21

Thread: VBA Application.Run for Running Macros-with-Parameters Stored Inside an Excel Cell

  1. #1

    VBA Application.Run for Running Macros-with-Parameters Stored Inside an Excel Cell

    Aim: To Loop Through Cells Containing Macro Names and Run Those Macros via Application.Run


    Issue: The code used to loop through cells and run macros works perfectly only for macros without parameters. For those macros with parameters, it fails.


    Problem in Detail:


    I have stored a few macro names inside Excel cells.


    I loop through those cells and call macros (names) written inside them as follows.


    [The subroutine below is called from another subroutine by providing the parameters correctly.]

    Sub SelectAppsToRun(ctlGrpName As String, ws As Worksheet, activeTbx As MSForms.TextBox)

    Dim rng as Range

    For each rng in Sheet1.Range(“A1:A5”)

    Application.Run rng.value

    Next rng

    End
    Sub

    It works correctly when there are no parameters for the macros. But generates error when it tries to run the following macro stored in a cell.


    JumpToNextCtl, ws, ctlGrpName, activeTbx


    This macro is supposed to take its parameters - ws, ctlGrpName and activeTbx - from the subroutine 'SelectAppsToRun'
    ws as Worksheet, ctlGrpName as String, activeTbx As MSForms.TextBox


    The error message I get is:


    Cannot run the macro '"JumpToNextCtl", ws, ctlGrpName, activeTbx'. The macro may not be available in this workbook or all macros may be disabled.


    I understand Application.Run considers the whole as a single string and the fact that there are commas in it does not treat them as separate parameters.


    Is there any way to accomplish what I aim to accomplish?

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You might get some help from the solutions here:

    Excel-vba Application.Run alternative to run macro with named arguments - Stack Overflow
    https://stackoverflow.com/questions/...amed-arguments

    Also, since it looks as though you might be passing an object (ws a sheet?) there might be something of interest here:

    Application.Run and ByRef Arguments
    http://tushar-mehta.com/publish_trai...n_method.shtml

    While searching I came across:
    https://www.ozgrid.com/forum/index.p...e-excel-cells/
    which is a cross post (that's fine) but not fine without supplying links to crossposts, see this:

    Excelguru Help Site - A message to forum cross posters
    https://www.excelguru.ca/content.php?184

    for netiquette (many forums have it as a requirement, not just netiquette)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Is there any way to accomplish what I aim to accomplish?
    Yes

    Option Explicit
    
    
    Sub test()
        
        With Worksheets("Sheet1")
            'need the .Text Edit -- or at least .Value. Can't leave as Range in the macro name
            Call Application.Run(.Range("A1").Text, .Range("B1"), .Range("C1"), .Range("D1"), .Range("E1"))
        End With
    
    
    End Sub
    
    
    Sub TwoTimes(P1 As Double, P2 As Double, P3 As Double, P4 As Double)
    
    
        MsgBox 2 * P1
        MsgBox 2 * P2
        MsgBox 2 * P3
        MsgBox 2 * P4
    
    
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Welcome to the forum!

    You passed the wrong value types, I guess.

    Your sub has:
    Sub SelectAppsToRun(ctlGrpName As String, ws As Worksheet, activeTbx As MSForms.TextBox)
    But I guess you did:
    Application.Run "JumpToNextCtl", ws, ctlGrpName, activeTbx
    You should have used:
    Application.Run "JumpToNextCtl", ctlGrpName, ws, activeTbx

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Assuming that the cells contain EITHER a macro name OR a Macro Name and 2 Parameters, and nothing else:

    Something like...
    Option Explicit
    
    Sub SelectAppsToRun()
    Dim rng As Range 'rng only contains Strings, your 'Macros" must deal with Strings
    Dim Arr
    
        For Each rng In Sheet1.Range("A1:A5")
            Arr = Split(rng, ", ") 'The Split parameter depends on the textual formatting of the contents of the cell.
                                   'I am assuming a Comma & Space separates each "value"
            If Not IsArray(Arr) Then
                Application.Run rng.Value
            Else
               Application.Run Arr(0), Arr(1), Arr(2)
            End If
        Next rng
    
    End Sub
    See how much easier it is to just use VBA:
    Sub RunAllFive()
       Macro1
       Macro2 Sheets("Sheet1"), TextBox2
       Macro3
       Macro4 Sheets("Sheet2"), Texbox1
       Macro5
    End Sub
    Last edited by SamT; 05-14-2021 at 08:12 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    These forums makes it easy to get clear help. The best way is to attach a simple example file. Click the Go Advanced button on lower right of a reply box. Then click the Manage Attachments below that reply box.

    One critical thing that is often overlooked is the Module name. Sometimes one also needs to Run a macro from another workbook. In that case, we would add that but since you did not say that, I did not explain or show that.

    Here is a short example where both work for me. Normally, I use more distinctive module names than Module1.
    Sub Test_Run()  
      Application.Run "Module1.Run1", Worksheets(2)
      Application.Run "Module1.Run2", Worksheets(2).Name
    End Sub
    
    
    Sub Run1(ws As Worksheet)
      MsgBox ws.Name
    End Sub
    
    
    Sub Run2(wsName As String)
      MsgBox Worksheets(wsName).Name
    End Sub
    Last edited by Kenneth Hobs; 05-14-2021 at 09:03 AM.

  7. #7

    Method via array does not work either.

    To SamT

    The method via array does not work either. Here is a file attached. I have simplified my original query for more clarity,
    Attached Files Attached Files

  8. #8
    To Kenneth Hobs

    If I provide it directly as Application.Run "JumpToNextCtl", ws, ctlGrpName, activeTbx it works. But not when I pick that as a string from a cell in Excel.

  9. #9
    To Paul_Hossler

    Thanks for explaining through file. There is a difference in the case I explained. My macroname with its parameters lie in a single cell. Attached herewith is a file where I have simplified my original query.
    Attached Files Attached Files

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You're supposed to include the cross-post links yourself, here's another one:
    https://stackoverflow.com/questions/...de-an-excel-ce

  11. #11
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Your #8 does not work in your example file. You have to post code for us to see how that "works".

    In that file, you are passing this:
    'DisplayNameofTextBox, ws, activeTbx
    Sub DisplayNameofTextBox(ws As Worksheet, activeTbx As MSForms.TextBox)
    Again, you are passing strings in Run while the Sub is expecting objects. ws is a string. activeTbx is a string. Your DisplayNameofTextBox should be coded to handle string inputs rather than objects.

  12. #12
    That is what I am trying to find.

    How to pass objects from cells? OR how to make the sub interpret what I pass from cell as object?

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    LOTS of disconnects, wrong type of parameters being passed, wrong name of TextBox, wrong passing order of parameters, wrong macro name, ......


    Sub SelectAppsToRun()
        Dim rng As Range, LR As ListRow, Arr, ws As Worksheet, txtctl As MSForms.TextBox
        
        With MacroListSht.ListObjects("MacrosTable")
            For Each LR In .ListRows
            
                Set rng = LR.Range
                
                Set ws = ThisWorkbook.Worksheets(rng.Cells(1, 1).Value)
                
                Arr = Split(rng.Cells(1, 2).Value, ", ")
                Set txtctl = ws.OLEObjects(Arr(2)).Object
                
                Application.Run Arr(0), ws, txtctl
            Next
        End With
    
    
    End Sub
    
    
    Sub DisplayNameofTextBox(ws As Worksheet, activeTbx As Object)
        MsgBox ws.Name
        MsgBox activeTbx.Value
    End Sub
    Attached Files Attached Files
    Last edited by Paul_Hossler; 05-15-2021 at 07:46 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    You only have one valid value, the name of the macro, to parse out. You had already created the other two inputs.

    Sub SelectAppsToRun()  
      Dim rng As Range, ws As Worksheet, txtctl As MSForms.TextBox
      
      Set ws = ThisWorkbook.Worksheets("Macro List")
      Set txtctl = TxtBoxSht.OLEObjects("TextBox1").Object
       
      For Each rng In MacroListSht.ListObjects("MacrosTable").ListColumns(2).DataBodyRange
        Application.Run Split(rng, ", ")(0), ws, txtctl
      Next rng
    End Sub
    
    
    Sub DisplayNameofTextBox(ws As Worksheet, activeTbx As MSForms.TextBox)
        MsgBox ws.Name
        MsgBox activeTbx.Name
    End Sub
    Use Paul's file to achieve your goal.

    Unless you know that the inputs will always exist, you will want to add error handling code for missing things like macro, worksheet, and textbox.
    Last edited by Kenneth Hobs; 05-15-2021 at 08:12 AM.

  15. #15
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location

    How to pass objects from cells? OR how to make the sub interpret what I pass from cell as object?
    Cells don't contain Objects, they contain text or numbers. Your code must be able to use Strings.

    Cell.Value = "Macro1, Sheet1, Tbox1"
    Sub MacroName(wsName as String, TBoxName as String)
    With Worksheets(wsName). Controls(TBoxName)...
    Then

    Application.Run Arr(0), Arr(1), Arr(2)
    In your example Arr(0) = "DisplayNameofTextBox", Arr(1) = "ws", and Arr(2) = "activeTbx". Neither "ws" nor "activeTbx" are Objects

    Reread the opening line of Paul's signature. "Tell us WHAT you want to do, not HOW you think you want to do it"
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  16. #16
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    I added to Paul's file in #13. I also added another row to the table with just one value in column 2.

    One can setup the Cases to act on the Sub's name or number of elements in the array or such.

    Sub Main()
      Dim rng As Range, LR As ListRow, Arr, ws As Worksheet, txtctl As MSForms.TextBox
      
      With MacroListSht.ListObjects("MacrosTable")
        For Each LR In .ListRows
          Set rng = LR.Range
          Arr = Split(rng.Cells(1, 2).Value, ", ")
          
          Select Case True
            Case UBound(Arr) = 0
              Application.Run Arr(0)
            Case Arr(0) = "DisplayNameofTextBox"
              Set ws = ThisWorkbook.Worksheets(rng.Cells(1, 1).Value)
              Set txtctl = ws.OLEObjects(Arr(2)).Object
              Application.Run Arr(0), ws, txtctl
            Case Else
              'do something else if nothing is true...
          End Select
          
        Next LR
      End With
    End Sub
    
    
    Sub ShowActiveSheetsUsedRangesAddress()
      MsgBox ActiveSheet.UsedRange.Address(external:=True), vbInformation, "ShowActiveSheetsUsedRangesAddress"
    End Sub

  17. #17
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Select Case True
    Case UBound(Arr) = 0
      Application.Run Arr(0)
    Case Arr(0) = "DisplayNameofTextBox"
      DisplayNameofTextBox ws, txtctl
    End Select

  18. #18
    Remembering: Tell us WHAT you want to do, not HOW you think you want to do it


    I will try my best to explain in detail what I really want to do rather than detailing on my ideas.


    I have a few sheets containing many shapes and ActiveX controls (file attached exhibiting simplified version of my problem). Selected Shapes and ActiveX controls have been grouped together (with groups being given a name). There are few such groups.


    The ActiveX controls are assigned to Classes with KeyDown event at the Open event of workbook. One class for dealing with TextBoxes and another class for dealing with ComboBoxes. So when I press TAB while inside an ActiveX control, the cursor moves to the next ActiveX control in that group. The macro that facilitates this is JumpToNextCtl ws, ctlGrpName, txtctl


    With respect to each group of ActiveX Controls / Shapes, separate macros are also supposed to run on pressing TAB like mentioned above. Taking examples of the attached file:
    (1) For the currently active ActiveX control, If its sheet is Sheet1 and ActiveX group name is AddContGrp the macro that will run is DisplayContactsGroupSheetName.
    (2) For the currently active ActiveX control, If its sheet is Sheet2 and ActiveX group name is AddProjGrp the macro that will run is DisplayProjectGroupSheetName.


    I have given all these macro names in a sheet named 'Tables Sheet' in a table. To loop through these table range and pick the macro to run, I call the following macro inside the Class Module mentioned earlier.


    SelectMacrosToRun ws, ctlGrpName


    This macro finds the worksheet name (of currently active ActiveX control) in the first column of the table and ActiveX controls group name (of currently active ActiveX control) in the second column of the table. If both match it will run the macro in the third column of the table.


    I could have directly called those macros in the KeyDown event of the classes. But avoided doing that for the following reasons:


    (1) All macros will run whenever I press the TAB. I would like only those macros to run that are related to the currently active ActiveX Control.
    (2) Want to keep Class Module compact without filling in all the macro names. Instead I prefer to keep those macro names in a separate sheet, choose from there according to the currently active ActiveX control and then run only those particular macros.
    (3) Keeping the macro names in a separate sheet enables me easily narrow down into the problematic macros because beside the macro names in the sheet, there will also be the names of Worksheet and ActiveX Control Group names. So when I press TAB when inside an ActiveX control, I can easily refer to the sheet and check just the macros related to that ActiveX control.


    For the time being, I am calling the macro [ JumpToNextCtl ws, ctlGrpName, txtctl ] directly from the Class Modules.

    But what I want is, I want to choose this macro from the table and run using [ SelectMacrosToRun ws, ctlGrpName ]
    Attached Files Attached Files

  19. #19
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I understand that you are trying to emulate the UserForm's Tab action on a Worksheet.

    Why doesn't Setting the Sheet's ActiveX controls' TabKeyBehavior Properties to True work for you?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  20. #20
    To SamT:

    Because Ranges are also involved in the tab order and not just the ActiveX. I have not shown that in the file though.

    My sole issue is getting the macros in the cells to run.

    Is there a way to take the argument piece from the cell, check whether a similar parameter exists in the 'SelectMacrosToRun' and use that as parameter. If so the object would get passed as parameter like we wanted. Right?

Tags for this Thread

Posting Permissions

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