Showing billions in a value

  • Thread starter Thread starter Lo
  • Start date Start date
L

Lo

How do you prevent Visual Basic Editor from blowing up
when you're summing numbers and the numbers are in the
billions?

For example, I can type 200 Billion on a worksheet, but
when I do a simple calculation (summing cells) in VBE, and
it exceeds 20 Billion, it gives an "overflow" error
message. For example, it gives a "Run time error 9 -
subscript out of range' message.

Is there a way to show billions in Excel without it
blowing up? I'm a very new VBE/Excel user, so can someone
help walk me through how to do this...

Thanks.
Lo
 
Hi
how have you defined your variables in your macro? Try defining them as
double. e.g.
Dim ret_val as Double
 
I had no problem with the following:

I entered 30,000,000,001 in each cell in the range A1:A10 (the ending 1 was
to make sure there was no significant digits issue. Then I ran:

Sub Test()
Dim dResult As Double
dResult = WorksheetFunction.Sum(Range("A1:A10"))
MsgBox Format(dResult, "#,000")
End Sub

No problem; message box showed 300,000,000,010.
 
How do you prevent Visual Basic Editor from blowing up
when you're summing numbers and the numbers are in the
billions?

For example, I can type 200 Billion on a worksheet, but
when I do a simple calculation (summing cells) in VBE, and
it exceeds 20 Billion, it gives an "overflow" error
message. For example, it gives a "Run time error 9 -
subscript out of range' message.

I've also tried defining the variables in your macro as
double (Dim ret_val as Double), that doesn't work.

Any additional thoughts?
 
As suggested already, post the code you are using, plus the ranges and values of
the cells you are querying.
 
The feedback got me on the right track for my
previous "billions" questions - for those who helped, I
used "Variant" and got the desired result.

Now I have another issue - data pulling from a DB2
database and when the worksheet is opened, it refreshes
data. This takes almost 8 minutes.

Any thoughts on speeding this up?
 
Hi
you may start a new thread for this question :-)
please provide some data for this question_
- how many records
- is the data source on a network drive

Probably not much you can do about this refresh time though
 
Hi
probably not much you can do about this time lag (could be anything
starting with your network performance, the ODBC driver for your DB2
database, etc)
 
Query syntax and the existence of an execution plan are a couple of
things that spring to mind.

Eight minutes to fetch 6000 rows sounds excessive. I can fetch 6000
rows from a Jet database on a network - the nearest equivalent I have
- in a fraction of a second (that faction not being 480 over one <g>).

--
 
Back
Top