CODEWITHSUNDEEP
functionexcelfor-loopexcel-2010vba
2ndFloorStuff
2ndFloorStuff

Reputation: 1

VBA Excel 2010 function to make cell calculations then sum using different ranges

I am trying to write a function that will determine the total basal area in square feet in a cell by summing the individual calculations for tree diameters in varying numbers in the range of adjacent cells.

I am new to VBA and I keep getting errors, that I can't seem to find examples and answers online. My Spreadsheet cell using this just returns a #VALUE in the cell. 

Function BArange(x As Range)
    Dim BArangeTot As Double
    BArangeTot = 0

    Dim y As Integer
    y = x.Count

    Dim BArangei As Integer
    BArangei = 0

    For i = 1 To y
        BArangei = 0.005454154 * x(1, i) ^ 2
        BArangeTot = BArangeTot + BArangei
    Next i

    Return

    MsgBox (BArangeTot)

End Function

Function BA(x As Double)
    BA = 0.005454154 * x ^ 2
End Function

To describe the canopy occupancy of a site we calculate the cross sectional area of trees at a 4.5-foot height often in spreadsheets, and we need to sum up multiple stemmed trees as well. The number of square feet of basal area per acre is a very standard metric of density/occupancy of a site. I thought defining a function that we could use for this would be handy.

I have spent several hours on this and tried using the Set command to initiate my variables but I think that is only necessary if they are objects. I have tried commenting out lines and running the program to my curser point in the program, but I don't get anything I can look up as similar examples answers. My lastest problem was it expecting sub, function, or property.

Upvotes: 0

Views: 1559

Answers (2)

brettdj
brettdj

Reputation: 55672

You could do this as a standard Excel formula.

If your data is in A1:A10

=SUMPRODUCT(A1:A10,A1:A10)*0.005454154

Upvotes: 3

Barranka
Barranka

Reputation: 21047

A few corrections I sugest:

  1. If x is a Range, if you want to read the values of the cells, you need to write this:

    x.Cells(1,i)

    or 

    x.Cells(1,i).Value

    (I prefer the second option)

  2. If you want to return a value, you need to do an assignment to the function name:

    BArange = BArangeTot

  3. Also, I infer that your range is a column, so I would assign the y variable as follows:

    y = x.Rows.Count

  4. Finally, delete the Return.

Hope this helps you

Upvotes: 2

Related Questions