UserForm / VBA getting "Error 6 - overflow" for Division problem

Cacoethes

New Member
Joined
Jun 18, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Using Excel 2016 Windows, I am basically new to MrExcel and this is my first Thread. I am basically self taught and do this for enjoyment and not for any work projects. I am trying to make a pretty large UserForm program for
my first time at UserForm. So, I am finding some difference between Excel and UserForm programming but find it interesting.

My problem is that I am trying to divide two variables, each are, variable = ##.## / #.## (SliceVm = PanVt / SliceVt) and get an error 6, overflow ( the actual line is "SliceVm = Application.Round((PanVt / SliceVt), 2)".
I have tried several options but I just don't get the results without the overflow?
I have set the variables up in "Option Explicit" with Public ****** As Double and have tried Long but still get the error. I can Divide either number by an actual number and it works such as "SliceVm = PanVt / 6.73"
but, when I put them together the error occurs?
I have copied the Option Explicit, and sections of the routines that pertain to the values of the variables. I have also included the full routine of the section I am having issues with. By the way this is the first time I have ever
used Public variables, but I don't think that is an issue here. I have Also made the line with the error in BOLD for you.
Any help would be appreciated. I hope I have done this correctly in your system...
VBA Code:
Option Explicit

Public Calories As Double
Public CaloriesT As Double
Public AreaT As Double                          ' Variable for Total Area of the Pan
Public SomeVw As Double
Public SomeVl As Double
Public SliceVt As Double                         ' Variable for Customers Slice Total Area
Public SliceVm As Long                          ' Variable for the Sum of PanVt / SliceVt  
Public PanVt As Double                          ' Variable for Pan's Total Area Divided by Cust. Slice total area
'-------------------------------------------

Private Sub UserForm_Activate()
' Servings for Rectangles- Form Page

Dim Wn As Integer
Dim Wsn As Integer
Dim Ln As Integer
Dim Lsn As Integer

'ETC.
End Sub
'-------------------------------------------

Private Sub AreaT_txtbox_Change()

Dim SliceVst As Double                     ' Variable Sub Total
Dim SliceVr As Double
Dim CalVt As Double                          ' Variable for New CaloriesTotal
Dim CustSw As Double                       ' Variable for Customers Slice Width
Dim CustSl As Double                         ' Variable for Customers Slice Length
Dim CaloriesTotal As Double

RectangleShape_txtbox.Visible = True
Rectangle_Inst_txtbox.Visible = True

If (AreaT_txtbox > 0) Then
    Call Show_Custom_Slices
    Call Show_Middle_Column

    '----- Enter Size of Pan in Middle Column  -----------------------
    Header_Inst2_txtbox.Value = Application.Text(AreaT_txtbox, "###.00")         ' Area Total of Rectangle Pan
    AreaT = Val(AreaT_txtbox.Value)

ETC.
End Sub
'----------------------------------------------

Private Sub Width_Length_Slice_Fract()
' Calculate for Slices Width & Length

Dim Wfs As Double
Dim Lfs As Double

'--- Converting Width Fractions into Decimals --------------------------------
If (WidthFract_Slice_cobo.ListIndex <> -1) Then
    WidthFract_Slice_cobo.Text = (WidthFract_Slice_cobo.List(WidthFract_Slice_cobo.ListIndex))
End If

If (WidthFract_Slice_cobo.Text = "0") Then
    Wfs = 0
    GoTo ByPass1
End If
If (WidthFract_Slice_cobo.Text = "1/8") Then
   Wfs = 0.125
    GoTo ByPass1
End If
If (WidthFract_Slice_cobo.Text = "1/4") Then
    Wfs = 0.25
    GoTo ByPass1
End If
If (WidthFract_Slice_cobo.Text = "3/8") Then
    Wfs = 0.375
    GoTo ByPass1
End If
If (WidthFract_Slice_cobo.Text = "1/2") Then
    Wfs = 0.5
    GoTo ByPass1
End If
If (WidthFract_Slice_cobo.Text = "5/8") Then
    Wfs = 0.625
    GoTo ByPass1
End If
If (WidthFract_Slice_cobo.Text = "3/4") Then
    Wfs = 0.75
    GoTo ByPass1
End If
If (WidthFract_Slice_cobo.Text = "7/8") Then
    Wfs = 0.875
End If

ByPass1:

'--- Converting Length Fractions into Decimals --------------------------------
If (LengthFract_Slice_cobo.ListIndex <> -1) Then
    LengthFract_Slice_cobo.Text = (LengthFract_Slice_cobo.List(LengthFract_Slice_cobo.ListIndex))
End If

If (LengthFract_Slice_cobo.Text = "0") Then
    Lfs = 0
    GoTo ByPass2
End If
If (LengthFract_Slice_cobo.Text = "1/8") Then
    Lfs = 0.125
    GoTo ByPass2
End If
If (LengthFract_Slice_cobo.Text = "1/4") Then
    Lfs = 0.25
    GoTo ByPass2
End If
If (LengthFract_Slice_cobo.Text = "3/8") Then
    Lfs = 0.375
    GoTo ByPass2
End If
If (LengthFract_Slice_cobo.Text = "1/2") Then
    Lfs = 0.5
    GoTo ByPass2
End If
If (LengthFract_Slice_cobo.Text = "5/8") Then
    Lfs = 0.625
    GoTo ByPass2
End If
If (LengthFract_Slice_cobo.Text = "3/4") Then
    Lfs = 0.75
    GoTo ByPass2
End If
If (LengthFract_Slice_cobo.Text = "7/8") Then
    Lfs = 0.875
    GoTo ByPass2
End If

ByPass2:

'----- Width Slice Custom Size ------------------------------------
SomeVw = WidthNo_Slice_cobo + Wfs                               'CDbl = Change to Dim Double
WidthSliceT_txtbox = Application.Text(SomeVw, "# ??/??")
CustInch0w_txtbox = Application.Text(SomeVw, "# ??/??")

'----- Length Slice Custom Size ------------------------------------
SomeVl = LengthNo_Slice_cobo + Lfs
LengthSliceT_txtbox = Application.Text(SomeVl, "# ??/??")
CustInch0l_txtbox = Application.Text(SomeVl, "# ??/??")

    '----- Strip 0 -  Custom Slice ----------------------
If (WidthSliceT_txtbox > "0") And (LengthSliceT_txtbox > "0") Then
    PanVt = Application.Round(AreaT, 2)
    SliceVt = Application.Round((SomeVw * SomeVl), 2)
    If (PanVt >= SliceVt) Then
    [B]SliceVm = Application.Round((PanVt / SliceVt), 2)[/B]
        People0_Value_txtbox.Value = Application.RoundDown(SliceVm, 0)
    Else
         CustInch0w_txtbox = " "
         CustInch0l_txtbox = " "
    End If
   
'----- My Testing Textboxes for Results ---------------------  
    Test1_txtbox = "PanVt = " & PanVt
    Test2_txtbox = "SliceVt = " & SliceVt
    Test3_txtbox = "SliceVm = " & SliceVm
    Test4_txtbox = "# People = " & People0_Value_txtbox

End Sub
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,547
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Are you sure SliceVt is not 0? You should test for that before doing the division.
 

Cacoethes

New Member
Joined
Jun 18, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Thank you Roy for your reply. I thought of that and if I make the error line as a remark so the program plays out.
If you noticed at the bottom of my program I have several "Test1_txtbox that shows what my results are before reaching that line and they show numbers like 64.68, 4.36 etc. or just an integer if I don't use fractions for my input. Either way when I start my program I immediately get the error 6 overflow.
I tried using "SliceVm = CDbl(PanVt) / CDbl(SliceVt) ect. and I can't get the program to work, keep getting overflow. I also tried making all my variables pertaining to that line Long and nothing works.

Test1_txtbox = "PanVt = " & PanVt
Test2_txtbox = "SliceVt = " & SliceVt
Test3_txtbox = "SliceVm = " & SliceVm
Test4_txtbox = "# People = " & People0_Value_txtbox
 

Cacoethes

New Member
Joined
Jun 18, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Oh Roy, one thing I forgot to mention is, this routine doesn't work until after the program starts and data is chosen from other routines within the same Userform. So These variables really don't have a value when starting the program.
Before sending this, at the beginning of the UserForm_Activate() I gave all the variables = 0 and still get overflow, I made the variables = 1 and get "ERROR 11 Division by 0" which doesn't make sense since you can divide 1 into 1.

Any suggestions would be greatly appreciated.
 

Cacoethes

New Member
Joined
Jun 18, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
OK I "SOLVED" my issue but not the question why?
As I run the UserForm the variables are not equal to 0 when I get to the variable equations! BUT... when I Run and compile it, it returns an error... either as "error 6 overflow" or "error 11 division by zero".
To resolve this I placed an if statement "If (variable > 0) then continue on, if not bypass this section! This Routine only runs when the variables have already become larger than 0!
I don't understand why when compiling, it looks as values and not just format? Because if it would let me run the program the values would not be 0 by the time it reaches the equation!

I'm just confused because I have other software. example an electronic design software that when it compiles, this is not an issue.
Can anyone explain this without having to go into a long dissertation :)? Or, should I just let it go and just live with it as a learning experience?

I have learned... the more I learn... I learn, the less I know!
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,128,167
Messages
5,629,079
Members
416,364
Latest member
maatpsr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top