CODEWITHSUNDEEP
vbaexcel
Shank
Shank

Reputation: 685

Condition for Empty String in VBA

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??

Upvotes: 6

Views: 19615

Answers (4)

Abdellah OUMGHAR
Abdellah OUMGHAR

Reputation: 3745

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

Upvotes: 9

Soulfire
Soulfire

Reputation: 4296

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.

Upvotes: 0

basodre
basodre

Reputation: 5770

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

Alternatively, you can write:

(strFile1 & strFile2) = vbNullString

or

(strFile1 & strFile2) = ""

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

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

Upvotes: 4

Related Questions