There must be a better way to code this

[Frank]

I am no VBA pro but I do write several semi complicated macro.

Here is that I wrote to check for two errors using if then else
statements. It works perfectly but I wonder I it could be written more
professionally.

If error1 <> 0 Then 'error 1 check
If error2 <> 0 Then 'error 1 & 2 found
MsgBox error1 & " data input are missing and highlighted in
yellow. " _
& error2 & " entries are a missmatch and highlighted in blue."
Exit Sub
Else 'error 1 only found
MsgBox error1 & " data input are missing and highlighted in
yellow."
Exit Sub
End If
Else
If error2 <> 0 Then 'error 2 only found
MsgBox error2 & " entries entries are a missmatch and
highlighted in blue."
Exit Sub
Else ' no error
Exit Sub
End If
End If

 

[Per Jessen]

Hi

The exit sub statements are not needed, as the code will terminate
after all.

Look at this:

Sub aaa()
If error1 <> 0 And error2 <> 0 Then 'error 1 & 2 found 'error 1 check
MsgBox error1 & " data input are missing and highlighted in
yellow. " _
& error2 & " entries are a missmatch and highlighted in blue."
ElseIf error1 <> 0 Then 'error 1 only found
MsgBox error1 & " data input are missing and highlighted in
yellow."
ElseIf error2 <> 0 Then 'error 2 only found
MsgBox error2 & " entries entries are a missmatch and highlighted
in blue."
End If
End Sub


Regards,
Per

 

[Bob Phillips]

Dim msg As String
msg = ""
If error1 <> 0 Then 'error 1 check
msg = error1 & " data input are missing and highlighted in yellow."
End If
If error2 <> 0 Then 'error 1 & 2 found
msg = error2 & " entries entries are a missmatch and highlighted in
blue."
End If
If msg <> "" Then MsgBox msg

HTH

Bob

 

[Bernard Liengme]

Untested but only slightly restructured

If error1 <> 0 and error2 <> 0 Then 'error 1 and error 2
detected
MsgBox error1 & " data input are missing and highlighted in yellow.
" _
& error2 & " entries are a missmatch and highlighted in blue."
Elseif error1 <> 0 'error 1
only found
MsgBox error1 & " data input are missing and highlighted in yellow."
Elseif error2 <> 0 Then 'error 2 only
found
MsgBox error2 & " entries entries are a missmatch and highlighted in
blue.
End If


There is no need for Exit Sub if this is all there is since whenever an IF
condition is satisfied control passes to the the statements below that if
and then to endif without testing other conditions

If you sub has more code after this, then how about adding after the above
If error1 <> 0 or error2 <> 0 then exit sub

 

[Ryan H]

You could try this. Its not that much shorter, but its a little easier to
read. Hope this helps! If so, let me know, click "YES" below.

Select Case True

Case error1 <> 0 And error2 <> 0
MsgBox error1 & " data input are missing and highlighted in
yellow. " _
& error2 & " entries are a missmatch and highlighted in blue."
Exit Sub

Case error1 <> 0
MsgBox error1 & " data input are missing and highlighted in
yellow."
Exit Sub

Case error2 <> 0
MsgBox error2 & " entries entries are a missmatch and
highlighted in blue."
Exit Sub

Case Else
Exit Sub

End Select

 

[Frank]

Hi

The exit sub statements are not needed, as the code will terminate
after all.

Look at this:

Sub aaa()
If error1 <> 0 And error2 <> 0 Then 'error 1 & 2 found 'error 1 check
    MsgBox error1 & " data input are missing and highlighted in
yellow. " _
    & error2 & " entries are a missmatch and highlighted in blue."
ElseIf error1 <> 0 Then 'error 1 only found
    MsgBox error1 & " data input are missing and highlighted in
yellow."
ElseIf error2 <> 0 Then  'error 2 only found
    MsgBox error2 & " entries entries are a missmatch and highlighted
in blue."
End If
End Sub

Regards,
Per





- Show quoted text -

Thanks. Not familiar with elseif but it looks very simple!

 

[Bob Bridges]

I tend to do that sort of thing this way:

msg=""
If error1 <> 0 Then msg = error1 & _
" data input are missing and highlighted in yellow. "
If error2 <> 0 Then
if msg <> "" then msg = msg & " "
msg = msg & error2 & " entries are a missmatch and highlighted in blue."
end if
If msg <> "" Then
MsgBox msg
Exit Sub
End If

 

[Rick Rothstein]

Try doing it like this...

Dim Msg As String
........
........
Msg = ""
If Error1 <> 0 Then Msg = Error1 & " data input are missing and highlighted in yellow."
If Error2 <> 0 Then Msg = Trim(Msg & " " & Error2 & " entries are a mismatch and highlighted in blue.")
If Len(Msg) > 0 Then MsgBox Msg