CODEWITHSUNDEEP
vbaexcelms-word
james
james

Reputation: 139

Macros not showing up in the run macro menu

I started learning VBA and I don't understand why some macros copy-pasted from the internet do not show up in the run macro menu (Alt-F8).

Below there are 2 macros, but only the second one is showing. Why? And how do I fix it?

Sub Test1(ByVal Target As Hyperlink)

    '...

End Sub

Sub Test2()

    '...

End Sub

enter image description here enter image description here

Upvotes: 5

Views: 15056

Answers (5)

Xavier
Xavier

Reputation: 21

You can call an even private macro from any excel object you can assign a macro, calling it this way:

'MyWorkbook'!'MyModule.MyProcedure "MyParameter1"'

(be careful with single quotes: ' around procedure name with parameter)

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Macros with arguments are not available in Macros list because they cannot be run alone instead they are called by another macro by passing the required arguments.

Upvotes: 6

Vityata
Vityata

Reputation: 43585

Here are my 5 cents - if you give an optional parameter, you will be able to call the sub routine, even if it will not be shown among the ones which you can chose from.

Write aaaaTestMe and press Run.

Public Sub aaaaTestMe(Optional lngA As Long = 8)
    Debug.Print lngA
End Sub

Upvotes: 1

interesting-name-here
interesting-name-here

Reputation: 1890

You cannot call macros that have parameters as you are describing. If you need to have a parameter, you can take it out and then have the user input the value.

Sub Test1()        
    Dim hyperLink As String
    hyperLink = InputBox("Please input hyperlink", "My Parameter")

    '...

End Sub

Alternatively, if the hyperlink is in your document, grab the value from your document instead.

Upvotes: 1

avb
avb

Reputation: 1753

If a Sub declaration contains parameters it will not show there.

Upvotes: 4

Related Questions