Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make only active sheet Volatile? Or other solutions?
Hi,
I had to have a lot of arrays on several sheets and they all had to have volatile OFFSET and INDIRECT functions. Since arrays always have to calculate each cell etc, there will occure some calculating time - Is it possible with VBA to make the cells in the active sheet volatile and the rest sheets non-volatile? Then every time you have ws_activate event, you could calculate that specific sheet instead. Othervice, the user will not see the SUM results from changing one cell and become unsure. - Another way could be to put the volatile formulas in ONE cell and referre to that cell ... , but this is array formulas of 1 x 500 cells ( x10 columns on the sheet). How could that be made? (This Q might be passed to .excel.functions newsgroup, I guess but I'll give it a try here now.) Maybe Happy to suggestions /Tskogstrom If interested: Background to Offset, indirect and arrays: The reason I need OFFSET and INDIRECT and arrays is because I have an input sheet were users had to be able to do cut, paste and drag- and- drop. This would ruin the reference links, but now I have arrays starting one cell above and end one cell under the unprotected area. They can even insert and delete rows and the array is intact. I have tested other solutions, like have code to restore cell references and copy-paste formulas etc, but that has huge drawbacks and I decided to use this instead. Now I use less VBA and more cell formulas and made it generally a lot faster, except this thing ... |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make DATE non-volatile? | Excel Worksheet Functions | |||
Copy sheet and make new sheet active | Excel Discussion (Misc queries) | |||
Code to make a sheet active | Excel Programming | |||
How to make chart embedded in active sheet? | Excel Programming | |||
How to make a sheet the active sheet? | Excel Programming |