Condition for Empty String in VBA

Question

how do I check if the string variable is empty in vba?

if:

Dim StrFile1 As String, StrFile2 As String
Dim Text3 As String
Dim Len1 as Integer, Len2 As Integer 

  With NewMail
   Text3 = Cells(i, 3).Value
   StrPath = Cells(i, 2).Value & Text3
   Text = Cells(i, 1).Value

  .Subject = 
  ' adds the data in column3 with space as subject
  .From = 
  .To = Text
  .BCC = ""
  .TextBody = 

StrFile1 = Dir(StrPath & "*.txt")
   Len1 = Len(StrFile1)
   Do While Len(StrFile1) > 0
   .AddAttachment StrPath & StrFile1
   StrFile1 = Dir
   Loop

   StrFile2 = Dir(StrPath & "*.pdf")
   Len2 = Len(StrFile2)
   Do While Len(StrFile2) > 0
   .AddAttachment StrPath & StrFile2
   StrFile2 = Dir
   Loop

   If (Len1 & Len2) = 0 Then
   GoTo Last



  '.AddAttachment Text3
  .Send
End With
i = i + 1
Loop

Last:
End With
i = i + 1
Loop

Now i want to check simultaneously if Len1 and Len2 are 0, if so then I want to go to Last.

When I use this code I get a message/Compile error "Want to end with without with" and i am not sure if

If (Len1 & Len2) = 0 Then
       GoTo Last

this is a proper code. and Do i need to declare the label Last??

Note: `IsEmpty()` is a VBA function, but won't work for empty strings I don't think. (Try it, `Debug.Print IsEmpty(StrFile1)` returns `FALSE`) — BruceWayne, Jun 06 '16 at 17:22
Possible duplicate of [EXCEL VBA Check if entry is empty or not 'space'](http://stackoverflow.com/questions/14108948/excel-vba-check-if-entry-is-empty-or-not-space) — Mathieu Guindon, Jun 06 '16 at 17:38
Wait, I think this may be an [XY Problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). **Why** are you checking for an empty string? Are you trying to see if there's a PDF or TXT file? Is that what the string check is for? — BruceWayne, Jun 06 '16 at 17:53
Yes...if the folder does not have either the .txt or .pdf files I want to end the current loop and go on to the next loop.(in this case a folder) — Shank, Jun 06 '16 at 17:57

Abdellah OUMGHAR · Accepted Answer · 2016-06-06T18:07:58.553

8

You have many way to do that like below :

Dim StrFiles As String
StrFiles = Trim(StrFile1 & StrFile2)

If IsEmpty(StrFiles) Then
If StrFiles = vbNullString Then
If StrFiles = "" Then
If StrFiles = Empty Then
If Len(StrFiles) = 0 Then

you can use + operator to check 2 strings are empty reference to your code, because Len Function returns an integer containing either the number of characters in a string

If (Len1 + Len2) = 0 Then

edited Jun 06 '16 at 18:07

answered Jun 06 '16 at 17:28

Abdellah OUMGHAR

3,627
1
11
16

Is there a way to check whether 2 strings are empty simultaneously? – Shank Jun 06 '16 at 17:54
@Shank you can use : `If (Len1 + Len2) = 0 Then` – Abdellah OUMGHAR Jun 06 '16 at 17:57
Any Idea why I am getting the error message " End with Without a with"? – Shank Jun 06 '16 at 18:10
@Shank Why you have `With NewMail` and 2 `End With` ? – Abdellah OUMGHAR Jun 06 '16 at 18:14
I am sending email with attachment....so I am checking if the folder has either the txt or pdf file. If the doesnt have both then I want to end the loop. No reason for 2 end.... I do not know how label works....I am thinking if it goes to label then it will execute the entire code else if it doesnt the the above code – Shank Jun 06 '16 at 18:17
is you have set `End If` for `If (Len1 & Len2) = 0 Then` ? – Abdellah OUMGHAR Jun 06 '16 at 18:28
If (Len1 + Len2) = 0 Then GoTo Last End If ------------------- I get the same error message. ---------------do i end the if after the label?? – Shank Jun 06 '16 at 18:31
is you have another `with` out of `With NewMail` because for each `with` you must use one `End With`, because in your code i see one `with` and 2 `End with` – Abdellah OUMGHAR Jun 06 '16 at 18:34

score 4 · Answer 2 · edited May 23 '17 at 11:58

4

You can use Trim(strFile1 & vbNullString) = vbNullString to check if the string is empty.

So:

If Trim(strFile1 & vbNullString) = vbNullString Then
   Debug.print "Empty String!"
End If

Thanks to @LordPeter

edited May 23 '17 at 11:58

Community

1
1

answered Jun 06 '16 at 17:20

BruceWayne

22,923
15
65
110

Oops, missed your answer. Removing mine which also suggested using `Trim`. +1 for using `vbNullString` over `""` (`vbNullString` is a null string pointer, `""` is uselessly allocated 2 bytes). – Mathieu Guindon Jun 06 '16 at 17:33

score 0 · Answer 3 · answered Jun 06 '16 at 17:20

0

is.empty doesn't exist for VBA, but the second option works.

Alternatively, you can write:

(strFile1 & strFile2) = vbNullString

or

(strFile1 & strFile2) = ""

answered Jun 06 '16 at 17:20

basodre

5,720
1
15
23

score 0 · Answer 4 · answered Jun 06 '16 at 17:21

0

Yet another way is:

If Len(strFile1 & strFile2) > 0 Then

I did test to ensure that strings which aren't set return a length of 0, which appeared to be the case.

answered Jun 06 '16 at 17:21

Soulfire

4,218
23
33

How do I check if (Len1 & Len2 ) = 0? – Shank Jun 06 '16 at 17:50

Condition for Empty String in VBA

4 Answers4