CODEWITHSUNDEEP
excelvba
user20114520
user20114520

Reputation:

Once variable is assigned a value do not let it be overwritten

I have a global variable ErrorMsg that can be set based on multiple different functions. The problem I am running into is if a certain condition is met, then the macro will assign ErrorMsg a text string that will be displayed in the worksheet Log Sheet. The way I have my code set up, even if an error occurs and ErrorMsg has a set text string assigned to it, the macro will continue running all the way through all the data until it is at the end. The issue with this that a lot of the conditions stem off each other. So if the function GetColumnIndex throws an error, assigns ErrorMsg a text string and continues on, then the next function like GetData will also throw an error and overwrite the value of ErrorMsg to be whatever it is set within its function. This occurs because the value that comes from GetColumnIndex is an input in GetData function. So my question is, how do I tell the macro to recognize once a value has been set to ErrorMsg to continue running through the code, but to not overwrite the variable with an updated text string. As you can see in the script, each function has a line that defines what ErrorMsg is. I am looking to find a way for if GetColumnIndex set ErrorMsg to be the text string to not have GetData overwrite ErrorMsg to a different text string.

Here are the functions I mentioned above.

Global ErrorMsg As String 
Sub Main 
Dim cell As Range, ws As Worksheet, sysnum As String, sysrow As Integer, wb As Workbook, logsht As Worksheet 
Dim power_col As Long, power_value As Double 

Set wb = ActiveWorkbook 
Set ws = ActiveWorksheet 
Set logsht = wb.Worksheets("Log Sheet") 

For Each cell In ws.Range("E2", ws.cells(ws.Rows.Count, "E").End(xlUp)).cells 
sysnum = cell.Value
sysrow = cell.row

power_col = GetColumnIndex(ws, "Power (mW)")
power_value = GetJiraData(ws, sysrow, power_col)

Dim begincell As Long
With logsht 
    begincell = .cells(Rows.Count, 1).End(xlUp).row
    .cells(begincell + 1, 2).Value = sysnum
    .cells(begincell + 1, 2).Font.Bold = True
    If Not ErrorMsg = "" Then
        .cells(begincell + 1, 3).Value = "Complete with Erorr - " & ErrorMsg
        .cells(begincell + 1, 3).Font.Bold = True
        .cells(begincell + 1, 3).Interior.Color = vbRed
    Else
        .cells(begincell + 1, 3).Value = "Completed without Errors"
        .cells(begincell + 1, 3).Font.Bold = True
        .cells(begincell + 1, 3).Interior.Color = vbGreen
    End If
End With

Next cell 

End Sub 

Function GetColumnIndex(sht As Worksheet, colname As String) As Double 
Dim paramname As Range
Set paramname = sht.Range("A1", sht.cells(2, sht.Columns.Count).End(xlToLeft)).cells.Find(What:=colname, Lookat:=xlWhole, LookIn:=xlFormulas, searchorder:=xlByColumns, searchdirection:=xlPrevious, MatchCase:=True) 
    If Not paramname Is Nothing Then 
        GetColumnIndex = paramname.Column
    ElseIf paramname Is Nothing Then 
       ErrorMsg = colname & " column index could not be found. Check before running again."
    End If
End Function

Function GetData(sht As Worksheet, WDrow As Integer, parametercol As Long) 
GetData = sht.cells(WDrow, parametercol)
If GetData = -999 Then
ElseIf GetData < 0 Then
    ErrorMsg = "Data cannot be a negative number. Check before running again." 
End If
End Function

Upvotes: 0

Views: 59

Answers (1)

Ike
Ike

Reputation: 13024

You can use

If lenb(ErrorMsg) > 0 then ErrorMsg = ErrorMsg & vbCrLf`
ErrorMsg = ErrorMsg  & "your text"

And when you have written the error message to the sheet, clear ErrorMsg:`

ErrorMsg = vbNullString

Upvotes: 1

Related Questions