VB or macro question

[Computerguy]

Hi,

I very familar with Excel but have not done much macro or VB programming. I
would like to create a formula which will prompt for an input number, do a
calculation (referring to specific cells on the spreadsheet) and place the
result in the selected cell. Example:

A2 = some_function(input number, a1,a3)

I have to do this many times so what is the best way to automate this?

TIA,
-GB

P.S. What is a good source of info on this kind of thing?

 

[Don Guillett]

This is a UDF (user defined function). To use place in a REGULAR module and
on the sheet just type =multiplyit(12) to get 36. You may/may not need to
uncomment the first line.

Function multiplyit(x)
'application.volatile
multiplyit = x * 3
End Function

 

[Computerguy]

Hi Don,

I guess that I am not as familiar with Excel as I thought since I have
several followup questions:
1) I presume that the function example that you sent is a VB function?
2) What is a REGULAR module and how do I indicate that the input should come
from the keyboard (i.e. does "x" in your function imply that it does?)
3) How do I indicate relative cell references?

TIA,
-GB

 

[Bob Phillips]

Hi Don,

I guess that I am not as familiar with Excel as I thought since I have
several followup questions:
1) I presume that the function example that you sent is a VB function?

What Don provided you is a UDF, which is VBA function (i.e. it returns a
result rather than just performing an action) that can be used within a
worksheet.

2) What is a REGULAR module and how do I indicate that the input should come
from the keyboard (i.e. does "x" in your function imply that it does?)

Modules come in 4 flavours in Excel VBA, Userforms and classes,
worksheet/workbook modules, and normal/standard/regular code modules.

To create the latter, with your workbook open, go into the VBIDE (Alt-F11),
select Insert>Module from the menu, and type the code in there.

The x is an argument of the function, and this means that when the function
is called, values must be supplied for all arguments. In this case, there is
just one, so on youyr worksheet you would enter something like

=MultiplyIt(A1)

which would multiply the value in A1 by 3 and show the result in the cell
that contains the formula.

Or you could even use

=MultiplyIt(10)

3) How do I indicate relative cell references?

Just use A1, H10, etc, as against $A$1 or $A1 or A$1.

From your original post, I am not sure whether you are looking for a UDF, or
whether you want a macro that will create all the formulae within a
worksheet for you.

 

[Computerguy]

Hi Bob,

I wasn't very clear in my original post.

A1 A2 A3 A4 <- input data in this row
B1 B2 B3 B4 <- I want to calculate these values using data in rows A and C
C1 C2 C3 C4 <- input data in this row

For example, B1 = function(A1,C1, keyboard input), B2 = function(A2,C2,
keyboard input) etc.

Would a macro do this?

TIA.
-GB

 

[Bob Phillips]

User input in a UDF is a bad idea, it will re-prompt every time the sheet
calculates - not good.

Far better to use a cell that the user sets and use that. They can change it
whenever they want. Everything said so far then applies.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)