Cell Formula problem in VBA (2003)

Topic

New Reply

Hi All,

I need to grab the formulas of some cells as strings to work with them. Generally works fine, just sFormula = oCell.formula. However, it bombs on formulas very near the formula character limit (of which, unfortunately, our users have a good amount).

I’ve played with the formulas in question. The number of chars I need to remove in order to work with the formula in VBA is a bit variable, depending on the formula, usually I have to bring the length down to between 1007 and 1010 chars.

I can work around this by switching to display formulas mode and grabbing the text of the cell, it seems, but I was wondering why/when this happens and if there’s any other way to work with these formulas.

Thanks a lot.

Reply | Quote

Replies

I’d shorten formulas by using extra cells to hold intermediary results. A 1000-character formula is very difficult to understand and to maintain, IMHO.

(And no, I don’t know a workaround)

Reply | Quote

Thanks Hans. Unfortunately they are user’s files, and stubborn users to boot, so they probably won’t break up their formulas. I’ll just use that workaround.
Best,

Reply | Quote