Function cannot obtain cell values

[tkpmep]

I have a large spreadsheet (200 Mb) in which I create a user defined
VBA function that reads two columns of data and then computes various
results using these values. The first column (dates) is numeric; it
contains only values and has no formulas. The second column (Returns)
has complex formulas that evaluate to values. My function is coded as
follows:

Public Function Drawdown(dates As Range, Returns As Range)

d11 = dates.Cells(1).Value
d12 = dates.Cells(2).Value

r11 = Returns.Cells(1).Value
r12 = Returns.Cells(2).Value
..
..
Drawdown = results
end

the cells containing dates have just values (i.e. no fomulas), while
the cells containing Returns have valid formulas in them. But if I
single step through the macro, d11 and d12 consistently evaluate to
numbers, while r11 and r12 consistently evaluate to Empty. However, if
the macro is re-executed many times, r11 and r12 eventually evaluate
to the appropriate values. I have tried this in Excel 2002 and Excel
2007 - I get the same result. On the other hand, if I create a new
small spreadsheet with just two cells in the date range and two cells
in the Returns range with some simple formulas, it seems to work fine
- r11 and r12 assume the correct values right away. What could be the
problem here? Does the size of the spreadsheet (200 MB) somehow
prevent Excel from allowing the function to evaluate all the
spreadsheet functions in the Returns range? If so, what is the
solution?

Thanks in advance for your assistance

Thomas Philips

 

[Jim Cone]

Thomas,
I wouldn't expect a 200mb file to be very responsive.
My guess is that it takes a very long time to do the necessary calculations.
Another guess is that one of these days it isn't even going to open.
My recommendation is reduce the file size by about 75%.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message
I have a large spreadsheet (200 Mb) in which I create a user defined
VBA function that reads two columns of data and then computes various
results using these values. The first column (dates) is numeric; it
contains only values and has no formulas. The second column (Returns)
has complex formulas that evaluate to values. My function is coded as
follows:

Public Function Drawdown(dates As Range, Returns As Range)

d11 = dates.Cells(1).Value
d12 = dates.Cells(2).Value

r11 = Returns.Cells(1).Value
r12 = Returns.Cells(2).Value
..
..
Drawdown = results
end

the cells containing dates have just values (i.e. no fomulas), while
the cells containing Returns have valid formulas in them. But if I
single step through the macro, d11 and d12 consistently evaluate to
numbers, while r11 and r12 consistently evaluate to Empty. However, if
the macro is re-executed many times, r11 and r12 eventually evaluate
to the appropriate values. I have tried this in Excel 2002 and Excel
2007 - I get the same result. On the other hand, if I create a new
small spreadsheet with just two cells in the date range and two cells
in the Returns range with some simple formulas, it seems to work fine
- r11 and r12 assume the correct values right away. What could be the
problem here? Does the size of the spreadsheet (200 MB) somehow
prevent Excel from allowing the function to evaluate all the
spreadsheet functions in the Returns range? If so, what is the
solution?

Thanks in advance for your assistance

Thomas Philips