I have my macro all set to format a report. The last thing I had to do was
pass in the date of the report since the ETL happens elsewhere. So, when I
took:

Sub LoadData()

and changed it to

Sub LoadData(dt As String)

the macro name disappeared from my run macro list and the script that calls
it can't see it either. Is there some way to pass a parameter into a macro
like this?

To get a parameter passed in remove the argument you have added and add some
code to prompt for a value similar to this

Sub LoadData()
dim dt as string

dt = inputbox("Please enter the date")

--
HTH...

Jim Thomlinson


"kmbarz" wrote:

I have my macro all set to format a report. The last thing I had to do was
pass in the date of the report since the ETL happens elsewhere. So, when I
took:

Sub LoadData()

and changed it to

Sub LoadData(dt As String)

the macro name disappeared from my run macro list and the script that calls
it can't see it either. Is there some way to pass a parameter into a macro
like this?

You can still run it from the macro dialog box, even though it
will not show up in the list. In the Macro Name box, enter
'LoadData "the string" '


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"kmbarz" wrote in message
...
I have my macro all set to format a report. The last thing I
had to do was
pass in the date of the report since the ETL happens elsewhere.
So, when I
took:

Sub LoadData()

and changed it to

Sub LoadData(dt As String)

the macro name disappeared from my run macro list and the
script that calls
it can't see it either. Is there some way to pass a parameter
into a macro
like this?

It will disappear from the list as these are macros that you can run without
supplying parameters, there is no way to supply parameter values from there.

That should not stop you using it from another procedure though, just ensure
that you pass the parameter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"kmbarz" wrote in message
...
I have my macro all set to format a report. The last thing I had to do
was
pass in the date of the report since the ETL happens elsewhere. So, when
I
took:

Sub LoadData()

and changed it to

Sub LoadData(dt As String)

the macro name disappeared from my run macro list and the script that
calls
it can't see it either. Is there some way to pass a parameter into a
macro
like this?