Macros Not Showing Up in Macro Table

by

Macros that take arguments are not visible in the macro box because there is no point in having them there. If they need arguments to run, they cannot be run from the macro box because there is no way to supply an argument to the macro in question.

Normally, a macro shows up in the macro list when you display the
Macros dialog box (press Alt+F8), unless one of three conditions is
met:

  • The macro is a function. Functions typically return information,
    and they require information to be passed to them. Since running a
    macro from the macro list doesn’t allow either of these things to
    happen, Excel figures there is no need to list it. User-defined
    functions, which are quite useful in Excel, are not displayed in the
    Macros dialog box because they are, after all, functions.

  • The macro is
    a subroutine with parameters.     Excel assumes that since parameters are
    necessary, and you cannot provide parameters by choosing the
    subroutine from the macro list, there is no need to list it.

  • The subroutine has been declared Private. This means that the subroutine
    is only useful to code within the module in which it is declared.

Source.

Depending on your need, a possible workaround is to use a helper-sub like this:

Sub InvisibleMacro(strArg As String)
    MsgBox("The passed argument was " & strArg)
    ' This macro won't be visible in the macro dialog because it can only be called with an argument
End Sub

Sub VisibleMacro()
    Call InvisibleMacro("Abc")
    ' This macro will be visible in the macro dialog because it requires no arguments and is not private. 
    ' It will call the "invisible" macro with a preset argument.
End Sub

You can use InputBox or the likes if you need the passed argument to be non-static. Of course, depending on what datatype you need to pass as an argument, this approach may be limited and/or require some extra hoops.

More Related Contents:

  1. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  2. How to avoid using Select in Excel VBA
  3. How do I use FileSystemObject in VBA?
  4. Cycle through sub-folders and files in a user-specified root directory [duplicate]
  5. Convert Text to Date?
  6. Run Excel Macro from Outside Excel Using VBScript From Command Line
  7. Importing text file into excel sheet
  8. Loop through each cell in a range of cells when given a Range object
  9. Checking for #N/A in Excel cell from VBA code
  10. Non-web SQL Injection
  11. Error: “Could Not Find Installable ISAM”
  12. Filling any empty cells with the value above
  13. Is there a way to slow down a Web Scraper so it will pick up the code?
  14. Create a folder and sub folder in Excel VBA
  15. One-dimensional array from Excel Range
  16. Renaming Files with Excel VBA
  17. Delete cells in an Excel column when rows = 0
  18. Is there a way to import data from .csv to active excel sheet?
  19. Break out of a While…Wend loop
  20. How to make safe API Timers in VBA?
  21. How to copy data from closed workbooks (keeping them closed) into master workbook using VBA?
  22. EntireRow.Delete performance issue
  23. How do I reference tables in Excel using VBA?
  24. How to extract groups of numbers from a string in vba
  25. Delay macro to allow events to finish
  26. Export multiple sheets to PDF simultaneously without using ActiveSheet or Select
  27. declaring a unicode string in vba in excel [duplicate]
  28. Change the Point Color in chart excel VBA
  29. AutoFilter – Use of SpecialCells
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment