VBA calculation of Maximum, minimum, Mean and Median for values in a column

For reasons unknown, the Application.WorksheetFunction procedures do not work on my Excel and so I am trying to do these calculations using VBA. I have written a sub routine for these calculations but the problem is that, while it runs find for maximum and minimum value calculations when tracing through the code line by line, it returns unexpected value when I run the code without stepping through it. (Example: The maximum value of data in column E should $808467 but if It let the code run, I get the value 1). This sounds irrational but that is the problem I need to fix. I also would appreciate any guidance on how to calculate the mean for values in the column E of my worksheet, using VBA because, as mentioned above, WorksheetFunction is no go.

I searched for reasons or previous posts on Excel limitations (if any like versions) but have not been to find anything that I use to overcome the problem.

Here is the relevant code snippet from a macro I am putting together:

Sub Stats(NewLastRow, fMax, fMin, FMean, fMedian, fSpread)

'NewLastRow = 22 (end row)
'i =6 (start row)

'Sample data in column E
'(E6 value is $8084467 while E22 is $0)

Dim i as integer
Dim NewRngStart as Range
Dim fMax as long
Dim fMin as long
Dim fMean as long


'TO FIND MAXIMUM VALUE OF DATA IN COL E
For i =6 to NewLastRow
    with wbTarget.worksheets("Sheet1")
        if (Range("E" & i) < Range("E" & i+1) then
            fMax=CLng(Range("E & i+1).value)
        else
            If CLng(Range("E" & i) > fMax) then
                fMax=CLng(Range("E & i).value)
            end if
        End if
    End with
    if i>NewLastRow then Exit For
Next i

msgbox fMax


'TO FIND MINIMUM VALUE OF ALL DATA IN COL E 
For i =6 to NewLastRow
    with wbTarget.worksheets("Sheet1")
        if (Range("E" & i) > Range("E" & i+1) then
            fMin=CLng(Range("E & i+1).value)
        else
            If CLng(Range("E" & i) < fMin) then
                fMin=CLng(Range("E & i).value)
            end if
        End if
    End with
    if i>NewLastRow then Exit For
Next i

msgBox fMin


'TO FIND THE MEAN OF DATA IN COL E
i=6
with wbTarget.worksheets("Sheet1")
    For i =6 to NewLastRow
        fmean=fMean+CLng(Range("E" & i).value)
    Next i
    if i>NewLastRow then Exit For
    fMean=fMean/NewLastRow
End With
MsgBox fMean

The code gives different answers when run by stepping through it line by line (F8) versus when running the complied code.

Can someone advise what I am doing wrong, please?

Solution

Here are 3 functions you can use for MAX, MIN and MEAN :

MAX

With wbTarget.worksheets("Sheet1")
    
    fMax = .Range("A" & 6).Value '--Initialisation of fMax with first value--
        
    For i = 6 To (NewLastRow - 1) '--Loop until (NewLastRow - 1) because always check the next value (i+1)--
            
        If (fMax < .Range("E" & i + 1).Value) Then '--If current value is bigger--
                
            fMax = .Range("E" & i + 1).Value '--Define it as current MAX
            
        End If
        
    Next i
    
End With

MsgBox (fMax)

MIN

With wbTarget.worksheets("Sheet1")
    
    fMin = .Range("A" & 6).Value '--Initialisation of fMax with first value--
        
    For i = 6 To (NewLastRow - 1) '--Loop until (NewLastRow - 1) because we always check the next value (i+1)--
            
        If (fMin > .Range("E" & i + 1).Value) Then '--If current value is smaller--
                
            fMin = .Range("E" & i + 1).Value '--Define it as current MIN
            
        End If
        
    Next i
    
End With

MsgBox (fMin)

MEAN

With wbTarget.worksheets("Sheet1")
    
    fMean = 0 '--Initialisation of the Mean to 0 this suppose that at least 1 of your value is not blank or that a mean of blank values give 0
    
    For i = 6 To NewLastRow '--Loop from first value (6) to last value (NewLastRow)--

        fMean = fMean + .Range("E" & i).Value '--We calculate the sum--
        
    Next i

    fMean = fMean / (NewLastRow - 6 + 1) '--We divide the sum by the number of elements to get the mean--
    
End With

MsgBox (fMean)

Here are a few observations regarding your initial code :

if i>NewLastRow then Exit For this is useless in a for loop, it would have been neccesary in a while True loop but one purpose of the for loop is to avoid that.
CLng is not mandatory, it will depend on your data type.
When using With you need to call all the methods (Range for example) with a "." sign for example .Range("E10")
Many typo errors are in the code fMin=CLng(Range("E & i+1).value) for example should be replaced by fMin=CLng(Range("E" & i+1).value). If the code ran, the only explanation I have is that you have On Error Resume Next somewhere. This should / must be avoided.

The comments in my codes are not real comments (they would have been really bad, in my opinion, since they are only describing the WHAT and not the WHY). They are only here to hoppefully help Ven better understand.