Defined Name assigned to cell, bulletproof way to read cell?

Hello!

I have an 2010 xlsm file that my VBA script must read ("harvest" as I like to say) the values of cells on various worksheets. Rows and columns can get moved around, so we used Defined Names to identify crucial cells for a safe and easy way to grab their value from VBA. My script is fairly large, and it's working well. I just ran into an issue (happily, before the VBA went into production!).

I am Mr. Error Checker. I'm a very defensive programmer, and this has served me well in ExcelLand. I thought I had some good code for reading the values of cells in a way to avoid runtime errors if the cell has any variety of errors, e.g., #N/A, #VALUE!, etc. But I'm getting a runtime error 1004 with this code when I ran into a mistake with a Defined Name. I thought my code would gracefully handle the error!

Here's the problem. I expected a particular cell's Defined Name to be, "ProjDet_AppNam" and that's what's in my VBA code. Turns out that in the spreadsheet, the actual Defined Name of the cell I was looking for was "ProjDet_AppNamCol". So I thought my defensive code would handle that gracefully, but it failed with a runtime 1004 error. (Naturally, I will correct the name of the Defined Name, but I have other similar code in my VBA who's robustness is now suspect.)

Where did I go wrong?

Note: My method is to first check that there are no errors in the cell--that it can be read without error--then I check if the cell is blank, null or empty.

Please Login or Register  to view this content.

I had never seen this type of "Evaluate-ISREF" code before. Works like a champ. Once I know the Named Cell exists using this code, I am then proceeding with my other error-checking. Thank you very much Jerry!