VBA Project help and overflow error

[Goallie11]

For my final project in my computer apps class we have to write a VBA program to dip a straw into a glass of water, remove the amount of water in the straw and repeat until the water level in the glass is 0.5 inches.

I'm getting overflow error 6 at the 5th line from the bottom and dont know why. This is the same code my instructor wrote during class and it worked for him.

[VBA]Dim Rs As Double
Dim iRow As Integer
Dim iCol As Integer
Dim nDips As Integer
Dim Cuph As Integer
Dim Rc As Double
Dim Volc As Integer
Dim Vols As Integer
Dim Hn As Integer

'Initial Straw Radius and column number
Rs = 0.2
iCol = 1
'Clear work area
Range("A1:K2000").Clear
'iterate through straw sizes
Do While Rs <= 0.5
'initialize data
nDips = 0
iRow = 2
Cuph = 10
Cells(1, iCol) = "Rs=" & Rs
'Column Reader
Cells(iRow, iCol) = "nDips"
Cells(iRow, iCol) = "Cup Height"
iRow = iRow + 1
'write initial values
Cells(iRow, iCol) = nDips
Cells(iRow, iCol) = Cuph
iRow = iRow + 1
'iterate til final cup height is established
Do While Cuph >= 0.5
'Calculate cup volume using cup radius and water level
'Calculate straw volume using straw radius and water level
'Get new cup volume after removing the water in the straw
'Calculate new water level
Cuph = Cuph - Rs
nDips = nDips + 1
Cells(iRow, iCol) = nDips
Cells(iRow, iCol) = Cuph
iRow = iRow + 1
Loop
iCol = iCol + 2
Rs = Rs + 0.05
Loop[/VBA]

[Bob Phillips]

An initial guess, change all of the Integer types to Long. Integer only holds up to 32,767, whilst longs go up to 2,147,483,647.

[Goallie11]

Just did that and it got rid of that error but another one came up, 1004 application-defined or object defined error.

Thats in row 7 from the bottom "Cells(iRow, iCol) = nDips"

Also they way it is now it should subtract the Rs from the cupH and get a new cuph, and repeat. But it just goes on and on and on with the cuph staying at 10.

Example, it should do this

Rs = 0.2
nDips Cup Height
0 10
1 9.5


Yet it only shows Cup Height, and it remains at 10.

[Goallie11]

Anyone have any ideas?

I really can't debug this stuff because I haven't done it long.

[Bob Phillips]

Doi you have a workbook to try it on?

[Goallie11]

I have the workbook that I'm writing it in, all that's in it though is the command button.

[Aussiebear]

Then how did you test the code?

[Goallie11]

Click the button and it's supposed to fill everything in on the worksheet. It's all contained in the code so it doesn't need outside input.

[Bob Phillips]

Your workbook will save us recreating it all.

[Goallie11]

The .xls file is 2.82 MB so its too big to attach. Sorry but how do I attach it now?

[Goallie11]

Ok so I worked on the code a bit and got the numbers to line up correctly so they now display how they should:

Rs=0.2
Cup Height---nDips
10-------------0

And I added my functions, so now the code looks like this

[VBA]Dim Rs As Double
Dim iRow As Integer
Dim iCol As Integer
Dim nDips As Integer
Dim Cuph As Integer
Dim Rc As Double
Dim Volc As Integer
Dim Vols As Integer
Dim Hn As Integer

'Initial Straw Radius and column number
Rs = 0.2
iCol = 1
'Clear work area
Range("A1:K2000").Clear
'iterate through straw sizes
Do While Rs <= 0.5
'initialize data
nDips = 0
iRow = 2
Cuph = 10
Cells(1, iCol) = "Rs=" & Rs
'Column Reader
Cells(iRow, iCol) = "nDips"
Cells(iRow, iCol) = "Cup Height"
iRow = iRow + 1
'write initial values
Cells(iRow, iCol) = nDips
Cells(iRow, iCol) = Cuph
iRow = iRow + 1
'iterate til final cup height is established
Do While Cuph >= 0.5
'Calculate cup volume using cup radius and water level
CupVolume
'Calculate straw volume using straw radius and water level
Straw Volume
'Get new cup volume after removing the water in the straw
New Cup
'Calculate new water level
NewWater
Cuph = Cuph - Rs
nDips = nDips + 1
Cells(iRow, iCol) = nDips
Cells(iRow, iCol) = Cuph
iRow = iRow + 1
Loop
iCol = iCol + 2
Rs = Rs + 0.05
Loop

Function CupVolume()
Volc = Pi * 16 * Cuph

End Function

Function StrawVolume()
Vols = Rs * Cuph

End Function

Function NewCup()
Volc = Volc - Vols

End Function

Function NewWater()
Cuph = (Pi * 16) / Volc

End Function[/VBA]

Now when it gets to that last function it does the overflow error. I know it shouldn't be going through so many iterations that it gets an error like that.

And how can I shrink the file to attach it here? Still can't figure it out.

[Blade Hunter]

Do you have some sort of reference somewhere because I can't see where you are defining Pi.

On that if you are not your formula wil end up being 0/0 which will spit an overflow error.

Maybe put a constant reference at the top Pi = 3.141592654

[Goallie11]

Oh i thought pi was something that it knew. I'm at the iron man premier so will change it when i get back and see if it works.

[Blade Hunter]

Cool mate, it should fix it for you. Enjoy the movie, it is pretty damn awesome

[Goallie11]

Finally got around to trying that and it didn't work. it's still doing the error in the same place. I put

Dim Pi as Integer
Pi = 3.141592654

But still didn't work.

[mdmackillop]

There's something here that doesn't add up!

Dim Pi as Integer
Pi = 3.141592654

[mdmackillop]

Fix your integer types, insert your Sub and End Sub lines ensuring that the variables are available to the Functions and the code will run.

[Goallie11]

Ok I changed Pi to long, the Sub and End Sub have been there all along, and it still gives the overflow error. Unless you mean I need to change Pi and everything else to something else.

Here's the full code:
[VBA]Private Sub CommandButton1_Click()
'Name
'Student number
'Final Project

Dim Rs As Double
Dim iRow As Long
Dim iCol As Long
Dim nDips As Long
Dim Cuph As Long
Dim Rc As Long
Dim Volc As Long
Dim Vols As Long
Dim Hn As Long
Dim Pi As Long

Pi = 3.141592654

'Initial Straw Radius and column number
Rs = 0.2
iCol = 1
'Clear work area
Range("A1:K2000").Clear
'iterate through straw sizes
Do While Rs <= 0.5
'initialize data
nDips = 0
iRow = 2
Cuph = 10
Cells(1, iCol) = "Rs=" & Rs
'Column Reader
Cells(iRow, iCol + 1) = "nDips"
Cells(iRow, iCol) = "Cup Height"
iRow = iRow + 1
'write initial values
Cells(iRow, iCol + 1) = nDips
Cells(iRow, iCol) = Cuph
iRow = iRow + 1
'iterate til final cup height is established
Do While Cuph >= 0.5
'Calculate cup volume using cup radius and water level
CupVolume
'Calculate straw volume using straw radius and water level
StrawVolume
'Get new cup volume after removing the water in the straw
NewCup
'Calculate new water level
NewWater
nDips = nDips + 1
Cells(iRow, iCol + 1) = nDips
Cells(iRow, iCol) = Cuph
iRow = iRow + 1
Loop
iCol = iCol + 2
Rs = Rs + 0.05
Loop

End Sub

Function CupVolume()
Volc = Pi * 16 * Cuph

End Function

Function StrawVolume()
Vols = Rs * Cuph

End Function

Function NewCup()
Volc = Volc - Vols

End Function

Function NewWater()
Cuph = (Pi * 16) / Volc

End Function[/VBA]

[mdmackillop]

Sub is in the wrong place. Step through the code to test it.
Look up the Integer and Long data types, also Single and Double.
Also look at Public Variables.

[Goallie11]

Ok so I changed it and it goes through now, but when it gets to the 314th "dip" it displays the same number over and over until it gets error 1004 at the bolded line. 8 Rows above End Sub.

[VBA]'Name
'Student number
'Final Project

Dim Rs As Double
Dim iRow As Double
Dim iCol As Integer
Dim nDips As Long
Dim Cuph As Double
Dim Rc As Integer
Dim Volc As Integer
Dim Vols As Integer
Dim Hn As Integer
Dim Pi As Double
Private Sub CommandButton1_Click()

Pi = 3.141592654

'Initial Straw Radius and column number
Rs = 0.2
iCol = 1
'Clear work area
Range("A1:K2000").Clear
'iterate through straw sizes
Do While Rs <= 0.5
'initialize data
nDips = 0
iRow = 2
Cuph = 10
Cells(1, iCol) = "Rs=" & Rs
'Column Reader
Cells(iRow, iCol + 1) = "nDips"
Cells(iRow, iCol) = "Cup Height"
iRow = iRow + 1
'write initial values
Cells(iRow, iCol + 1) = nDips
Cells(iRow, iCol) = Cuph
iRow = iRow + 1
'iterate til final cup height is established
Do While Cuph >= 0.5
'Calculate cup volume using cup radius and water level
CupVolume
'Calculate straw volume using straw radius and water level
StrawVolume
'Get new cup volume after removing the water in the straw
NewCup
'Calculate new water level
NewWater
nDips = nDips + 1
Cells(iRow, iCol + 1) = nDips
Cells(iRow, iCol) = Cuph
iRow = iRow + 1
Loop
iCol = iCol + 2
Rs = Rs + 0.05
Loop

End Sub

Function CupVolume()
Volc = Pi * 16 * Cuph

End Function

Function StrawVolume()
Vols = Rs * Cuph

End Function

Function NewCup()
Volc = Volc - Vols

End Function

Function NewWater()
Cuph = Volc / (Pi * 16)

End Function
[/VBA]