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.

I take it this means all worksheet functions are not
necessarily available in VBA macros?

That's correct. ISBLANK is one not available.

Maybe using Len() as a test will work. It should
return 0 for a blank cell.

HTH,
Merjet

This works to some degree.

Sub Empt()
If IsEmpty(Range("B4")) = True Then
MsgBox "Empty"
Else
MsgBox "Not Empty"
End If
End Sub

"Jeff Johnson" wrote:

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.

Hi Jeff,

I'm assuming you are the Jeff Johnson I know from over in the compiled VB
newsgroups, right? What are you doing here... did you get lost?<g

The ISBLANK function only returns TRUE for a fully empty cell (it returns
FALSE if nothing is displayed as a result of a formula). If that is the
functionality you want in your VBA code, I think this function below will
work for you (just pass in a Range reference for the cell you are
testing)...

Function IsCellBlank(MyCell As Range) As Boolean
IsCellBlank = (MyCell.Value = "") And (MyCell.Formula = "")
End Function

Notice, it is a one-liner.<bg

Rick


"Jeff Johnson" wrote in message
news:_Nednc0WcKVC3ZPVnZ2dnUVZ_h2pnZ2d@datapex...
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.

"Rick Rothstein (MVP - VB)" wrote in
message ...

I'm assuming you are the Jeff Johnson I know from over in the compiled VB
newsgroups, right? What are you doing here... did you get lost?<g

I'm stunned at how active this group is! I no longer feel bad for directing
people here; I used to think this place was kind of dead.

The ISBLANK function only returns TRUE for a fully empty cell (it returns
FALSE if nothing is displayed as a result of a formula). If that is the
functionality you want in your VBA code, I think this function below will
work for you (just pass in a Range reference for the cell you are
testing)...

Function IsCellBlank(MyCell As Range) As Boolean
IsCellBlank = (MyCell.Value = "") And (MyCell.Formula = "")
End Function

Notice, it is a one-liner.<bg

As expected!

The point is that the cell really is empty, but on workbook load, Excel
doesn't think so. As stated, if I simply double-click on it (or press F2)
and then hit Enter without doing anything else, Excel will "realize" that
the cell is empty. In other words, if A1 is one of these cells and I put
"=ISBLANK(A1)" into B1, B1 will display FALSE at first, but if I do the
"null edit" thing I just mentioned B1 will now show TRUE.

The Len() function, as mentioned by another poster, won't help, because as
far as VBA code is concerned, the Value of the cell is "". But ISBLANK()
definitely "knows" the cell isn't blank, or more specifically, it knows that
the COUNTA() function won't consider it blank. But since I can't use it in
code, I'm kind of stumped....

See inline...

I'm assuming you are the Jeff Johnson I know from over in the compiled VB
newsgroups, right? What are you doing here... did you get lost?<g

I'm stunned at how active this group is! I no longer feel bad for
directing people here; I used to think this place was kind of dead.

Not just this newsgroup. I regularly frequent m.p.e.programming and
m.p.e.worksheet.functions and they are equally active.

The point is that the cell really is empty, but on workbook load, Excel
doesn't think so. As stated, if I simply double-click on it (or press F2)
and then hit Enter without doing anything else, Excel will "realize" that
the cell is empty. In other words, if A1 is one of these cells and I put
"=ISBLANK(A1)" into B1, B1 will display FALSE at first, but if I do the
"null edit" thing I just mentioned B1 will now show TRUE.

The problem you are describing (COUNTA seeing a blank cell as not blank) is
not one I have seen before. Perhaps one of the regulars here has some
insight into your problem.

Rick

I'm stunned at how active this group is! I no longer feel bad for
directing people here; I used to think this place was kind of dead.

Not just this newsgroup. I regularly frequent m.p.e.programming and
m.p.e.worksheet.functions and they are equally active.

DUH! We are in the m.p.e.programming newsgroup, aren't we. In that case, the
m.p.e.misc newsgroup (the one I thought we were in) is also quite active.

By the way, does the IsCellBlank function I posted see your blank cells as
blank in the same way ISBLANK is supposed to?

Rick

"Jeff Johnson" wrote in message
news:p4CdnYaju_cbWpLVnZ2dnUVZ_uGdnZ2d@datapex...
"Rick Rothstein (MVP - VB)" wrote in
message ...

I'm assuming you are the Jeff Johnson I know from over in the compiled VB
newsgroups, right? What are you doing here... did you get lost?<g

I'm stunned at how active this group is! I no longer feel bad for
directing people here; I used to think this place was kind of dead.

Yeah, we like it here.

The ISBLANK function only returns TRUE for a fully empty cell (it returns
FALSE if nothing is displayed as a result of a formula). If that is the
functionality you want in your VBA code, I think this function below will
work for you (just pass in a Range reference for the cell you are
testing)...

Function IsCellBlank(MyCell As Range) As Boolean
IsCellBlank = (MyCell.Value = "") And (MyCell.Formula = "")
End Function

Notice, it is a one-liner.<bg

As expected!

The point is that the cell really is empty, but on workbook load, Excel
doesn't think so. As stated, if I simply double-click on it (or press F2)
and then hit Enter without doing anything else, Excel will "realize" that
the cell is empty. In other words, if A1 is one of these cells and I put
"=ISBLANK(A1)" into B1, B1 will display FALSE at first, but if I do the
"null edit" thing I just mentioned B1 will now show TRUE.

The Len() function, as mentioned by another poster, won't help, because as
far as VBA code is concerned, the Value of the cell is "". But ISBLANK()
definitely "knows" the cell isn't blank, or more specifically, it knows
that the COUNTA() function won't consider it blank. But since I can't use
it in code, I'm kind of stumped....

Does it happen every time you open a workbook, or only the first time? It
sounds to me like what happens with imported data, where a "blank" may come
in as "", but upon "null editing" the cell, becomes an official blank cell.

I notice if I put ="" into a cell, ISBLANK() tells me the cell is not blank.
After I do this in the Immediate Window with that cell active:

ActiveCell.Value = ActiveCell.Value

then ISBLANK() tells me that the cell is blank. You might run this kind of
command on the appropriate region of the worksheet when it is first opened.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"Jon Peltier" wrote in message
...

Does it happen every time you open a workbook, or only the first time? It
sounds to me like what happens with imported data, where a "blank" may
come in as "", but upon "null editing" the cell, becomes an official blank
cell.

Yes, I'm almost positive the data was imported or perhaps pasted from the
result of an Access query.

I notice if I put ="" into a cell, ISBLANK() tells me the cell is not
blank. After I do this in the Immediate Window with that cell active:

ActiveCell.Value = ActiveCell.Value

then ISBLANK() tells me that the cell is blank. You might run this kind of
command on the appropriate region of the worksheet when it is first
opened.

Yeah, I know how to do that. The whole issue is not one of FIXING this
problem, but of finding an accurate way to DETECT it. It really just boils
down to Excel being a bit...eccentric, shall we say?

Not sure if this string is still open, but I would like to see if I can
get this question answered. From what I'm reading this is what I'm
looking for to test blank cells.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFileName As String

If Range("C3") = "" Then
MsgBox "Date Missing. File is not Saved. Click OK to return to worksheet
to update."
Range("C3").Select
Exit Sub
ElseIf Range("C4") = "" Then
MsgBox "Job No. Missing. You must enter information in this field before
the file will be saved. Click OK to return to worksheet to update."
Range("C4").Select
Exit Sub
(there's more)
If the user presses the space bar Excel thinks it has something in the
field, but I want someone to enter words or a date in the field.

Can you explain where you put your code in VBA. I'm new to this and
never worked with functions. Also, I wasn't sure if your function worked
with selecting specific cells.

"Function IsCellBlank(MyCell As Range) As Boolean
IsCellBlank = (MyCell.Value = "") And (MyCell.Formula = "")
End Function"

Thanks.




*** Sent via Developersdex http://www.developersdex.com ***

One thing you can do is to use the Trim() function like the following line:

If VBA.Trim(Range("C3").Text) = "" Then

That way, the result of the above would remove any leading/trailing spaces.
This also works for when someone just put in spaces as it will remove the
spaces and return an empty string. The only spaces it doesn't remove will
be any spaces between any other characters within the value.

As a new person to programming, one of the first things you want to learn is
to avoid anything that is implicit (that implies and makes assumptions when
in fact those assumptions could be false) by nature. Example: The above
line of code is still implying the active worksheet as the worksheet you
want to have the Text property of Cell C3 checked. To avoid such an
implicit code, you may want to explicitly prequalify that Range Object with
a Worksheet Object in front of it and a Workbook Object in front of the
Worksheet Object like the following:

Workbooks("Book1.xls").Worksheets("Sheet1").Range( "C3").Text

By having it like that, it removes any assumptions that could be false.

Also, you will want to avoid using either the Select and/or Activate methods
as much as you reasonably can. There may be exceptions to using them in
cases that you don't have much of a choice, but those cases should be rare
in nature. I do have one such case, but it's as a result of the vendor of
our third party program (an add-in program we use within Excel for certain
functionalities) does not address this issue too well. I have contacted
them both by phone and by email including in response to an email with them
asking us as the customer if any improvements that could be done. In that
response in particular, I typed up a very detail response that got into what
are the issues it causes, which when I'm working on other stuff while my
reports are running within Excel, any time the code comes to the line of
"Workbooks(<WorkbookSpecName).Activate" (Note, the line inside the double
quotes, but not including the double quotes.), it deactivates the program
that I'm physically working in and activates Excel. If it activates the
spreadsheet side, not so bad as nothing too bad happens other than it's
rather irritating to be removed from the program that I was physically
working in. On the other hand, if it activates the VBA side, what few
characters I had typed past the point of time when the activation took place
may go into the VBA at it's cursor point, which then that can very well
being on a whole host of issues. If you are a 2 finger typer (hunt and peck
as some call it), then maybe that's not as much of an issue, but then it
could still be an issue as you are looking at the keyboard rather than at
the screen. On the other hand, if you are a typer like me that can type at
least 50 wpm (some may even get up to 90 to 100 wpm. I am around 50 to 60
wpm), then you can see how quickly that can also cause an issue. In that
email, I not only stated the problem, but I also gave them lines of how to
fix the issue and what sort of documentations would need to be put in. Of
course, the technical reps there who I have had contact from time to time
have acknowledged that my knowledge has exceeded their level of knowledge in
the program as many of them don't even remotely get into learning about the
events within the program where as I have it down to the point I not only
know how to use the events, but also know what order they fall in and what
are the various issues they have to fix with those different events, which I
have had to put in work arounds to address those different issues.

A couple more areas that you may want to think about doing.

One, for those objects you plan on calling on multiple times within the
code, you may want to assign them to object variables, so as to have the
code run more efficiently. However, also be sure to set those object
variables to the keyword of "NOTHING" when those object variables are no
longer of use anymore.

Also be sure to watch the naming of your various variables which the scheme
I use is the following:

<First letter of scope level & "_" & <3 letters to indicate type of
variable & <Readable variable name so as it's more or less self
documentation and easier to debug should that need to take place

Example:

Dim l_rngOrderDate As Range, l_dteOrderDate As Date

The reason for me to use this scheme, it allows for me to see what scope
it's at, This also helps the code itself as it eliminate a lot of confusion
that can happen otherwise. Not only can you see the scope, but you can also
see the data type instantly too. Also, as shown above, be sure to
explicitly declare all variables so as the code doesn't have a chance to
apply the incorrect data type to the variable name, and not only that, but
if you set the option to be explicit of all variables, it will allow for
proper compilation checks (a lot of errors can be caught via compilations
though you will still have run-time and logical type bugs to work out beyond
compilation test. Of course, you could assign variables either to Variant
or Object, but I don't like to use either one of these 2 for the same basic
reason along with the fact, it doesn't allow for the use of intellisense
while coding. As a matter of fact, if you get into VB.NET or any of the
languages in .NET programming, the Variant data type is no longer allowed.

I could go on, but figure this will be a good start for you as far as
learning VBA is concerned.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Jim McCaffrey" wrote in message
...
Not sure if this string is still open, but I would like to see if I can
get this question answered. From what I'm reading this is what I'm
looking for to test blank cells.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFileName As String

If Range("C3") = "" Then
MsgBox "Date Missing. File is not Saved. Click OK to return to worksheet
to update."
Range("C3").Select
Exit Sub
ElseIf Range("C4") = "" Then
MsgBox "Job No. Missing. You must enter information in this field before
the file will be saved. Click OK to return to worksheet to update."
Range("C4").Select
Exit Sub
(there's more)
If the user presses the space bar Excel thinks it has something in the
field, but I want someone to enter words or a date in the field.

Can you explain where you put your code in VBA. I'm new to this and
never worked with functions. Also, I wasn't sure if your function worked
with selecting specific cells.

"Function IsCellBlank(MyCell As Range) As Boolean
IsCellBlank = (MyCell.Value = "") And (MyCell.Formula = "")
End Function"

Thanks.




*** Sent via Developersdex http://www.developersdex.com ***