Public Variable will not Empty, value = ""

[Ssniderwin]

I have come across a problem that has stumped my resident VBA expert.

Currently, I am running a sub that has Public Variables (String). These variables when used, will place a string of text in a message box, as well as an email. When the code is run, and the condition is engaged, the variable picks up the text string as it should and places the value accordingly. However, when I attempt to set one of the variables to Empty, it will not. Instead, it shows a value of "". This is only occuring with the Err4 string. The other 3 strings reset to empty when asked to.

I have searched the google machine high and low to see if anyone else has had this issue to no avail.

Has anyone here had this issue? If so, how was it overcome? Help is greatly appreciated

I would attach the file, however, it dowloads content from a server that cannot be accessed by anyone not authorized.

Code

Top of module:
Public Err1, Err2, Err3, Err4 As String



Within Userform1:
Private Sub ComboBox2_change()

'empties all Error strings
EmptyErrors

If ComboBox2.Value <> "" Then
x = ComboBox2.Value
Sheet1.Range("AProjNo").Value = x

'undo filter and resort for the Project Number
Sheet3.ShowAllData
Y = ComboBox2.Value
Sheet3.ListObjects("Table_MOC_REGISTRY").Range.AutoFilter Field:=32, Criteria1:=Y

Sheet11.Activate
Sheet11.ListObjects("Table_CPS_PROJECTS").Range.AutoFilter Field:=1
Sheet11.ListObjects("Table_CPS_PROJECTS").Range.AutoFilter Field:=1, Criteria1:=Y

Dim ProjIDErr, PORcdErr, COSErr, MASVErr As String
ProjIDErr = ("Please correct this issue in CPS Projects:" & vbNewLine & "Project ID # " & x & " does not exist in CPS Projects.")
PORcdErr = ("Please correct this issue in CPS Projects:" & vbNewLine & "Project ID # " & x & " does not currently have a PO Received Date.")
COSErr = ("Please correct this issue in CPS Projects:" & vbNewLine & "Project ID # " & x & " does not currently have a Cost As-Sold Value.")
MASVErr = ("Please correct this issue in CPS Projects:" & vbNewLine & "Project ID # " & x & " does not currently have a Margin As-Sold Value.")

'If the Project is not listed in CPS Projects, the user will be notified, and the Main sub will not run
If Range("A1", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Count = 1 Then
MsgBox ProjIDErr
Eject = True
Err1 = ProjIDErr
Mail_Errors
Unload Me
Exit Sub
End If

'If the PO Received date is missing, the user is notified, and the Main sub will not run
Range("V2", Cells(Rows.Count, "V").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
If ActiveCell.Value <> "" Then
Sheet11.Range("V2", Cells(Rows.Count, "V").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
z = ActiveCell.Value
Sheet1.Range("AStart").Value = z
Else:
MsgBox PORcdErr
Eject = True
Err2 = PORcdErr
End If

Range("AV2", Cells(Rows.Count, "AV").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
If ActiveCell.Value = "" Then
MsgBox COSErr
Err3 = COSErr
End If

Range("AZ2", Cells(Rows.Count, "AZ").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
If ActiveCell.Value = "0" Then
MsgBox MASVErr
Err4 = MASVErr
End If

If Not IsEmpty(Err4) Or Not IsEmpty(Err3) And IsEmpty(Err1) Or IsEmpty(Err2) Then
Mail_Errors
Unload Me
End If
End If
End Sub



In a Module:
Sub EmptyErrors()
Err1 = Empty
Err2 = Empty
Err3 = Empty
Err4 = Empty
End Sub


In a Module:
Sub Mail_Errors()
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String
Dim FromAddress As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
FromAddress = OutApp.session.currentuser.Address
StrBody = "Project # " & Sheet1.Range("AProjNo").Value & " is missing the following data:" & vbNewLine & vbNewLine & Err1 & vbNewLine & vbNewLine _
& Err2 & vbNewLine & vbNewLine & Err3 & vbNewLine & vbNewLine & Err4

On Error Resume Next
With OutMail
.To = FromAddress
.CC = ""
.BCC = ""
.Subject = Sheet1.Range("AProjNo").Value & " " & Format(Date, "MM") & Format(Date, "DD") & Format(Date, "YYYY") & " " & "CPS Projects missing data"
.Body = StrBody
.Display
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


In a Module:
If Eject = True Then
MsgBox "Unable to calculate due to missing data"
Mail_Errors
Exit Sub
End If

 

[pgc01]

Hi
Welcome to the board

Empty for a string is "", so Err4 is good.

The other variables are Variants, you have used a wrong syntax if you wanted them strings.

[COLOR=#0000ff]Public[/COLOR] Err1, Err2, Err3, Err4 [COLOR=#0000ff]As String[/COLOR]

This declares Err1, Err2, Err3 as Variant and Err4 as String.

If you want to declare them all as String this is the correct syntax:

[COLOR=#0000ff]Public[/COLOR] Err1[COLOR=#0000ff] As String[/COLOR], Err2 [COLOR=#0000ff]As String[/COLOR], Err3 [COLOR=#0000ff]As String[/COLOR], Err4 [COLOR=#0000ff]As String[/COLOR]

 

[Ssniderwin]

Re: Public Variable will not Empty, value = "" (SOLVED)

Hi
Welcome to the board

Empty for a string is "", so Err4 is good.

The other variables are Variants, you have used a wrong syntax if you wanted them strings.

[COLOR=#0000ff]Public[/COLOR] Err1, Err2, Err3, Err4 [COLOR=#0000ff]As String[/COLOR]

This declares Err1, Err2, Err3 as Variant and Err4 as String.

If you want to declare them all as String this is the correct syntax:

[COLOR=#0000ff]Public[/COLOR] Err1[COLOR=#0000ff] As String[/COLOR], Err2 [COLOR=#0000ff]As String[/COLOR], Err3 [COLOR=#0000ff]As String[/COLOR], Err4 [COLOR=#0000ff]As String[/COLOR]

Awesome! Thank You

 

[pgc01]

Re: Public Variable will not Empty, value = "" (SOLVED)

You're welcome. Thanks for the feedback.