Using formulas in VB with variables passed from Excel cells

I am fairly new in using VBA to customize Excel but have used VBA with AutoCAD and MS Access before.

I have created a worksheet where each row holds measurements of steel shapes. I would like to perform calculations based on these measurements and thought I could do it with VBA. However, I'm not clear on how to pass these measurements over to VBA and how I invoke these calcuations in Excel.

I realize that this is a fairly general question, but I would appreciate any advice that you may have or directions to where I can go to learn how to do this.

Thanks!

Nick

Well, I suppose that could be a possibility:

There would be around 4 variables that would need to be set in the worksheet and then a few different calculations would be performed using those variables. Which formulas are used also depends on the results from other formulas that are calculated first. Make sense?

Because I am familiar with VBA, I see the logical steps for IF/THEN statements to determine the results that I would like to see come back to the worksheet when everything is said and done. One thing I forgot to mention is that with the four variables that are put into the worksheet, there would be at least four values that I would like to be calculated and put into the worksheet after all of the formulas have been run.

Unfortunately I haven't started putting this together yet so I really don't have anything to show right now...

As noted, we're going to need to know some specifics of what you want to do in order to give specific help. A few ideas of things to look at:

1) The built in IF worksheet function can be used to make some basic decisions. From what I gather, your problem involves some decision making (If/then statements). You might review Excel help for the IF function and see if that can be used as part of your solution.

2) A lot of times, complicated IF functions can better be respresented using a lookup table. See Excel help for VLOOKUP and other lookup functions if needed.

3) For many engineering calculations that I do, I've found that I can simplify the spreadsheet side of things by using a VBA user-defined function (UDF). There are disadvantages to UDF's, so you'd want to be judicious in using this option. The advantage I've found is that a UDF can allow me to take a complex equation or something involving multiple decisions and pack it into a single function. See VBA help for the FUNCTION statement. Basic idea is:

Function myudf(arglist)
code to calculate function result
myudf=functioin result
end function

You can then call the UDF from the spreadsheet like any of the built in functions =myudf(A1,A2,...). I've even used UDF's to return arrays to multiple cells, so even that aspect can be accomplished.

On the surface, this seems like the closest thing to what you originaly asked for. Used judiciously, this could be a useful approach.

Hope that helps. Let us know specifics and perhaps we can help further.

MrShorty:

Your third point was exactly what I needed - I have it working from here.

Thanks again for everyone's help!