Solved: IsBlank? Missing Library?

[jwise]

I've been using a macro since April with a statement like:

If a_Value = IsBlank Then

Today this fails; I get an error on the "IsBlank". When I looked this up (via Google), it seems the consensus was that "IsBlank" was for formulas and "IsEmpty" was for VBA. The problem is ... this maco has worked since April. "IsEmpty" fails also.

I am assuming I could just replace "IsBlank" with "" (Quotes with nothing between them). I also assume that there is some library (via Tools => References) that will allow "IsBlank" to work again. I admit that I probably "cleaned" the code (with the common ASAP tool) at some time. The real problem is that there is a myriad of these libraries, and I have no clue how to find "IsBlank" in them. How do I find the missing library?

TIA

[Norie]

What error are you actually getting?

I've never heard of an IsBlank function/constant/whatever in standard VBA.

There is the ISBLANK worksheet function but that's not available in VBA, even via Application.WorksheetFunction.

And that's probably because there's a VBA equivalent.

[jwise]

Thanks for the response.

"Compile error. Variable not defined". "IsBlank" is highlighted.

[Bob Phillips]

So it is telling you that you are trying to use a variable called IsBlank, and it isn't declared.

Try



If a_value = "" Then

[jqpublic13]

What about:



If a_Value = vbNullString Then

[jwise]

I did change the code to double quotes("") and it worked, of course.

My real intention was to find out how to search the libraries and find out what is in each. It seems if VBA "knows" all these libraries exist, and which ones are in use, I should be able to find where the "IsBlank" was previously coming from.

I have seen a very nasty problem where the same include file name was in multiple libraries, and this include file was really "old" and "new" versions. The first one was picked up... naturally it should have been the second. So my question was a veiled attempt to prevent/diagnose this problem.

Restated: How do I find which library that a definition comes from? (In VBE, choose Tools | References.)

[Norie]

There is no IsBlank function in native VBA as far as I know.

Are you sure IsBlank wasn't a constant or variable?

Or perhaps part of an add-in?

[jwise]

Sorry, but I have no idea where it came from. I looked for snippets and then added the code I wrote. I must have found this. I do probably have some archived workbooks where IsBlank is still running. I save all these monthly workbooks.

[rory]

I would guess you got an Option Explicit added to your module at some point. Before that, the code just regarded IsBlank as an uninitialised variable, but with Option Explicit you would have to declare all variables used in the code, so now you get an error.

[jwise]

Problems of this nature bug me. Your explanation makes sense since I recently copied a code fragment into this workbook that does in fact use "Option Explicit". I should do this routinely!

I wrote this macro to continue retrieving rows until a particular field is "missing". I would assume the uninitialized variable has the same value as a "missing" value. I later changed the code to retrieve the rows based on the row count, but I left the "missing" value test in because the tested field is what is used to merge the data. In actuality, my data will probably never have this "missing" field (the code that builds the file does extensive error checking for things like this, and that program would have to have a very serious bug to send Excel a flawed file).

Thanks again.

[Bob Phillips]

Don't do it routinely, get Excel to do it

In the VBIDE, Tools>Options>Editor, and check the Require Variable Declaration box.

[jwise]

I have changed my options in VBE per your suggestion. This suggestion is especially good for me, since I am known to spell my own cryptic variable names incorrectly, thus generating a new variable instead of an error.