.onaction, passing arguments

[Neal Zimm]

Hi All,
Am just getting into toolbars and custom menus that will call macros.
Docum says in summary, for toolbars, .onaction = "MyMacro" 'with xxx and
_ end with not shown.

Well, I've got macros whose function varies via arguments.
e.g. call RealMacro(arg1)

Haven't yet read Walkenbach's chapter on menus, but as a heads-up I tried
..onaction = Run Macname "arg value" but of course it errored out.

So, it seems unless there's a better way, that I'll have to have an
"intermediate"
call where: (in sorta pseudo code)
.onaction = "MyMacA"
.onaction = "MyMacB"

sub MyMacA()
call RealMacro("arg value A")
end sub

sub MyMacB()
call RealMacro("arg value B")
end sub

Am I getting warm ?

 

[Bob Phillips]

I have never understood why anyone wants to set a parameter value in an
onaction, because it is static, so it defeats the objectives of parameters.
However, that being so, this is how to do it

.OnAction = "'mymacro ""hello""'"

A better way in my view is to test with the macro some of the control button
properties, like so

Sub mymacro()
With Application.CommandBars.ActionControl
If .Tag <> "" Then
MsgBox .Tag
End If
End With
End Sub


which you can set dynamically in the code like so

With Application.CommandBars("Standard").Controls("Test")
If somevalue > 17 Then
.Tag = "hello"
Else
.Tag = "goodbye"
End If
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tom Ogilvy]

Just a heads up, (and another reason not to use it)

.OnAction = "'mymacro ""hello""'"

is undocumented and in my experience doesn't work with every version of
excel.

 

[Neal Zimm]

Bob and Tom, thanks. I won't use the technique, (glad to hear that it's
undocumented, 'cuz I looked pretty hard for it and could not find any.)

To Bob's point, what's 'wrong' with the following?
1. Theoretically, if a macro can be called by .onaction = stuff, or by
another sub, keeping the control testing out of that macro makes it more
'independant'?

2. Are you saying that if a macro has to perform let's say 4 processes,
where much of the code is the same, that I should write 4 subs and NOT vary
the function via an argument?
e.g. In my app it's prudent to control several worksheet, ws, processes,
so I have an array of 'reserved' ws names.
I don't mind a user hiding the ws or shifting its ws name tab to the left
or right, arg = "hide" or "move", but I'd like to draw a line in the sand
when arg = "delete" or arg = "insert".

Thanks for your expertise.
Neal

 

[Neal Zimm]

Bob and Tom, thanks. I won't use the technique, (glad to hear that it's
undocumented, 'cuz I looked pretty hard for it and could not find any.)

To Bob's point, what's 'wrong' with the following?
1. Theoretically, if a macro can be called by .onaction = stuff, or by
another sub, keeping the control testing out of that macro makes it more
'independant'?

2. Are you saying that if a macro has to perform let's say 4 processes,
where much of the code is the same, that I should write 4 subs and NOT vary
the function via an argument?
e.g. In my app it's prudent to control several worksheet, ws, processes,
so I have an array of 'reserved' ws names.
I don't mind a user hiding the ws or shifting its ws name tab to the left
or right, arg = "hide" or "move", but I'd like to draw a line in the sand
when arg = "delete" or arg = "insert".

Thanks for your expertise.
Neal

 

[Neal Zimm]

Bob and Tom, thanks. I won't use the technique, (glad to hear that it's
undocumented, 'cuz I looked pretty hard for it and could not find any.)

To Bob's point, the objectives of paramerter's are to vary the processing is
what's being called, no? So, I guess I don't see the difference between A)
in 4 menu choices calling the same sub with 1 of 4 arg values. the onaction
may be status but the user choice via menu is not, versus, B) testing
that choice "inside" the macro being called?? . Your thoughts, please re:

1. Theoretically, if a macro can be called by .onaction = stuff, or by
another sub, keeping the control testing out of that macro makes it more
'independant'?

2. Are you saying that if a macro has to perform let's say 4 processes,
where much of the code is the same, that I should write 4 subs and NOT vary
the function via an argument?
e.g. In my app it's prudent to control several worksheet, ws, processes,
so I have an array of 'reserved' ws names.
I don't mind a user hiding the ws or shifting its ws name tab to the left
or right, arg = "hide" or "move", but I'd like to draw a line in the sand
when arg = "delete" or arg = "insert" depending upon the name chosen.

Thanks for your expertise.
Neal

 

[Neal Zimm]

Bob and Tom, thanks. I won't use the technique, (glad to hear that it's
undocumented, 'cuz I looked pretty hard for it and could not find any.)

To Bob's point, the objectives of paramerter's are to vary the processing is
what's being called, no? So, I guess I don't see the difference between A)
in 4 menu choices calling the same sub with 1 of 4 arg values. the onaction
may be status but the user choice via menu is not, versus, B) testing
that choice "inside" the macro being called?? . Your thoughts, please re:

1. Theoretically, if a macro can be called by .onaction = stuff, or by
another sub, keeping the control testing out of that macro makes it more
'independant'?

2. Are you saying that if a macro has to perform let's say 4 processes,
where much of the code is the same, that I should write 4 subs and NOT vary
the function via an argument?
e.g. In my app it's prudent to control several worksheet, ws, processes,
so I have an array of 'reserved' ws names.
I don't mind a user hiding the ws or shifting its ws name tab to the left
or right, arg = "hide" or "move", but I'd like to draw a line in the sand
when arg = "delete" or arg = "insert" depending upon the name chosen.

Thanks for your expertise.
Neal

 

[Bob Phillips]

I am only going to answer once, couldn't bear to type it out 4 time <g>

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob and Tom, thanks. I won't use the technique, (glad to hear that it's
undocumented, 'cuz I looked pretty hard for it and could not find any.)

To Bob's point, the objectives of paramerter's are to vary the processing
is
what's being called, no? So, I guess I don't see the difference between
A)
in 4 menu choices calling the same sub with 1 of 4 arg values. the
onaction
may be status but the user choice via menu is not, versus, B) testing
that choice "inside" the macro being called?? . Your thoughts, please re:

1. Theoretically, if a macro can be called by .onaction = stuff, or by
another sub, keeping the control testing out of that macro makes it more
'independant'?

I am not suggesting not testing in the macro, just not using a parameter as
the way to pass the variable.

2. Are you saying that if a macro has to perform let's say 4 processes,
where much of the code is the same, that I should write 4 subs and NOT
vary
the function via an argument?
e.g. In my app it's prudent to control several worksheet, ws, processes,
so I have an array of 'reserved' ws names.
I don't mind a user hiding the ws or shifting its ws name tab to the
left
or right, arg = "hide" or "move", but I'd like to draw a line in the sand
when arg = "delete" or arg = "insert" depending upon the name chosen.

Not at all, just use a different method. As I showed you, you can set a
property of the commandbar control at any point in your code, and you can
test that property very easily within the OnAction macro using
Application.CommandBars.ActionControl which points at the currently
activated control.

 

[Neal Zimm]

First, Apologies on the qty of times I TRIED to get the indentation correct
on prior responses, I never meant for you to read and/or type it more than
1x.

I think I understand your point, but I just wanted to make sure our language
is in synch. I keep saying arguments, and you keep saying parameter, so
......

call Macname(ABC, DEF)

I call ABC and DEF arguments which I think is MSo's term for them in most of
the Help. Is your name for them parameters?

If so, you think it's a crappy idea to pass their value via onaction, but
rather to test their values in a separate piece of code with the called
Macname.

Thanks,
Neal

 

[Bob Phillips]

In my terminology, the variable declared in a procedure signature the
arguments, and the values that are passed to the procedure parameters. They
are different beasts in my mind, and require a different label.

So in this procedure

Function myFunc(ByVal msg As String)

MsgBox msg
End Function

msg is an argument.

In the call, such as

sText = "hello"
Call muFunc(sText)

sText is a parameter.

I am not saying it is a crappy idea to pass their value via onaction (I
probably am, but even I would not be so blunt as that <g>), but I am saying
it is pointless IMO. If you define a commandbar control with a parameter
value, what is the point? That value is fixed, and non-changeable, so why
not just build the value into the procedure? You cannot create a situation,
at least I cannot see how, when invoking that control will ever pass any
value other than the one originally defined, which defeats the objectives of
parameters/arguments to me. To make the procedure able to respond
differently to changing situations, you need another method, such as the one
I showed. And then there is Tom's comment about it not necessarily working
in all versions of Excel. Can't say I have come across this myself, but I
wouldn't use that technique so it is not likely I would, and Tom knows what
he is talking about. Summary, pointless, possibly dangerous ... nah, on
reflection I think it is a great idea.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Neal Zimm]

Hi Bob,
Well, the fog is lifting. Thanks so much for the time you took to explain.
This 'last' reply needs no response unless you're so inclined after
reading the last question at the bottom. It concerns $.
Allow me to explain a bit... as briefly as I can, I've been in apps
development a ton of years in large corporate mainframe environments, and am
building a whoppingly large add-in(at least for a 1st timer like me) which I
hope to sell to the newspaper industry. Since 9/11 when I got "laid-off", I
am entirely self taught in VBA along with Walkenbach's 2003 programming book
and the expertise of guys like you on this site. Note: The add-in is a
result of my delivering newspapers for a while to help make ends meet.
As my first foray into toolbars and menu bars, your 1st answer initially
made no sense to me due to my lack of understanding of the basic control
itself, as well as no experience with the object model of toolbars and menus.
Of course what you say makes sense, and I will follow the advice.

I am about two months away from competing this project. I am SURE I don't
know the 'dangers' I'm going to run into when and if my add-in gets sold, but
I will be looking for help which I will PAY for.

Last question:
Do you want me to send you the eventual RFP as I try to round up the help
I will
need ?
My email is below, at least in the copy of the message I'm looking at. If
you can't see it, then I've clicked the notify me box, so send me something I
can use to contact you the 'right' way. Again, many thanks.
Neal