Hi Jamie,
I think you are making up your own definition for macro.

The HELP includes lots of uses of the word macro that that
do not require that the name appear in the macro dialog box.

You have very specifically excluded a public subroutine with a parameter
as they will not appear in the macro dialog box, but you can type the name
into the macro dialog box and run it from the spreadsheet like
any other macro appearing in the dialog box.

From HELP:
The Run method returns whatever the called macro returns.
Objects passed as arguments to the macro are converted to
values (by applying the Value property to the object). This
means that you cannot pass objects to macros by using the Run method.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jamie Collins" wrote in message om...
"David McRitchie" wrote ...

Hi Hari,
For item #1 and #3 Chip Pearson has a page on the
the difference between a Macro and a Function, it should
clear up some confusions. (probably also #5)
Macros And Functions (Functions as Opposed to Macros)
http://www.cpearson.com/excel/differen.htm

The suggestion seems to be that 'Sub' is synonymous with 'Macro'. I'd
make the distinction that a macro must appear in the 'Macro' dialog
(Tools, Macro, Macro) i.e. must be a parameterless public Sub in a
public module, being a standard module with no 'Option Private Module'
statement or an object module (e.g. ThisWorkbook code module).

Jamie.

--

"David McRitchie" wrote ...

I think you are making up your own definition for macro.

True. But perhaps my definition will prove more useful <g.

You have very specifically excluded a public subroutine with a parameter
as they will not appear in the macro dialog box, but you can type the name
into the macro dialog box and run it from the spreadsheet like
any other macro appearing in the dialog box.

I tried this:

1. Create a new blank workbook.
2. Open the VBE.
3. Insert a standard .bas module.
4. Add the following code:

Public Sub Test(ByVal Arg1 As Integer)
MsgBox CStr(Arg1)
End Sub

5. Close the VBE.
6. Show the Macro dialog (Tools, Macro, Macro).
7. Note that the list of macros is empty.
8. In the Macro name textbox, type:

Test(1)

9. Hit the Run button.
10. Message appears: 'Reference is not valid.'

I tried a few variations on this theme, e.g. change the parameter to
ByRef, calling the code without parentheses, using a cell reference
(why have I only just spotted it is a RefEdit control rather than a
textbox?) etc, but I can't get the code to run from the macro dialog.

Am I missing something?

Cheers,
Jamie.

--

Hi Jamie,
Your example rewritten
You will not fine Test in the macro dialog box,
but you can run Test and you can run Test2 from the macro dialog box.
You cannot include a parameter from the macro dialog box,
you can only use that parameter from another macro.

Public Sub Test(Optional ByVal Arg1 As Integer)
MsgBox CStr(Arg1) & " --- so what"
End Sub
Sub Test2()
Test (4444)
End Sub

You will see an example of this in
http://www.mvps.org/dmcritchie/excel/proper.htm

Sub Proper_case()
'-- This macro is invoked by you -- i.e. from Macro Dialog (Alt+F8)
proper_case_inner 'The macro you invoke from a menu is Proper_Case
end sub
Sub Proper_Case_Inner(Optional mySelection As String)

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jamie Collins" wrote in message om...
"David McRitchie" wrote ...

I think you are making up your own definition for macro.

True. But perhaps my definition will prove more useful <g.

You have very specifically excluded a public subroutine with a parameter
as they will not appear in the macro dialog box, but you can type the name
into the macro dialog box and run it from the spreadsheet like
any other macro appearing in the dialog box.

I tried this:

1. Create a new blank workbook.
2. Open the VBE.
3. Insert a standard .bas module.
4. Add the following code:

Public Sub Test(ByVal Arg1 As Integer)
MsgBox CStr(Arg1)
End Sub

5. Close the VBE.
6. Show the Macro dialog (Tools, Macro, Macro).
7. Note that the list of macros is empty.
8. In the Macro name textbox, type:

Test(1)

9. Hit the Run button.
10. Message appears: 'Reference is not valid.'

I tried a few variations on this theme, e.g. change the parameter to
ByRef, calling the code without parentheses, using a cell reference
(why have I only just spotted it is a RefEdit control rather than a
textbox?) etc, but I can't get the code to run from the macro dialog.

Am I missing something?

Cheers,
Jamie.

--

"David McRitchie" wrote ...

Hi Jamie,
Your example rewritten
You will not fine Test in the macro dialog box,
but you can run Test and you can run Test2 from the macro dialog box.
You cannot include a parameter from the macro dialog box,
you can only use that parameter from another macro.

Public Sub Test(Optional ByVal Arg1 As Integer)
MsgBox CStr(Arg1) & " --- so what"
End Sub
Sub Test2()
Test (4444)
End Sub

David,

I don't see much difference between

"can have optional parameters, provided they are not used"

and

"must be parameterless"

:-)

Jamie.

--

It was kind of hard to follow your quotes, out of context.
But I already pointed out that you CAN use a macro that
has optional parameters from the macro dialog box even
though you do not see the macro in the macro dialog box

You quotes are out of context, so I it is hard to follow what you are
trying to say. You original statement stated that a macro had
to be in the macro dialog box to be a macro and that is incorrect.

The second was from you, and is incorrect -- you can use macros
from the Macro Dialog box even if you can't see it -- does not say
that you can use all macros from any project, just that you can use
macros from the macro dialog box even though they don't appear there.

The suggestion seems to be that 'Sub' is synonymous with 'Macro'. I'd
make the distinction that a macro must appear in the 'Macro' dialog
(Tools, Macro, Macro) i.e. ___must be a parameterless___ public Sub in a
public module, being a standard module with no 'Option Private Module'
statement or an object module (e.g. ThisWorkbook code module).
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jamie Collins" wrote in message
I don't see much difference between
"can have optional parameters, provided they are not used"

and
"must be parameterless"

"David McRitchie" wrote ...

It was kind of hard to follow your quotes

They do look like quotes, don't they? I was paraphrasing and in a
light-hearted way at that (note the smilie). Sorry for any confusion.

Jamie.

--