IsBlank() in VBA code?
I had the need to use the ISBLANK() worksheet function in some VBA code, so
I went into the editor, typed "WorksheetFunction.Is" and the first thing
that came up in the Intellisense list was IsError. ??? So I scrolled up and
found I had passed items starting with "is," meaing IsError was defintely
the first in the list. I take it this means all worksheet functions are not
necessarily available in VBA macros?
And for reference, in case this was some weird thing that didn't show in
Intellisense but still worked in code (like the Print method of the Printer
object in VB6) I manually typed "IsBlank" and tried to run the macro. I got
an error saying the WorksheetFunction object didn't support this method.
Ultimately, the problem I was trying to solve was one of "Excel being
Excel." I have a spreadsheet that has lots of blank cells (they don't even
contain spaces) which Excel doesn't THINK are blank cells. That is, when I
use the COUNTA() function on a range with these cells, they are counted and
not ignored. (If I simply go into edit mode in the cell and then hit Enter,
Excel wakes up and realizes the cell really is blank.) So what? Well, I have
some macros that do what they do until they encounter a blank cell. I test
for "blankness" by checking the Value of the cell against the empty string.
The problem is that these cells return the empty string for their value but
they're not really blank in Excel's eyes, so I'm looking for a better way to
detect them. If ISBLANK() were available in code then I could check that
instead.
|