Overflow error in vbscript

Question

I am getting an "Overflow" error on line 40 of the following script:

Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Excel|*.xls|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "D:\"   
intResult = objDialog.ShowOpen 

If intResult = 0 Then
Wscript.Quit
 Else
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(objDialog.FileName) 
End If


objExcel.Visible = True
Set objWorksheet2 = objWorkbook.Worksheets("Foaie1") 
objWorksheet2.Activate 


objExcel.ScreenUpdating = False
objExcel.Calculation = xlCalculationManual


Set RngVal = objWorksheet2.Range("D1").CurrentRegion.Columns(4)
Set RngCrt = RngVal.Offset(0, -3)

objWorksheet2.Select
objWorksheet2.Range("A2").Select

Do Until IsEmpty(ActiveCell)

If ActiveCell.Value <> ActiveCell.Offset(-1, 0).Value Then
Criteria = ActiveCell.Offset(-1, 0).Value
ActiveCell.Offset(-1, 7).Value = objExcel.WorksheetFunction.SumIf(RngCrt, Criteria, RngVal) / objExcel.WorksheetFunction.CountIf(RngCrt, Criteria)
End If
ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Offset(-1, 7).Value = objExcel.WorksheetFunction.SumIf(RngCrt, Criteria, RngVal) / objExcel.WorksheetFunction.CountIf(RngCrt, Criteria)

objExcel.Calculation = xlCalculationAutomatic
objExcel.ScreenUpdating = True

The script calculates the average of column "D", and when the value from "A" changes, puts the result in "H".

Example:

 1. "A"         "D"                "H"
 2. 07:36:16    8.4
 3. 07:36:16    3.4                 5.9
 4. 07:36:17    2.1

What Tim said, plus "overflow" generally implies that you are trying to put a value into a variable that can't handle that value. Since you didn't show how your variables are declared, I don't know how we can help. — JimmyPena
– JimmyPena, Commented Nov 7, 2011 at 13:59
If you are running Windows Server 2003 x64 or Windows XP x64 you may be affected by a fault in the C++ Runtime library and can download a hotfix from Microsoft. For further details, refer to Microsoft KB955536 - VBScript throws a floating-point exception when the C runtime returns a stale floating-point status flag in a Windows Server 2003 environment. — Stephen Quan
– Stephen Quan, Commented Oct 3, 2012 at 1:52

Hans Olsson · Accepted Answer · 2011-11-08 12:14:05Z

2

In your code above the line

ActiveCell.Offset(-1, 7).Value = objExcel.WorksheetFunction.SumIf(RngCrt, Criteria, RngVal) / objExcel.WorksheetFunction.CountIf(RngCrt, Criteria)

Is line 41, so I assume that an extra newline has been added to that sample by mistake because I'm quite sure that's the line that fails.

So the easy way to figure out why it fails is to look at the values being calculated, so try changing that line into something like:

value1 = objExcel.WorksheetFunction.SumIf(RngCrt, Criteria, RngVal)
value2 = objExcel.WorksheetFunction.CountIf(RngCrt, Criteria)
msgbox value1
msgbox value2
ActiveCell.Offset(-1, 7).Value = value1 / value2

I assume that the values shown in the messageboxes won't be what you expect and so will hopefully show you where there error is.

answered Nov 8, 2011 at 12:14

Hans Olsson

55.1k16 gold badges99 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Overflow error in vbscript

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related