How to debug runtime error code?

[Dave Smith]

Hi Everyone,

When trying to run this vba i'm getting a Debug error. I'm new to VBA so any help is appresheated.

I'm guessing my placment of statements are incorrect, but I'm sure a move advaced VBA programmer will see what I'm doing wrong

Public Function GetXLStDev(No1 As Double, No2 As Double, No3 As Double, No4 As Double, No5 As Double, No6 As Double) As Double
   Dim objExcel As Object
   Set objExcel = CreateObject("Excel.Application")
   
   
   Let GetXLStDev = objExcel.StDev(No1, No2, No3, No4, No5, No6)
   
   Dim dblSum As Double, dblAvg As Double
Dim intCount As Integer
dblSum = (No1 + No2 + No3 + No4 + No5 + No6)
intCount = IIf(No1 > 0, 1, 0) _
+ IIf(No2 > 0, 1, 0) _
+ IIf(No3 > 0, 1, 0) _
+ IIf(No4 > 0, 1, 0) _
+ IIf(No5 > 0, 1, 0) _
+ IIf(No6 > 0, 1, 0)
dblAvg = dblSum / intCount
GetXLStDev = Sqr(((No1 - IIf(No1 = 0, 0, dblAvg)) ^ 2 _
+ (No2 - IIf(No2 = 0, 0, dblAvg)) ^ 2 _
+ (No3 - IIf(No3 = 0, 0, dblAvg)) ^ 2 _
+ (No4 - IIf(No4 = 0, 0, dblAvg)) ^ 2 _
+ (No5 - IIf(No5 = 0, 0, dblAvg)) ^ 2 _
+ (No6 - IIf(No6 = 0, 0, dblAvg)) ^ 2) _
/ (intCount - 1))
   
   objExcel.Quit
   Set objExcel = Nothing
End Function

Public Function Pause(PauseSeconds As Double)

Dim Start
Start = Timer
Do While Timer < Start + PauseSeconds
DoEvents
Loop

End Function

Error:
Run-time error '6':

Overflow

Then this part is highlighted:

GetXLStDev = Sqr(((No1 - IIf(No1 = 0, 0, dblAvg)) ^ 2 _
+ (No2 - IIf(No2 = 0, 0, dblAvg)) ^ 2 _
+ (No3 - IIf(No3 = 0, 0, dblAvg)) ^ 2 _
+ (No4 - IIf(No4 = 0, 0, dblAvg)) ^ 2 _
+ (No5 - IIf(No5 = 0, 0, dblAvg)) ^ 2 _
+ (No6 - IIf(No6 = 0, 0, dblAvg)) ^ 2) _
/ (intCount - 1))

all of this is highlighted with the arrow pointing to ->/ (intCount - 1))

[Rabbit]

What line is it stopping on? I suspect it's line 6 where you have a Let instead of a Set. Although I question if Set would even be necessary.

[Rabbit]

You're probably dividing by 0.

[Dave Smith]

Thanks Rabbit - what would I need to do to fix it? or do I need to do something totally different?

[ADezii]

1. Set a Reference to the Microsoft Excel XX.X Object Library.
2. Unless I am reading the Code incorrectly, In Line #6 you are setting the Return Value of the Function equal to the Standard Deviation of 6 Doubles as calculated by Excel. The assignment Operator Let is not needed.
3. In Line #18, you appear to be re-assigning the Return Value of the Function equal to an Expression. What Results are you looking for exactly?

[Dave Smith]

Thanks ADezil, I'm still new to VBA, would you be able to help me put the code together corretly?

Public Function GetXLStDev(No1 As Double, No2 As Double, No3 As Double, No4 As Double, No5 As Double, No6 As Double) As Double
   Dim objExcel As Object
   
   
   Set objExcel = CreateObject("Excel.Application")
      
      Dim dblSum As Double, dblAvg As Double
Dim intCount As Integer
dblSum = (No1 + No2 + No3 + No4 + No5 + No6)
intCount = IIf(No1 > 0, 1, 0) _
+ IIf(No2 > 0, 1, 0) _
+ IIf(No3 > 0, 1, 0) _
+ IIf(No4 > 0, 1, 0) _
+ IIf(No5 > 0, 1, 0) _
+ IIf(No6 > 0, 1, 0)
dblAvg = dblSum / intCount
GetXLStDev = Sqr(((No1 - IIf(No1 = 0, 0, dblAvg)) ^ 2 _
+ (No2 - IIf(No2 = 0, 0, dblAvg)) ^ 2 _
+ (No3 - IIf(No3 = 0, 0, dblAvg)) ^ 2 _
+ (No4 - IIf(No4 = 0, 0, dblAvg)) ^ 2 _
+ (No5 - IIf(No5 = 0, 0, dblAvg)) ^ 2 _
+ (No6 - IIf(No6 = 0, 0, dblAvg)) ^ 2) _
/ (intCount - 1))
   

   Let GetXLStDev = objExcel.StDev(No1, No2, No3, No4, No5, No6)
   
   
   objExcel.Quit
   Set objExcel = Nothing
End Function

Public Function Pause(PauseSeconds As Double)

Dim Start
Start = Timer
Do While Timer < Start + PauseSeconds
DoEvents
Loop

[ADezii]

It appears as though intCount cannot be <=1, but a critically placed Line of Code should solve that (Line #13).

Dim dblSum As Double, dblAvg As Double
Dim intCount As Integer

dblSum = (No1 + No2 + No3 + No4 + No5 + No6)

intCount = IIf(No1 > 0, 1, 0) _
+ IIf(No2 > 0, 1, 0) _
+ IIf(No3 > 0, 1, 0) _
+ IIf(No4 > 0, 1, 0) _
+ IIf(No5 > 0, 1, 0) _
+ IIf(No6 > 0, 1, 0)

If intCount <= 1 Then intCount = 99      'Substitute your Value for 99,
                                         'Exit the Function, return a Null
                                         'Value, etc...

dblAvg = dblSum / intCount

GetXLStDev = Sqr(((No1 - IIf(No1 = 0, 0, dblAvg)) ^ 2 _
+ (No2 - IIf(No2 = 0, 0, dblAvg)) ^ 2 _
+ (No3 - IIf(No3 = 0, 0, dblAvg)) ^ 2 _
+ (No4 - IIf(No4 = 0, 0, dblAvg)) ^ 2 _
+ (No5 - IIf(No5 = 0, 0, dblAvg)) ^ 2 _
+ (No6 - IIf(No6 = 0, 0, dblAvg)) ^ 2) _
/ (intCount - 1))

P.S. - I'm still noy sure what you are trying to accomplish given Code Line #s 17 and 26 in your Post. Maybe I'm missing something that Rabbit will pick up.

[Dave Smith]

I dont know how to add to this posting, so I added my DB to the posting below. Can you help me understand how I put the code in also?

http://bytes.com/topic/access/answers/907109-help-debug-vba

[Dave Smith]

ADezii/Rabbit

I'm still getting error's. I'm sorry I'm so ignorant about VBA, I really appreseat you taking the time to help me with this and help me learn.

Please help me understand what " 'Substitute your Value for 99 means. sorry again

[ADezii]

You still have not answered my basic question, but if you are trying to Calculate the Standard Deviation of 6 Values passed to a Function, and have Excel do the Calculations and return the Result to your Function, then:

Public Function GetXLStDev_2(No1 As Double, No2 As Double, No3 As Double, No4 As Double, No5 As Double, No6 As Double) As Double
Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")

GetXLStDev_2 = objExcel.Application.StDev(No1, No2, No3, No4, No5, No6)
   
   
objExcel.Quit
Set objExcel = Nothing
End Function

Sample Usage:

MsgBox "The Standard Deviation of 24, 97, 12, 456, 3, and 976 is: [" & _
        GetXLStDev_2(24, 97, 12, 456, 3, 976) & "]"

[Rabbit]

I'm still noy sure what you are trying to accomplish given Code Line #s 17 and 26 in your Post. Maybe I'm missing something that Rabbit will pick up.

Sorry, I have no idea why he's doing that either.

[ADezii]

Thanks Rabbit, I thought it may have been something that I missed.

[Stewart Ross]

I'm sorry to say I think the approach is too flawed to be salvageable without simply starting again. Referring to post 1 there is an immediate flaw in line 6, where a call to the Excel Standard Deviation function is attempted. To use an Excel worksheet function you need to do so using the WorkSheetFuncti on method of the application object:

objExcel.WorksheetFunction.StDev(No1, No2, No3, No4, No5, No6)

It looks to me like you are trying to implement a standard deviation calculation from scratch, in which you'd take the root mean square deviation from the mean of the values supplied (although it is normal to divide not by N but by N-1 for small populations). If this is the case then you do not need the Excel StDev function as it can't help you to implement your own.

If, however, you just want to calculate the standard deviation then use the Excel one and leave out all the other sum and mean calculations which are being done.

-Stewart

[ADezii]

@Stewart - The StDev() Excel Function can also be directly called from Excel's Application Object, as indicated in Post #10. Is there an advantage to injecting another Object into the Expression?

As you previously have indicated, it appears as if the OP is using two Methods of calculating the Standard Deviation (Excel and Custom), as indicated by the Code and Results posted below:

Public Function GetXLStDev(No1 As Double, No2 As Double, No3 As Double, No4 As Double, No5 As Double, No6 As Double) As Double
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
  
Debug.Print objExcel.StDev(No1, No2, No3, No4, No5, No6)
  
Dim dblSum As Double
Dim dblAvg As Double
Dim intCount As Integer

dblSum = (No1 + No2 + No3 + No4 + No5 + No6)

intCount = IIf(No1 > 0, 1, 0) _
+ IIf(No2 > 0, 1, 0) _
+ IIf(No3 > 0, 1, 0) _
+ IIf(No4 > 0, 1, 0) _
+ IIf(No5 > 0, 1, 0) _
+ IIf(No6 > 0, 1, 0)

dblAvg = dblSum / intCount

GetXLStDev = Sqr(((No1 - IIf(No1 = 0, 0, dblAvg)) ^ 2 _
+ (No2 - IIf(No2 = 0, 0, dblAvg)) ^ 2 _
+ (No3 - IIf(No3 = 0, 0, dblAvg)) ^ 2 _
+ (No4 - IIf(No4 = 0, 0, dblAvg)) ^ 2 _
+ (No5 - IIf(No5 = 0, 0, dblAvg)) ^ 2 _
+ (No6 - IIf(No6 = 0, 0, dblAvg)) ^ 2) _
/ (intCount - 1))

objExcel.Quit
Set objExcel = Nothing
End Function

Debug.Print GetXLStDev(21,5 2,3,34,95,76) will yield:

34.4987922493914 
34.4987922493914

Unfortunately intCount in the Custom Code cannot be <=1, or an Error will occur.

[Stewart Ross]

@ADezii - if the application object can provide it that's fine. It's not how MS themselves describe how to access worksheet functions (see for example this MSDN page: http://msdn.microsoft.com/en-us/libr...ffice.11).aspx).

One practical drawback of using the application object directly is that the editor's Intellisense is not activated by direct use of the application object, so the parameter usage of the function concerned is not shown to the user, nor is the list of worksheet functions available to VBA.

Using <application> .WorksheetFuncti on.<Function> activates the normal intellisense display of the arguments concerned, and also on typing the dot after WorksheetFuncti on the editor shows users all potential functions that could be used.

Without testing all functions I can't be sure if they are all available directly from the Application object without going through the WorksheetFuncti on method, but I guess it would be inconsistent if this were so.

Finally in my reasons for using this method, we have an Insights article by NeoPa which incorporates the use of the WorksheetFuncti on approach (from a post originally by Scott Price): http://bytes.com/topic/access/insigh...nctions-access

-Stewart