How to avoid open the code page?

[marlonsaveri]

When an error occurs, I use "On error goto" to solve, to prevent code page appears. However, sometime it doesn't works and a 1004 Error appears.
In my case:
1004 error, It is not possible get Average property from WorksheetFunction

WorksheetFunction.Average use to work ok; but, sometimes (I don't know why) the code stops run.

1st. How can I make a check like this?

IF "error does not happen with average function", averagefunction.
Else, don't call average.

Because we don't want code page is showed.

2nd. Actually, this is the code. I dont understand it neither; why my labels dont shows decimal numbers, just entires?

 maxi = Format(WorksheetFunction.max(interval), "##0.0000")
    mini = Format(WorksheetFunction.min(interval), "##0.0000")
    myAverage = Format(WorksheetFunction.Average(interval), "##0.0000")
 
    LabelMax.Caption = "Max: " & maxi
    LabelMin.Caption = "Min: " & mini
    LabelMed.Caption = "Av: " & myAverage

 

[GlennUK]

You say that you are using On Error Goto ... but you're not showing that in your code. And you don't show the maxi mini and myAverage declarations ... what have they been declared as?

 

[marlonsaveri]

as long.

 

[RoryA]

They should be declared as Double. (Longs are still integers.)

 

[GTO]

Also, although it doesn't error, I don't think assigning the return of Format will do anything.

Sub exa()
Dim i As Double
    
    
    i = Format(12, "##0.0000")
    MsgBox i
    
    MsgBox Format(i, "##0.0000")
    
    i = Format(12.333, "##0.0000")
    MsgBox i
    
    MsgBox Format(i, "##0.0000")
    
End Sub

 

[shg]

They are receiving a formatted value; they should be declared as String.

 

[GlennUK]

They are receiving a formatted value; they should be declared as String.

I concur!

 

[marlonsaveri]

They are receiving a formatted value; they should be declared as String.

So, the best way is to declare as string?

However, "interval" is a range of cells, so, I could not use this, could I?

 Format(WorksheetFunction.max(interval), "##0.0000")

or

For Each mycell on interval
   If IsNumeric(mycell) Then
       if max < val(mycell) Then
          max = val(mycell)
       End If
   End If
Next

When I tried:

    maxi = Format(WorksheetFunction.max(interval), "##0.0000")
    mini = Format(WorksheetFunction.min(interval), "##0.0000")
    medi = Format(WorksheetFunction.Average(interval), "##0.0000")
 
    LabelMax.Caption = "Max: " & val(maxi)
    LabelMin.Caption = "Min: " & val(mini)
    LabelMed.Caption = "Av: " & val(medi)
'maxi, mini, medi as string

A error happens when there's nothing in the group of cells (= interval) or when there's something different of a number. Error 5; invalid argument or invalid procedure call.

For example, if interval are empty cells or if there is a letter, parentesis, etc inside it, the code doesn't run. Error 5 (max, min and average) or Error 1004 (just to average)