Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Ludwig Fischer   on Sep 19 In MS Office Category.

  
Question Answered By: Heidi Larson   on Sep 19

: 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

Share: 

 

This Question has 2 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Average Macro Help Or get search suggestion and latest updates.


Tagged: