Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function cannot obtain cell values
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need hyperlink function to obtain range name from a cell (contents | Excel Worksheet Functions | |||
Using vlookup - how can I obtain a sum of values, not just first v | Excel Worksheet Functions | |||
Can I obtain x values from y values on an Excel chart? | Charts and Charting in Excel | |||
How do I obtain the address of a cell using the vlookup function? | Excel Worksheet Functions | |||
Would like to Obtain a Cell Address from a vlookup function | Excel Programming |