Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 22-10-2009
Member
 
Join Date: Dec 2008
Posts: 36
Excel VBA Overflow Error

Hello,

I am facing a problem With Excel , Here is Original problem on the basis of a simplified example my file contains two columns:
- Column A contains three amounts total figure in line 5. This column contains the values for the unity that we do not want to show to the reader / user;
- Column B contains the same amounts have been rounded to the nearest thousand. The contents of this column is visible .

However, the total point for the 2nd column is 4 then it should be 3 if we refer to the contents of cells B2 to B4. ,also sometime i get vba overflow Error , can you tell me how can I solve this problem
Reply With Quote
  #2  
Old 22-10-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: Excel VBA Overflow Error

To solve the problem described above, I wrote the following function for use as a formula in your Excel sheet (ie in cell B5 of example):


Code:
Function getRoundedSum (rngFigures As Range, strRndType As String) As Integer Function getRoundedSum (rngFigures As Range, strRndType As String) As Integer 

Dim i As Integer 
Sun lngTotal As Long 
Sun strSubPgm, strFormat As String 
Sun varFiguresArray () As Variant 

On Error GoTo ErrorHandler 

strSubPgm = 'Function' getRoundedSum () " 
getRoundedSum = 0 
lngTotal = 0 

varFiguresArray () = rngFigures.Value 
For i = 1 To UBound (varFiguresArray) 
Select Case UCase (strRndType) 
Case "T" 
strFormat "#,## = 0 " 
Case "M" 
strFormat "#,## = 0,, " 
Case Else 
Exit Function 
End Select 
lngTotal = lngTotal + Format (CLng (varFiguresArray (i, 1)), strFormat) 
Next i 
getRoundedSum = lngTotal 
ErrorHandler: 
Call handleError (Err.Number, Err.Description, strSubPgm) 
End Function 



Dim i As Integer 
Sun lngTotal As Long 
Sun strSubPgm, strFormat As String 
Sun varFiguresArray () As Variant 

On Error GoTo ErrorHandler 

strSubPgm = 'Function' getRoundedSum () " 
getRoundedSum = 0 
lngTotal = 0 

varFiguresArray () = rngFigures.Value 
For i = 1 To UBound (varFiguresArray) 
Select Case UCase (strRndType) 
Case "T" 
strFormat "#,## = 0 " 
Case "M" 
strFormat "#,## = 0,, " 
Case Else 
Exit Function 
End Select 
lngTotal = lngTotal + Format (CLng (varFiguresArray (i, 1)), strFormat) 
Next i 
getRoundedSum = lngTotal 
ErrorHandler: 
Call handleError (Err.Number, Err.Description, strSubPgm) 
End Function
Reply With Quote
  #3  
Old 22-10-2009
Member
 
Join Date: Dec 2008
Posts: 36
Re: Excel VBA Overflow Error

This works perfectly as long as the final total return in the Excel sheet is less than 32,768, which (is it a coincidence ...?) corresponds to 2 Power 15 (2 ^ 15). Where appropriate (ie total being greater than or equal to 32,768), the error 6 (Overflow) is generated. Does anyone have an explanation and a track that I can follow to resolve this problem of "Overflow"? In advance thank you for your comments and help!
Reply With Quote
  #4  
Old 22-10-2009
Member
 
Join Date: Nov 2005
Posts: 1,323
Re: Excel VBA Overflow Error

it is not an accident and that's normal. you are declaring your function as "whole":

Function getRoundedSum (rngFigures As Range, strRndType As String) As Integer

storage space for your getRoundedSum variable is limited to 2 bytes (= 16 bit) you can not exceed ? (2 ^ 15) -1 values is between -32 768 and 32 767. declaring your function like this Long reserving it is ? 4 bytes (2 ^ 31) -1 possibilities (from -2 147 483 648 2 147 483 647), you should be able to leave it there
Reply With Quote
  #5  
Old 22-10-2009
Member
 
Join Date: Dec 2008
Posts: 36
Re: Excel VBA Overflow Error

Thank you for the super-fast response and clear! After the change of type Long, actually it works perfectly. I am somewhat ashamed for not having detected the source of my problem on my own. Like what: when analyzing something too long, we no longer noticed the most obvious elements. Many thanks again!

Best regards.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Excel VBA Overflow Error"
Thread Thread Starter Forum Replies Last Post
IE STACK OVERFLOW error occurring in windows 7- Toshiba L505 Alfiee Technology & Internet 5 06-05-2011 10:13 AM
Windows XP stack overflow error Sadeepan Operating Systems 3 31-07-2009 07:49 AM
Internet explorer stack overflow line 0 error Macfly Technology & Internet 2 25-05-2009 09:59 PM
BOOTCD memory overflow error mam1955 Operating Systems 7 30-08-2008 05:15 PM
CDBOOT: MEMORY OVERFLOW ERROR BUKSIS Windows XP Support 1 04-08-2005 05:03 AM


All times are GMT +5.5. The time now is 09:31 PM.