Jeremy Stein - Brain

« »

Excel VBA function displays “#value!” on open, but works when recalculated

I had an Excel UDF that I was using with Excel 2010 in a xlsm file. The function worked fine, but after I saved and re-opened the file, the values would switch from their calculated value to the “#VALUE!” error message. If I hit F2 on any of the cells and hit enter to recalculate, the correct value for all the cells would be displayed.

It turned out that this was because the UDF had a range parameter that covered cells which used calculations from external data. When the external data was refreshed (as I had configured it to do on Open), the UDF iterated over the cells in the range and accessed their Text property. When I changed from Text to Value, it worked.

February 5, 2013 No Comments.

No Comments

Be the first to comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Why ask?

« »