VB or macro question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Computerguy - 12 Oct 2006 22:57 GMT

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?

Reply to this Message

Don Guillett - 12 Oct 2006 23:06 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hi,
>
[quoted text clipped - 12 lines]
>
> P.S. What is a good source of info on this kind of thing?

Reply to this Message

Computerguy - 12 Oct 2006 23:49 GMT

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

> 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
[quoted text clipped - 21 lines]
> >
> > P.S. What is a good source of info on this kind of thing?

Reply to this Message

Bob Phillips - 13 Oct 2006 09:28 GMT

> 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.

Reply to this Message

Computerguy - 14 Oct 2006 05:51 GMT

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

> .........
> 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.

Reply to this Message

Bob Phillips - 14 Oct 2006 12:46 GMT

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.

Signature

HTH

Bob Phillips

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

> Hi Bob,
>
[quoted text clipped - 17 lines]
> > whether you want a macro that will create all the formulae within a
> > worksheet for you.

Reply to this Message