Overflow error on VBA Module

[Polarfranz]

Hello,
I have two VBA-Modules that are almost the same.
The first looks like this:


Sub Versuch()
Dim Z As Double
Dim counter As Integer
Dim a As Double
Dim b As Double
Dim f As Integer
Dim e As Integer
Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count


For I = 1 To WS_Count
Worksheets(I).Select
counter = 10
Z = 1
a = Cells(8, 6).Value
b = Cells(9, 6).Value
f = Cells(3, 6).Value
e = Cells(4, 6).Value

Do Until Z > e
Cells(counter, 3).Value = Z

counter = counter + 1

If Z < f Then
Z = Z + a
End If

If Z >= f Then
Z = Z + b
End If




Loop

Next I

End Sub

The second one is slightly changed and gives me an overflow error because of the line: counter = counter +1



Sub twosignificantlayers()
Dim Z As Double
Dim counter As Integer
Dim a As Double
Dim b As Double
Dim f As Integer
Dim e As Integer
Dim WS_Count As Integer
Dim I As Integer
Dim secondlayer As Integer
Dim secondlayerstep As Integer
Dim secondtobottom As Integer


WS_Count = ActiveWorkbook.Worksheets.Count


For I = 1 To WS_Count
Worksheets(I).Select
counter = 10
Z = 1
a = Cells(8, 6).Value
b = Cells(9, 6).Value
f = Cells(3, 6).Value
e = Cells(4, 6).Value
secondlayer = Cells(5, 6).Value
secondlayerstep = Cells(11, 6).Value
secondtobottom = Cells(15, 6).Value

Do Until Z > e
Cells(counter, 3).Value = Z

counter = counter + 1

If Z < f Then
Z = Z + a
End If

If Z >= f And Z < secondlayer Then
Z = Z + secondlayerstep
End If

If Z >= secondlayer Then
Z = Z + secondtobottom
End If



Loop

Next I

End Sub


Can anyone tell me why I get this error? It seems like the programme is not jumping to the next worksheet to continue the calculation

Regards,
Franz

[Aflatoon]

It appears you have exceeded the maximum value for an integer (32767). Declare your variables as Long instead.

[snb]

Sub Versuch()
For each sh in sheets
sp=sh.cells(1,6).resize(9)
sn=sh.cells(10,3).resize(sh.cells(rows.count,3).end(xlup).row-10)

for j=1 to ubound(sn)
z=z+ sp(8-(z<sp(3,1)),1)
if z>sp(4,1) then exit for
next
if j<ubound(sn)+1 then sh.cells(j,3)=z

next
End Sub

[Polarfranz]

What does ubound(sn) in your code mean? and where do you think I should imply this into my code

[snb]

It should replace your entire code.

[Polarfranz]

Hm that did not really help. I get the error message
"Compile error: Expected array" when using your code.

The point is that I have some raw data and want to recalculate the rawdata in that way:
Let's say I have some rawdata for x and y values that look like this:


1

0.549



2

0.741



3

0.741



4

0.741



5

1.106



6

12.597



7

52.680



8

118.524



9

196.830



10

286.526



11

372.597



12

428.750



13

458.827



14

482.285



15

482.285



16

482.285



Now i want to recalculate the first column. and have not steps in size of 1 from one to the next value, but the steps are only 0.4 for the values from 1 to 5, then 1.2 from 5 to 8 and 0.8 from 8 to 16
These steps shall be shown in column 3. that the final result looks like this:




1
0.549
1


2
0.741
1.4


3
0.741
1.8


4
0.741
2.2


5
1.106
2.6


6
12.597
3.8


7
52.680
5


8
118.524
6.2


9
196.830
7


10
286.526
7.8


11
372.597
8.6


12
428.750
9.4


13
458.827
10.2


14
482.285
11


15
482.285
11.8


16
482.285
12.6



The values for the increasement (here 0.4, 1.2 and 0.8) are given in the excelsheet in the fields F8, F11 and F15 respectively.
The thresholdvalues for the increasementchange (here 5, 8 and 16) are in the excelsheet in the fields F3, F5 and F4 respectively.
The start value is always 1.
This process should end as soon it reaches the maximal value (here 16, means field F4)

[snb]

It means that your data do not match what your code suggested.
Please post a file.

[jonh]

Sub test()
Dim v As Double
v = 0.6
addval 1, 5, 0.4, v
addval 6, 8, 1.2, v
addval 9, 16, 0.8, v
End Sub


Private Sub addval(istart As Integer, iend As Integer, incr As Double, ival)
For i = istart To iend
ival = ival + incr
Cells(i, 3) = ival
Next
End Sub

[Polarfranz]

It means that your data do not match what your code suggested.
Please post a file.

16018

[Polarfranz]

jonh

Why do you putv=0.6? What is the effect of "v"?

[Polarfranz]

Sub test()
Dim v As Double
Dim a As Integer
Dim b As Integer
Dim secondlayer As Integer
Dim secondlayerstep As Integer
Dim secondtobottom As Integer
Dim f As Integer
Dim e As Integer
a = Cells(8, 6).Value
b = Cells(9, 6).Value
f = Cells(3, 6).Value
e = Cells(4, 6).Value
secondlayer = Cells(5, 6).Value
secondlayerstep = Cells(11, 6).Value
secondtobottom = Cells(15, 6).Value

v = 0.6
addval 1, f, a, v
addval 6, secondlayer, secondlayerstep, v
addval 9, e, secondtobottom, v
End Sub


Private Sub addval(istart As Integer, iend As Integer, incr As Double, ival)
Dim I As Integer
For I = istart To iend
ival = ival + incr
Cells(I, 3) = ival
Next
End Sub

I tried it with this code, but then excel complains:
Compile error:
ByRef argument type mismatch. What does that mean?

[GTO]

It is a bit more complex than this, but in gist, ByRef means By Reference or that is, we are passing the variable back and forth when we pass 'incr as Double' in addval(). Thus, a, secondlayerstep and secondtobottom need to be doubles, or incr needs to be an Integer; but we cannot alter the Type of variable that is being passed into the called Sub or Function. Does that make sense?



Dim a As Double ' Integer
Dim b As Integer
Dim secondlayer As Integer
Dim secondlayerstep As Double ' Integer
Dim secondtobottom As Double ' Integer


Hope that helps,

Mark

[snb]

So klappt's:


Sub M_snb()
For Each sh In Sheets
sp = sh.Cells(1, 6).Resize(9)
sn = sh.Cells(9, 1).CurrentRegion.Resize(, 3)

For j = 2 To UBound(sn)
sn(j, 3) = sn(j - 1, 3) + sp(8 - (sn(j - 1, 3) < sp(3, 1)), 1)
Next

sh.Cells(9, 1).CurrentRegion.Resize(, 3) = sn
Next
End Sub

[Polarfranz]

snb:
Soll ich deinen Code mit in den anderen Code einbauen, oder den ganz allein dastehen lassen? Ich versteh leider nicht genau was dieser Code bewirkt

[snb]

The code is meant to replace your 'Versuch' totally.
It writes in column C your 'Z'- values.
Du kannst die Code laufen lassen und sehen ob der Erfolg dir gefällt.

PS. kennst du http://www.office-loesung.de/p/viewforum.php?f=166

[Polarfranz]

The code is meant to replace your 'Versuch' totally.
It writes in column C your 'Z'- values.
Du kannst die Code laufen lassen und sehen ob der Erfolg dir gefällt.


It looks almost solved.
Just the code takes for the first steps not the value F8 but F9 instead.
Then on the next increasement-step it adds the value from F8 but not from F11
And it is missing a third increasement step (starting value in F5) with the added value from F15

P.S. Hab ich noch nicht gekannt ;)

[snb]

Then I think you'd better explain more in detail how results have to be calculated.(I tried to deduce it from your code)
But if you understand the logic & function of the code it should be rather simple to adapt it yourself.

[Polarfranz]

Then I think you'd better explain more in detail how results have to be calculated.(I tried to deduce it from your code)
But if you understand the logic & function of the code it should be rather simple to adapt it yourself.

I did not really understand the logic&function of the code you wrote... Otherwise it seems to be really handy.

The results should be calculated in the following way:

This it the raw-data:

1 0.549
2 0.741
3 0.741
4 0.741
5 1.106
6 12.597
7 52.680
8 118.524
9 196.830
10 286.526
11 372.597
12 428.750
13 458.827
14 482.285
15 482.285
16 482.285

I would like to recalculate the values for the first column and not have stepwidths of 1 all the time, but different changes in the increasement. As in the example above they shall look like the following (example):
From the values 1 to 5 the numbers are increased by 0.4, from higher than 5 to 8 by 1.2 and from 8 to the end by 0.8.
The new values shall be shown in the third column. Like this:

1 0.549 1
2 0.741 1.4
3 0.741 1.8
4 0.741 2.2
5 1.106 2.6
6 12.597 3.4
7 52.680 4.2
8 118.524 5
9 196.830 6.2
10 286.526 7.4
11 372.597 8.2
12 428.750 9.0
13 458.827 9.8
14 482.285 10.6
15 482.285 11.4
16 482.285 12.2

The values for the increasmentchange (here 0.4, 1.2 and 0.8) are given in the fileds F8, F11 and F15.
The thresholds for the increasement change (here 5 and 8) are given in the fileds F3 and F5.
This process shall continue until the 3rd column has as many entries as the first column(here 16). The endvalue 16 is given in the field F4.

I hope this is understandable for you :)

[jonh]

jonh

Why do you putv=0.6? What is the effect of "v"?

v=0.6 is the starting value minus the first increment.

.6+.4 = 1

As GTO said, the variable v (not just it's value), is passed between the procedures, so that it's value is updated and used as the output.


Const StartingValue As Byte = 1


Dim F8 As Range
Dim F11 As Range
Dim F15 As Range
Dim F3 As Range
Dim F5 As Range
Dim F4 As Range


Private Function validate() As Boolean
Set F8 = Range("F8") '0.4
Set F11 = Range("F11") '1.2
Set F15 = Range("F15") '0.8
Set F3 = Range("F3") '5
Set F5 = Range("F5") '8
Set F4 = Range("F4") '16

'do any validating here

validate = 1
End Function


Sub test()
If Not validate Then Exit Sub

Dim v As Double
v = StartingValue - F8

addval 1, CInt(F3), CDbl(F8), v
addval CInt(F3) + 1, CInt(F5), CDbl(F11), v
addval CInt(F5) + 1, CInt(F4), CDbl(F15), v

End Sub

Private Sub addval(istart As Integer, iend As Integer, incr As Double, ival)
For i = istart To iend
ival = ival + incr
Cells(i, 3) = ival
Next
End Sub

[Polarfranz]

v=0.6 is the starting value minus the first increment.

.6+.4 = 1

As GTO said, the variable v (not just it's value), is passed between the procedures, so that it's value is updated and used as the output.


Const StartingValue As Byte = 1


Dim F8 As Range
Dim F11 As Range
Dim F15 As Range
Dim F3 As Range
Dim F5 As Range
Dim F4 As Range


Private Function validate() As Boolean
Set F8 = Range("F8") '0.4
Set F11 = Range("F11") '1.2
Set F15 = Range("F15") '0.8
Set F3 = Range("F3") '5
Set F5 = Range("F5") '8
Set F4 = Range("F4") '16

'do any validating here

validate = 1
End Function


Sub test()
If Not validate Then Exit Sub

Dim v As Double
v = StartingValue - F8

addval 1, CInt(F3), CDbl(F8), v
addval CInt(F3) + 1, CInt(F5), CDbl(F11), v
addval CInt(F5) + 1, CInt(F4), CDbl(F15), v

End Sub

Private Sub addval(istart As Integer, iend As Integer, incr As Double, ival)
For i = istart To iend
ival = ival + incr
Cells(i, 3) = ival
Next
End Sub

This looks like what I was looking for. Can you just tell me, how can I programme it, that excel starts writing the values in column C beginning in the 10th line? So far it starts writing the values in the C-column from the 1st line, but I would like to have it started from the 10th.
And what is the easiest way to run this code in every sheet?

Thanks a lot

[jonh]

Const StartingValue As Byte = 1
Const StartAtRow As Byte = 10 ' row to start at


Private Enum c
F8
F11
F15
F3
F5
F4
End Enum


Dim ar()


Sub CalcAllSheets()

'Calculate values for all sheets

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
CalcSheet sht
Next
End Sub


Private Function validate(sht As Worksheet) As Boolean
On Error GoTo eh
ReDim ar(c.F4)
ar(c.F8) = sht.Range("F8") '0.4
ar(c.F11) = sht.Range("F11") '1.2
ar(c.F15) = sht.Range("F15") '0.8
ar(c.F3) = sht.Range("F3") '5
ar(c.F5) = sht.Range("F5") '8
ar(c.F4) = sht.Range("F4") '16


'do any validating here
For Each r In ar
If Not IsNumeric(r) Then Exit Function
Next

validate = 1
eh: Err.Clear
End Function


Sub CalcSheet(sht As Worksheet)
If Not validate(sht) Then Exit Sub

Dim v As Double
v = StartingValue - ar(c.F8)


addval 1, CInt(ar(c.F3)), CDbl(ar(c.F8)), v, sht
addval CInt(ar(c.F3)) + 1, CInt(ar(c.F5)), CDbl(ar(c.F11)), v, sht
addval CInt(ar(c.F5)) + 1, CInt(ar(c.F4)), CDbl(ar(c.F15)), v, sht
End Sub

Private Sub addval(istart As Integer, iend As Integer, _
incr As Double, ival, sht As Worksheet)
For i = istart To iend
ival = ival + incr
sht.Cells(i + StartAtRow - 1, 3) = ival
Next
End Sub