VBA overflow error Double/Long

[epactheactor]

Hello!

I'm writing a macro and I keep getting an overflow error. At first I believed it was because I was using Integer and the value was too large for that data type. But I've tried Long and Double to the same error. What am I missing?

Values being used;
material = 35000
txtWT = .5
txtDia = 8.625

This is the formula;

answer = (material/(txtWT * 2)) / txtDia

I split it up to see if maybe that was the problem to this;

X = txtWT * 2
Y = material / X
answer = Y / txtDia

Thank you for any help.

 

[steve the fish]

What do you have all variables dimensioned as?

 

[joeu2004]

You neglect to show the declarations, if any. The macro below works just fine for me, with and without declarations, and with and without the redundant parentheses around the first division.

I suggest that you add Option Explicit at the top of the module. That requires that you declare every variable, thereby highlighting any typos (like txtTW and txDia) -- although any such typos would cause a div-by-zero error, not an overflow.

Finally, VBA often reports misleading errors on the wrong line, specifically the line following where the error really is. I wonder if the "overflow" error really indicates an assignment (storing) to an incorrect range reference.

If none of these wild-guesses helps, I suggest that you post the complete VBA procedure, ideally pared down to the minimum that demonstrates the error.

My macro, which works....

Option Explicit

Sub doit()
Dim material As Long
Dim txtWT As Double, txtDia As Double
Dim answer As Double, X As Double, Y As Double

material = 35000
txtWT = 0.5
txtDia = 8.625
answer = (material / (txtWT * 2)) / txtDia
MsgBox answer

X = txtWT * 2
Y = material / X
answer = Y / txtDia
MsgBox answer
End Sub

 

[joeu2004]

PS....

I suggest that you post the complete VBA procedure, ideally pared down to the minimum that demonstrates the error.

And if you do, be sure to copy-and-paste from the VBA editor and put "code tags" around the pasted text; click on the "#" icon in the forum's editor toolbar, or type "code" and "/code", each surrounded by square brackets.

When you retype code, as you did in your initial posting, you might unconsciously correct the very mistake that causes the error.

 

[epactheactor]

Hey Joeu,

I believe you are correct that the error is not with this calculation but what is right before it. I use the variable of "material" for the 35000 but found that the macro is assigning 0 to it instead which would lead to the issue. Again, I'm a bit confused as I've used code like this before without any errors.
This is the snippet of that code. It doesn't seem to populate the variable material for some reason. The example I use is B.

Dim txtGrade As String
Dim material As Long

If txtGrade = "A" Then
    material = 30000
ElseIf txtGrade = "B" Then
    material = 35000
ElseIf txtGrade = "x-42" Or txtGrade = "X-42" Or txtGrade = "X42" Or txtGrade = "x42" Then
    material = 42000
ElseIf txtGrade = "x-52" Or txtGrade = "X-52" Or txtGrade = "X52" Or txtGrade = "x52" Then
    material = 52000
ElseIf txtGrade = "x-60" Or txtGrade = "X-60" Or txtGrade = "X60" Or txtGrade = "x60" Then
    material = 60000
ElseIf txtGrade = "x-70" Or txtGrade = "X-70" Or txtGrade = "X70" Or txtGrade = "x70" Then
    material = 70000
End If

I'm hoping it is something stupid that I'm doing that I just can't see.

You neglect to show the declarations, if any. The macro below works just fine for me, with and without declarations, and with and without the redundant parentheses around the first division.

I suggest that you add Option Explicit at the top of the module. That requires that you declare every variable, thereby highlighting any typos (like txtTW and txDia) -- although any such typos would cause a div-by-zero error, not an overflow.

Finally, VBA often reports misleading errors on the wrong line, specifically the line following where the error really is. I wonder if the "overflow" error really indicates an assignment (storing) to an incorrect range reference.

If none of these wild-guesses helps, I suggest that you post the complete VBA procedure, ideally pared down to the minimum that demonstrates the error.

My macro, which works....

Option Explicit

Sub doit()
Dim material As Long
Dim txtWT As Double, txtDia As Double
Dim answer As Double, X As Double, Y As Double

material = 35000
txtWT = 0.5
txtDia = 8.625
answer = (material / (txtWT * 2)) / txtDia
MsgBox answer

X = txtWT * 2
Y = material / X
answer = Y / txtDia
MsgBox answer
End Sub

 

[epactheactor]

Just in case this is the complete code so far

Private Sub CommandButton1_Click()Option Explicit


Dim txtWT As Double
Dim txtDia As Double
Dim txtYear As Long
Dim txtGrade As String
Dim material As Long






Dim X As Double
Dim Y As Double






'Grade to SMYS
If txtGrade = "A" Then
    material = 30000
ElseIf txtGrade = "B" Then
    material = 35000
ElseIf txtGrade = "x-42" Or txtGrade = "X-42" Or txtGrade = "X42" Or txtGrade = "x42" Then
    material = 42000
ElseIf txtGrade = "x-52" Or txtGrade = "X-52" Or txtGrade = "X52" Or txtGrade = "x52" Then
    material = 52000
ElseIf txtGrade = "x-60" Or txtGrade = "X-60" Or txtGrade = "X60" Or txtGrade = "x60" Then
    material = 60000
ElseIf txtGrade = "x-70" Or txtGrade = "X-70" Or txtGrade = "X70" Or txtGrade = "x70" Then
    material = 70000
End If


MsgBox material


'100% SMYS
X = txtWT * 2
Y = material / X
SMYS_full = Y / txtDia








End Sub

 

[joeu2004]

Obviously, you did not follow instructions and copy-and-paste from VBA. The Option Explicit statement is misplaced. [1]

And when properly placed before the Sub statement, we see that SMYS_full is not declared (it defaults to type Variant). Moreover, this example bears no resemblance to the code that you posted originally.

Be that as it may, the problem here is: material is zero, and so is txtWT. An error occurs on the statement Y = material / X, resulting in 0/0, which is treated as an overflow instead of div-by-zero.

In the future, you can isolate the offending statement by pressing ctrl-f8 to run to the statement X = txtWT*2, then pressing f8 to single-step. You can hover the cursor over variables in order to see their values.


-----
[1] I shouldn't be so harsh. Perhaps the misplacement of the Option statement was a forum applet or browser paste error. But the fact is: the code would not run at all with a properly-place Option statement. That's what raised suspicions about the example.

 

[shg]

The variable txtGrade hasn't been initialized to anything, so it's a zero-length string.

 

[epactheactor]

Please help me understand something, how is material variable 0 if the If statement assigns it a value? (txtGrade is entered via a form). To counter this, what do I need to do, give it a temporary value which the if statement will overwrite?



As for the missing variables I have declared all variables I am going to use in this macro, but didn't want to show all of them as half are not used yet to confuse this already confusing issue so I accidentally deleted SYMS_full and txtWT in the example. This is my mistake, I apologize.

 

[shg]

txtGrade is a textbox on a userform? That's not how it's coded -- it's a simple local variable. That would require something like

txtGrade = myUserForm.txtGrade.Value

If you don't know how to step through code, this would be a good time to learn: http://www.cpearson.com/excel/DebuggingVBA.aspx