Why VBA code for "average" generates a result differing from Excel SUM()?

Question

Result of VBA code Worksheetfunction.Average differs from the result got by Excel +SUM(range) function.

I've put up the VBA script as below in order to calculate the occurences of specific values in a range, named "tart", and then to calculate the relevant average, maximum and minimum values :

In order to double check the average value generated I've made use of Excel's SUM()/35 function. I expected identical average values, however the two methods brought diverse results.

Also the Max and Min value in the specified range is actually incorrect

Sub freq()

Worksheets("feldolg").Activate
Dim tart As Range
    Set tart = Range("B2:H1222")
Dim nums As Byte
    nums = InputBox("blabla ?", "blabla")
    
    For szam = 1 To nums
Cells(2, 11).Offset(0, szam - 1) = WorksheetFunction.CountIf(tart, szam)
Cells(2, 11).Offset(0, nums + 1) = WorksheetFunction.Average(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1))
Cells(2, 11).Offset(0, nums + 3) = WorksheetFunction.Max(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1))
Cells(2, 11).Offset(0, nums + 5) = WorksheetFunction.Min(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1))
    Next szam

         'MsgBox Range(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1)).Cells.Count
         'MsgBox Cells(2, 11).Offset(0, nums - 1).Address
End Sub

The code parameters have been checked several times and to me they seem to be correct. What did I do wrong ?

score 0 · Accepted Answer · answered Mar 15 '23 at 13:59

You aren't giving a range to Average, Max or Min, you're giving it two cells. It isn't searching between the cells, its just looking at those two cells. You probably meant to have those two cells as arguments in Range() like Range(Cells(...), Cells(...))

Cells(2, 11).Offset(0, szam - 1) = WorksheetFunction.CountIf(tart, szam)
Cells(2, 11).Offset(0, nums + 1) = WorksheetFunction.Average(Range(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1)))
Cells(2, 11).Offset(0, nums + 3) = WorksheetFunction.Max(Range(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1)))
Cells(2, 11).Offset(0, nums + 5) = WorksheetFunction.Min(Range(Cells(2, 11), Cells(2, 11).Offset(0, nums - 1)))

I suggest cleaning up the code by using variables

Dim c As Range: Set c = Cells(2, 11)
Dim data As Range: Set data = c.resize(1, nums)

c.Offset(0, szam - 1) = WorksheetFunction.CountIf(tart, szam)
c.Offset(0, nums + 1) = WorksheetFunction.Average(data)
c.Offset(0, nums + 3) = WorksheetFunction.Max(data)
c.Offset(0, nums + 5) = WorksheetFunction.Min(data)

score 0 · Answer 2 · answered Mar 15 '23 at 15:37

0

You are right, that's what I aimed to formulate. The resolution as suggested above works out, thank you Toddleson. However this brought up a new problem for me to solve, but I can't succeed: The error message says: "Duplicate declaration in current scope" and the "Dim nums as Byte" expression gets marked. But I can't see that duplicated anywhere.

Sub freq()
Worksheets("feldolg").Activate
Dim c As Range: Set c = Cells(2, 11)
Dim data As Range: Set data = c.Resize(1, nums)
Dim tart As Range:    Set tart = Range("B2:H1222")
Dim nums As Byte:   Set nums = InputBox("bla", "blabla")

    For szam = 1 To nums
        c.Offset(0, szam - 1) = WorksheetFunction.CountIf(tart, szam)
        c.Offset(0, nums + 1) = WorksheetFunction.Average(data)
        c.Offset(0, nums + 3) = WorksheetFunction.Max(data)
        c.Offset(0, nums + 5) = WorksheetFunction.Min(data) 
    Next szam
End Sub

answered Mar 15 '23 at 15:37

A Pofta Tapofta

5
4

`Dim data As Range: Set data = c.Resize(1, nums)` this line has `nums` and appears before the line `Dim nums As Byte`. When a variable is written with no prior `Dim`, VBA automatically does `Dim _ As Variant`, so when you do `Dim _ As Byte` later, VBA complains because it has already Dimmed that variable. – Toddleson Mar 15 '23 at 16:06
To fix that, just put `Dim nums As Byte` above any line that uses `nums` – Toddleson Mar 15 '23 at 16:07
Also, when working with numbers in Excel VBA, there isn't actually any benefit to using `Integer` or `Byte`. The application functions and methods all use `Long` and it converts numbers back to `Long` to perform any operations. https://stackoverflow.com/a/26409520/14608750 – Toddleson Mar 15 '23 at 16:12
That is really logical. When applying this principle I get an other error message : "object required" and now "Set nums" gets highlighted in the code. – A Pofta Tapofta Mar 15 '23 at 17:01
oh true, I shouldve seen that earlier. `Set` is only used with `Object` variables. Since neither `Long` or `Byte` are `Object` variables, you shouldn't use `Set`. Just write `nums = InputBox("bla", "blabla")` – Toddleson Mar 15 '23 at 17:04

score 0 · Answer 3 · answered Mar 15 '23 at 16:30

That's really logical. By applying this principle npw I get an other error message: "Object required" and "Set nums" gets marked.


Worksheets("feldolg").Activate
Dim tart As Range
    Set tart = Range("B2:H1222")
Dim c As Range
    Set c = Cells(2, 11)
Dim nums As Byte
    Set nums = InputBox("  ?", " ")
Dim data As Range
    Set data = c.Resize(1, nums)

Why VBA code for "average" generates a result differing from Excel SUM()?

3 Answers3