: I am working on writing a simple macro that computes the
: average, but I am having trouble figuring out how to count the
: number of values so that I know what to divide by...
AVERAGE is already a built-in function in Excel. I assume you
are doing this for an exercise in writing macros.
: This is what I have so far:
:
: Function Average(rngNumbers As Range)
:
: average = 0
: sum = 0
: NumValues = ActiveSheet.UsedRange.Rows.Count
Do more testing.
Create a new workbook. Add three small ranges separated by
empty cells. Extend one range to be longer than the other two. Go
to the VB Editor (Alt+F11). Open the Immediate Window (Ctrl+G).
Shrink the window size down enough to see the worksheet. Add the
following to the window and press Enter.
ActiveSheet.UsedRange.Select
Do you see how all those empty cells were selected? Once you
see a visual representation of the cells you are selecting, you
may write better code. When I am having problems with a function,
I often start looking at each line of code to test my assumptions.
Showing which cells are selected is very helpful.
While we could use this method to count the cells in a range,
but the range we are interested in is already in rngNumbers. It
would be better to count the cells in that range.
Let's write a test function to count the cells in a range.
I realize this seems like overkill, but down the line you can
write tests for much more complex solutions. Mistakes in some of
those complex solutions may cost people money. Lot's of money.
Add this function to the new spreadsheet we created above.
Add the following to a few cells.
Function TestCount(CountRange As Range, KnownCount as Integer)
Dim iCount As Integer
iCount = CountRange.Rows.Count
TestCount = "Count test failed. Count = " & iCount
If iCount = KnownCount Then TestCount = "Count test Succeeded."
End Function
=TestCount(E4:G6, 9)
=TestCount(E1:E6, 6)
=TestCount(E3:G3, 3)
Of course this doesn't help us out since the count is still
wrong. A little more testing on other ranges reveals that
TestCount always returns the number of rows in the range. Careful
examination shows that we are counting the wrong thing.
CountRange.Rows.Count
Since we are counting cells, let's try this.
CountRange.Cells.Count
Change the function and recalculate the worksheet (F9). All the
tests should succeed.
Function TestCount(CountRange As Range, KnownCount as Integer)
Dim iCount As Integer
iCount = CountRange.Cells.Count
TestCount = "Count test failed. Count = " & iCount
If iCount = KnownCount Then TestCount = "Count test Succeeded."
End Function
Further testing revealed that CountRange.Count works as well.
Next, you might write a function to test the average of a range of
cells