PDA

View Full Version : [SOLVED] Overflow error 6



crmpicco
05-19-2005, 06:37 AM
I keep getting an Overflow error 6 with this code and i cant see what it is:



'... loop through all rows
For iRow = 1 To iTotalRows
flag = False
'... loop through all columns
For iCol = 1 To iTotalCols
sRange = getColumnLetter(iCol) & iRow
Range(sRange).Select
'... if the any cells are bold then remove the formatting from them
Selection.Font.Bold = False
'... the current cell colour
iCurrentCellCol = Range(sRange).Interior.ColorIndex
'... if the colour of the current cell is not WHITE
If iCurrentCellCol <> gbFLUSH_COLOUR Then
For k = LBound(gaFareBasisColour) To 0
iFahrpreisPos = InStr(gaFareBasisColour(k), "/")
iArrayFareBasisColour = Mid(gaFareBasisColour(k), iFahrpreisPos + 1, Len(gaFareBasisColour(k)) - 1)
If iCurrentCellCol = iArrayFareBasisColour Then
'... while the end of the tr has not been reached
While EndFareBasis <> True
sRange = getColumnLetter(iCol + iAddOne) & iRow
picco = True
sRechtsRange = getColumnLetter(iCol + iAddOne + 1) & iRow
'... if the cell is not blank
If Trim(Range(sRange).Text) <> "" Then
the_FareBasis = the_FareBasis & "&lt;td&gt;"
the_FareBasis = the_FareBasis & fix_characters(Trim(Range(sRange).Text))
the_FareBasis = the_FareBasis & "&lt;/td&gt;"
bGefundenFareBasis = True
ElseIf Trim(Range(sRange).Text) = "" And Trim(Range(sRechtsRange).Text) = "" Then
EndFareBasis = True
End If
iAddOne = iAddOne + 1
Wend
End If
Next
For j = LBound(gaRulesColour) To UBound(gaRulesColour)
iPos = InStr(gaRulesColour(j), "/")
iArrayColour = Mid(gaRulesColour(j), iPos + 1, Len(gaRulesColour(j)) - 1)
If iCurrentCellCol = iArrayColour Then
If the_Heading <> "" And the_FareBasis <> "" Then
rulesXML = rulesXML & "<fare_rule_lines>"
rulesXML = rulesXML & "<heading>" & fix_characters(fix_ampersand(the_Heading)) & "</heading>"
rulesXML = rulesXML & "<content>" & the_FareBasis & fix_characters(Trim(the_Content)) & "</content>"
rulesXML = rulesXML & "</fare_rule_lines>"
End If
the_Heading = fix_ampersand(Trim(Range(sRange).Text))
the_Content = "&lt;table&gt;"
End If
iPos = InStr(gaContentColour(j), "/")
iArrayContentColour = Mid(gaContentColour(j), iPos + 1, Len(gaContentColour(j)) - 1)
iCurrentCellColour = Range(sRange).Interior.ColorIndex
If iCurrentCellColour <> 2 Then
'... if the current cell is the same as the colour of the content in the template
If iCurrentCellColour = iArrayContentColour Then
iFlag = 0
While flag <> True
sRightRange = getColumnLetter(iCol + iFlag + 1) & iRow
'... if it is not empty then write another <td>
If iFlag = 0 And Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "&lt;tr&gt;"
the_Content = the_Content & "&lt;td&gt;"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
the_Content = the_Content & "&lt;/td&gt;"
ElseIf iFlag = 0 And Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "&lt;tr&gt;"
the_Content = the_Content & "&lt;td&gt;"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
the_Content = the_Content & "&lt;/td&gt;"
the_Content = the_Content & "&lt;/tr&gt;"
flag = True
ElseIf iFlag <> 0 And Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "&lt;td&gt;"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
the_Content = the_Content & "&lt;/td&gt;"
the_Content = the_Content & "&lt;/tr&gt;"
flag = True
ElseIf iFlag <> 0 And Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "&lt;td&gt;"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
the_Content = the_Content & "&lt;/td&gt;"
'... if the cell is empty
End If
iFlag = iFlag + 1
'... while flag <> true
Wend
the_Content = the_Content & "&lt;/table&gt;"
'... if the cell is the same as the colour in the template
End If
End If
Next j
End If
'... loop through each column
Next iCol
If iRow = iTotalRows Then
If the_Heading <> "" And the_FareBasis <> "" Then
rulesXML = rulesXML & "<fare_rule_lines>"
rulesXML = rulesXML & "<heading>" & fix_characters(fix_ampersand(Trim(the_Heading))) & "</heading>"
rulesXML = rulesXML & "<content>" & the_FareBasis & fix_characters(Trim(the_Content)) & "</content>"
rulesXML = rulesXML & "</fare_rule_lines>"
End If
'the_Heading = fix_ampersand(Trim(Range(sRange).Text))
'the_Content = "&lt;table&gt;"
End If
'... loop through each row
Next iRow

Bob Phillips
05-19-2005, 06:39 AM
[QUOTE=crmpicco]I keep getting an Overflow error 6 with this code and i cant see what it is:[QUOTE]

Where, and what are your variable declarations?

crmpicco
05-19-2005, 06:40 AM
'... variables for error handling
Const sErrorSource As String = "create_rules_OK_UKtoCzech_Rules()"
'... in the event of an error go to the ErrorHandler module
On Error GoTo ErrorHandler
'... progress bar variable
Dim pctDone As Single
'... the variable for the xml build-up
Dim rulesXML As String
'... control variables for main loop and heading/content/fare basis loop
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim controlSheet As String
Dim iNoSheets As Integer
Dim sRange As String
Dim sRightRange As String
Dim sRechtsRange As String
Dim tempRange As String
Dim iCol As Integer
Dim iRow As Integer
Dim iPos As Integer
Dim iFahrpreisPos As Integer
Dim iFlag As Integer
Dim iAddOne As Integer
Dim iArrayColour As Integer
Dim iArrayContentColour As Integer
Dim iArrayFareBasisColour As Integer
Dim iTotalRows As Integer
Dim iTotalCols As Integer
Dim iCurrentCellCol As Integer
Dim iCurrentCellColour As Integer
Dim the_Heading As String
Dim the_Content As String
Dim the_FareBasis As String
Dim content_range As String
Dim flag As Boolean
Dim EndFareBasis As Boolean
Dim bGenfundenFareBasis As Boolean
' test variables '
Dim picco As Boolean '

Bob Phillips
05-19-2005, 06:45 AM
I presume this is Excel 2003, so I can't test as I only have XP.

Start by changing all integer types to long (integer is a waste of time anyway as VBA internally does a convert to and from if you declare integer) and try again.

crmpicco
05-19-2005, 06:50 AM
I have changed code to:


'... variables for error handling
Const sErrorSource As String = "create_rules_OK_UKtoCzech_Rules()"
'... in the event of an error go to the ErrorHandler module
On Error GoTo ErrorHandler
'... progress bar variable
Dim pctDone As Single
'... the variable for the xml build-up
Dim rulesXML As String
'... control variables for main loop and heading/content/fare basis loop
Dim i As Long
Dim j As Long
Dim k As Long
Dim controlSheet As String
Dim iNoSheets As Long
Dim sRange As String
Dim sRightRange As String
Dim sRechtsRange As String
Dim tempRange As String
Dim iCol As Long
Dim iRow As Long
Dim iPos As Long
Dim iFahrpreisPos As Long
Dim iFlag As Long
Dim iAddOne As Long
Dim iArrayColour As Long
Dim iArrayContentColour As Long
Dim iArrayFareBasisColour As Long
Dim iTotalRows As Long
Dim iTotalCols As Long
Dim iCurrentCellCol As Long
Dim iCurrentCellColour As Long
Dim the_Heading As String
Dim the_Content As String
Dim the_FareBasis As String
Dim content_range As String
Dim flag As Boolean
Dim EndFareBasis As Boolean
Dim bGefundenFareBasis As Boolean
''''''''''''''''''''''
' test variables '
Dim picco As Boolean '
''''''''''''''''''''''


same problem.....

Regouin
05-19-2005, 06:52 AM
@xld

Are you serious about the convert stuff? Does VBA just convert the integer to a long and back again only with a cap at 34k? Normally these are limited to available memory slots (very often these numbers are 2^x numbers) (256 = 2^8, etc).

Regouin
05-19-2005, 06:53 AM
use option explicit at the start, forces you to claim every variable, can be handy with so many variables floating around.

crmpicco
05-19-2005, 06:53 AM
already had that in:




Option Explicit

Regouin
05-19-2005, 06:56 AM
I dont have the time right now to thoroughly examine the code, but check al your for...next loops if they are infinite (never stop). and try to run the code step by step (F8) and tell us which line it fails you.

johnske
05-19-2005, 07:07 AM
@xld

Are you serious about the convert stuff? Does VBA just convert the integer to a long and back again only with a cap at 34k? Normally these are limited to available memory slots (very often these numbers are 2^x numbers) (256 = 2^8, etc).

Hi Guys,

xld's right, no sense in using integer any more, it's converted to long - check here (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/decontheintegerdatatypes.asp) . (This issue was discussed in a poll here a month or two back)

Regards,
John

crmpicco
05-19-2005, 07:20 AM
this is not solved - sorry

Regouin
05-19-2005, 07:21 AM
what line does it stop on when you 'step' (F8) through the project?

Bob Phillips
05-19-2005, 07:30 AM
Does VBA just convert the integer to a long and back again only with a cap at 34k?

The advent of 32-bit operating systems means that the arithmetic works fastest with 32-bit integers (longs). If you use Integers (16-bit), the compiler converts them to 32-bit, does the arithmetic, then converts the result back to 16-bit. Using longs to begin with eliminates these conversion steps.

I don't know to hand a documented source for this, but I have read it so many times, from authoritative sources, and testing backs it up, then I am confident it is correct.

johnske
05-19-2005, 07:48 AM
This statement has the potential for an infinite loop. Flag appears not to be set to True for the first and last If statement conditions


iFlag = 0
While flag <> True
sRightRange = getColumnLetter(iCol + iFlag + 1) & iRow
'... if it is not empty then write another <td>
If iFlag = 0 And Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "&lt;tr&gt;"
the_Content = the_Content & "&lt;td&gt;"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
the_Content = the_Content & "&lt;/td&gt;"
ElseIf iFlag = 0 And Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "&lt;tr&gt;"
the_Content = the_Content & "&lt;td&gt;"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
the_Content = the_Content & "&lt;/td&gt;"
the_Content = the_Content & "&lt;/tr&gt;"
flag = True
ElseIf iFlag <> 0 And Trim(Range(sRightRange).Text) = "" Then
the_Content = the_Content & "&lt;td&gt;"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
the_Content = the_Content & "&lt;/td&gt;"
the_Content = the_Content & "&lt;/tr&gt;"
flag = True
ElseIf iFlag <> 0 And Trim(Range(sRightRange).Text) <> "" Then
the_Content = the_Content & "&lt;td&gt;"
the_Content = the_Content & fix_ampersand_to_amp(Trim(Range(getColumnLetter(iCol + iFlag) & iRow).Text))
the_Content = the_Content & "&lt;/td&gt;"
'... if the cell is empty
End If
iFlag = iFlag + 1
'... while flag <> true
Wend

crmpicco
05-20-2005, 01:56 AM
turns out it was an error in my ASP!!:




if left(rulexml.selectnodes("rules").item(0).selectnodes("fare_rule_lines").item(a).selectnodes("content").item(0).text,15) <> "<table><tr><td>" then
if airline_code = "OK" then
response.write "<table border=1 bordercolor=000000><tr><td>"
else
response.write "<table><tr><td>" end if

Richie(UK)
05-20-2005, 05:12 AM
The advent of 32-bit operating systems means that the arithmetic works fastest with 32-bit integers (longs). If you use Integers (16-bit), the compiler converts them to 32-bit, does the arithmetic, then converts the result back to 16-bit. Using longs to begin with eliminates these conversion steps.

I don't know to hand a documented source for this, but I have read it so many times, from authoritative sources, and testing backs it up, then I am confident it is correct.
I'd agree with xld on this one. Again, read it often but can't think of any references ... except this one to Dermot Balson's (http://www.webace.com.au/%7Ebalson/InsaneExcel/Other.htm) site. Check out the "Speed of different variable types" download.

Obviously this only gives an indication based on the test data used - each situation will vary according to its particular circumstances. But as a general guide Long data types will be quickest.