VBA Function to test if a cell is blank 2

[BajanPOET]

Is there any VBA function to test if a cell is blank?

What? Who? ME????

 

[PHV]

You may try something like this:
If Trim(yourCell.Value & "") = "" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mintjulep]

Why reinvent the wheel?

Just use the existing ISBLANK() function.

 

[BajanPOET]

I tried using the Isblank() but I couldn't get it to work. It was telling me the equivalent of 'that function doesn't exist.'

The Trim() works, but how would I use the Isblank()?

What? Who? ME????

 

[SkipVought]

What do you mean by BLANK?

VALUE is ""?

What about if there's a FORMULA in the cell? It may APPEAR to be blank, but it's not.
[tt]
=IF(A1="",TRUE,FALSE)
=IF(ISBLANK(A1),TRUE,FALSE)
[/tt]
can give different results.

Skip,
[sub]
[red][/red]
[/sub]

 

[BajanPOET]

I was writing code for my Workbook_Open stub.

IsBlank() wasn't supported... I wanted to test for E3=""... the Trim() worked fine - like this:

Range("E3").Select
If Trim(ActiveCell.Value) = "" Then
   VBAProject.ThisWorkbook.Worksheets("LPO").Range("E3").Value = Date
End if

What? Who? ME????

 

[SkipVought]

Select is not necessary....

'Range("E3").Select  -- WHAT WORKBOOK & SHEET IS THIS CELL ON???
If Trim(WHATWORKBOOK.WHATSHEET.Range("E3").Value) = "" Then
   VBAProject.ThisWorkbook.Worksheets("LPO").Range("E3").Value = Date
End if

Skip,
[sub]
[red][/red]
[/sub]

 

[BajanPOET]

ok


What? Who? ME????

 

[mintjulep]

Any excel function can be used in excel VBA by using the worksheetfunction property.

answer = Application.WorksheetFunction.IsBlank(myCell)

 

[BajanPOET]

Thanks mintjulep... that's what I wanted... I'll try it.


What? Who? ME????

 

[BajanPOET]

I tried that, but actually I get the message that "Object doesn't support this property or method." when I try to use that code snippet.

What? Who? ME????

 

[CBasicAsslember]

Some worksheet functions are not usable in VBA. Use 'IsEmpty' in VBA.

 

[mintjulep]

Well I'll be....

 

[SkipVought]

CA,

Sorry, but the IsEmpty function in VB returns whether a variable has been initialized or not.

Skip,
[sub]
[red][/red]
[/sub]

 

[CBasicAsslember]

Skip,
The original question was:

Is there any VBA function to test if a cell is blank?

The IsEmpty function, when used on a cell, will return False if there is either a formula or data in the cell.

 

[AnotherHiggins]

CBA,
Try it on an empty cell vs a cell with something in it: it always returns false.

BajanPOET, I'd go with the earlier-suggested

If Trim(Range("E3").value) = "" Then
    '...
End If

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[BajanPOET]

I will go back to my Range.Select... that worked...

I can't find IsEmpty as a function in this implementation of VBA, either :-(

Actually, regarding this code snippet (in order to remove the unnecessary Range.Select)

If Trim(WHATWORKBOOK.WHATSHEET.Range("E3").Value) = "" Then
   VBAProject.ThisWorkbook.Worksheets("LPO").Range("E3").Value = Date
End if

What would be the Workbook and the Worksheet I would need? I named that particular sheet "LPO" and the name of the *.xlt file is PurchaseOrders.xlt. So would it actually be

If Trim(PurchaseOrders.LPO.Range("E3").Value) = "" Then

What? Who? ME????

 

[CBasicAsslember]

anotherhiggins,

I did and it works.

Sub aTest()
If IsEmpty(Sheet1.Range("A1")) = True Then MsgBox "A1 is empty"
End Sub

 

[PHV]

With ThisWorkbook.Worksheets("LPO")
If Trim(.Range("E3").Value) = "" Then .Range("E3").Value = Date
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

Thanks PHV, that worked...

What? Who? ME????