Macro name not in the list

[tywlam]

I am using Excel 2010. I have created a macro through Visual Basic. I couldn't see the macro name in the list when I clicked Macros. Why? I need to type in the name in the dialogue box to run the macro. Please help. Thanks

[MarvinP]

Hi tywlam and welcome to the forum,

Is the Macro behind a Worksheet and Private or is it in a Module and Public?
That might be the problem.

[tywlam]

Thanks for your reply. It is in a Module. I am learning wrirting simple VBA from an On-line tutorial which shows that Excel 2007 can show the name on the list but my Excel 2010 can't!

[MarvinP]

In Excel 2010 I just created a Sub named BOB.
Click on Macros and it shows it.

Can you attach you file? It should show in 2010 without a problem.

Do you have an End Sub at the end?
Have you compiled it?
Do you have code running that is keeping you from starting another macro? That is are you in break mode?
In the Macro Dialog near the bottom it askes for "Macros in:" Is the macro in an open workbook?

[tywlam]

I have "End Function" instead. See below. The macro is in an open workbook. I don't know how to attach the Excel file.


Function SimpleCoinToss1()
CountHeads = 0
For i = 1 To 10
SingleToss = Round(Rnd(), 0)
CountHeads = CountHeads + SingleToss
Next i
Result = MsgBox("You got " & CountHeads & " Heads.", vbOKOnly)
End Function

[MarvinP]

Hi,

Functions aren't Subroutines. A function will not be listed in the Macro List.
Change the word Function to Sub and see if that does it.

To attach a file - click on Go Advanced and then on the Paper Clip Icon above the message area.

[tywlam]

It doesn't even if I change to Sub!
See attached.

[tywlam]

Pls check out below. It shows it works in Excel 2007.

http://excelmontecarlo.com/19a_run_m...worksheet.html

[vlady]

have tried MarvinP it works for me...

Please Login or Register  to view this content.

do we have the save prog.?

by the way if you insist in using functions you can call (run) with by typing in any cell =SimpleCoinToss1() -hit enter
HTH

[tywlam]

It doesn't work for me and displays error message as attached.

[vlady]

should be

Sub SimpleCoinToss1()

not

Function SimpleCoinToss1()

[tywlam]

Thanks MarvinP and vlady. It works if using Sub
But I just wonder why the Function works in Excel2003 but not Excel2010!

[Andy Pope]

Are you sure it works in xl2003?

As far as I am aware only subs with no compulsary arguments will appear in the Macro dialog.

[romperstomper]

Functions have never appeared in the macros dialog (other than perhaps XLM).