Nick VL,

Within VBA is a statement titled Select Case. For complicated sequences
such as you want it is easier to construct and document. I rewrote your
function as a macro you can run from within an Excel worksheet. It looks
bigger and more complicated because it spreads the function out over many
lines, however, that makes it much easier to write and debug.

For an Excel worksheet a subroutine forms the basis for a macro. I
named it the same as you named your function. The way it works is for the
user to first select a cell where they answer is to be placed. You then
select the run macro entry from the tools\macro menu item. Then run macro
GroundCover. The macro routine uses the three cells to fetch the data then
calculates the desired result and finally places that result in the cell you
select initially.

You can select any cell on the sheet.

If your not familiar with the Select Case statement you can go to the
Visual Basic window and use help to get a description. The short answer is
that you enter a value or expression to be evaluated after the Select Case
statement. Following that are a series of case descriptions in the form of
expressions. After each case expression is one or more statements to be
executed if the condition of the case is met.

For the routine shown, each input value is the case being selected. The
statement after each case option defines the calculation of your result.
All other cases are then ignored and the procedure jumps to the End Select.

Two sets of Select Case are used, one for the situation where "Flats" is
entered and a second for any other situation. It is feasible and reasonable
to use a Select Case for the cell contents that has the word "Flats" also in
which case you could have more than two options.

To use this in your copy of Excel, copy the entire subroutine into the
worksheet VB code window for your worksheet. When you select the macro
window on the spreadsheet, the macro GroundCover will show up ready for use.

Hope this longwinded answer helps.

RDF


Public Sub GroundCover()
'Visual Basic creates variables directly from code
'Fill the variables with input from the worksheet
With Worksheets("Sheet1") ' when doing multiple actions on a
worksheet, use the With .... End Width construct
input1 = .Cells(27, 2) ' use .cells function to access
worksheet data
input2 = .Cells(27, 3)
input3 = .Cells(27, 4)
' Determine if input1 = flats
If input1 = "Flats" Then

' use the Select Case construct instead of the nested if.. VBA code
options are much richer than EXCEL functions
Select Case input2
Case Is = 4
Ground_cover = input3 * 0.1406
Case Is = 6
Ground_cover = input3 * 0.0625
Case Is = 8
Ground_cover = input3 * 0.0351
Case Is = 9
Ground_cover = input3 * 0.0278
Case Is = 10
Ground_cover = input3 * 0.0225
Case Is = 12
Ground_cover = input3 * 0.0156
Case Is = 18
Ground_cover = input3 * 0.0087
Case Is = 24
Ground_cover = input3 * 0.0025
Case Is = 30
Ground_cover = input3 * 0.0025
Case Else
Ground_cover = 0 'probably should be numeric instead of
text... was "0"
End Select

Else
'Use second IF statement to check input1 and multiply input3 by
'coresponding Number
Select Case input2
Case Is = 4
Ground_cover = input3 * 9
Case Is = 6
Ground_cover = input3 * 4
Case Is = 8
Ground_cover = input3 * 2.25
Case Is = 9
Ground_cover = input3 * 1.78
Case Is = 10
Ground_cover = input3 * 1.44
Case Is = 12
Ground_cover = input3
Case Is = 16
Ground_cover = input3 * 0.56
Case Is = 18
Ground_cover = input3 * 0.45
Case Is = 24
Ground_cover = input3 * 0.25
Case Is = 30
Ground_cover = input3 * 0.16
Case Is = 36
Ground_cover = input3 * 0.1111
Case Is = 48
Ground_cover = input3 * 0.0625
Case Is = 72
Ground_cover = input3 * 0.0278
Case Else
Ground_cover = 0 'probably should be numeric instead of
text... was "0"
End Select

End If

' return result to the selected cell
Selection.Value = Ground_cover
End With
End Sub

"Nick VL" wrote in message
...
I originally was going to do this just as a standard (albeit long) function
in Excel, but quickly had more nested levels then allowed. So my hope is
that I can in VBA produce a custom function that will do all of the work
and
be compatible with 2003 xls format. Please let me know if this is
possible
and if there is a better approach to this. Below is the VBA that I have
so
far, that obviously does not work Im not sure how to use/insert the excel
IF
statements. Thanks for ANY help.


Public Function GroundCover()
'Create Variables to hold numbers
Dim input1 As Double
Dim input2 As Double
Dim input3 As Double

'Fill the variables with input from the worksheet
input1 = Worksheet.cels(27, 2)
input2 = Worksheet.Cells(27, 3)
input3 = Worksheet.Cells(27, 4)
' Determine if input1 = flats
If input1 = "Flats" Then

'Use IF statement to check input2 and multiply input3 by coresponding
number
Output=(IF(input2=4,input3*0.1406, IF(input2=6,input3*0.0625,
IF(input2=8,input3*0.0351, IF(input2=9,input3*0.0278,
IF(input2=10,input3*0.0225, IF(input2=12,input3*0.0156,
IF(input2=18,input3*0.0087, IF(input2=24,input3*0.0039,
IF(input2=30,input3*0.0025,"0"))))))))))

Else
'Use second IF statement to check input1 and multiply input3 by
coresponding number
Output=(IF(input2=4,input3*9, IF(input2=6,input3*4,
IF(input2=8,input3*2.25, IF(input2=9,input3*1.78,
IF(input2=10,input3*1.44,
IF(input2=12,input3, IF(input2=16,input3*0.56, IF(input2=18,input3*0.45,
IF(input2=24,input3*0.25, IF(input2=30,input3*0.16,
IF(input2=36,input3*0.1111, IF(input2=48,input3*0.0625,
IF(input2=72,input3*0.0278,"0")))))))))))))

End If


End Function