Public Variable will not Empty, value = ""

Ssniderwin

New Member
Joined
Sep 16, 2014
Messages
2
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

 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.

Code:
[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:

Code:
[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]
 
Upvote 0
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.

Code:
[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:

Code:
[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
 
Upvote 0
Re: Public Variable will not Empty, value = "" (SOLVED)

You're welcome. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top