Hi Tony,
Take a look at Chip Pearson's page at the differences
between Macros and Functions:
Macros And Functions (Differences)
http://www.cpearson.com/excel/differen.htm
I will assume you have the distinction correct because you
said you had to hard code the values into the macro,
and the purpose of a function is generally to use parameters.
But please read the above anyway. It will answer at least
one part of question that you asked..
You can use an inputbox in a macro to obtain values
see Prefix160 and other macros using InputBox in
http://www.mvps.org/dmcritchie/excel/join.htm
http://www.mvps.org/dmcritchie/excel/code/join.htm
Syntax
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
example with prompt, title, and default value::
Dim nRowsX as String
nRowsX = InputBox("Specify number of rows (including Cell A1)," _
& Chr(10) & "number of columns will be same as number of rows)" _
& " suggesting " & nRows, _
"Selection of Number of rows to Rotate", nRows)
If nRowsX = "" Then GoTo done 'Check for cancellation
You can have a short macro call another macro using
parameters, but you cannot invoke a macro and pass it
parameters. Example
XL2HTML, XL2HTMLs, XL2HTMLx, XL2HTMLa all call XL2HTML_MAIN
in
http://www.mvps.org/dmcritchie/excel/code/xl2htmlx.txt
When you use a real ISP as your return address, anyone who tries
to reply to you will send a response essentially to the server administrator,
who will either have to figure out who it is for or in the case of Microsoft
simply reject it.
