Improving a piece of code

[Trueblue862]

Hi, I'm currently learning VBA, I've created this piece of code below. I'm looking for advice on ways to improve it. This is the first piece of code that I've written entirely without copying or modifying sections from someone else. This code works perfectly for my purposes, I'm just looking for advice on how to go about making it better for future reference. I appreciate any input on this.

Private Sub LabEL_upDate()

If tbxSloppy.Value = "" And tbxJade.Value = "" Then
    LabelE.Visible = True
    LabelE.Caption = "YES!!!"
    LabelE.BackColor = vbGreen
  Else
    LabelE.Visible = False
End If
    labeL_DaTe

End Sub
Private Sub labeL_DaTe()

If LabelE.Visible = True Then Exit Sub
If tbxSloppy.Value = "PERHAPS" And tbxJade.Value = "" Then
    LabelE.Visible = True
    LabelE.Caption = "MAYBE"
    LabelE.BackColor = vbYellow
  Else
    LabelE.Visible = False
End If
    labeL_DaTe2
End Sub
Private Sub labeL_DaTe2()

If LabelE.Visible = True Then Exit Sub
If tbxSloppy.Value <> "" Or tbxJade.Value <> "" Then
    LabelE.Visible = True
    LabelE.Caption = "NO :(  "
    LabelE.BackColor = vbRed
  Else
    LabelE.Visible = False
End If

End Sub

 

[Fazza]

for sure use vbnullstring for assignments - that is, somestring = vbnullstring
and the LEN test for time savings - quicker to check if the LEN is zero

 

[Trueblue862]

This is all very interesting reading, all the different ways to solve the same problem. I really appreciate everyone's input on this.

CephasOz, what are the benefits to using "select case" instead of "if"?

 

[CephasOz]

That's a good question. Some programmers will argue the speed of one versus the other, but I just prefer the flexibility and readability of the Select Case statement. Most people are familiar with the basic form:

    Select Case strVar
        Case "a"
            MsgBox "a"
        Case "b"
            MsgBox "b"
        Case Else
            MsgBox "something else"
    End Select

and testing against other variables:

    Select Case strVar1
        Case strVar2
            MsgBox "1=2"
        Case strVar3
            MsgBox "1=3"
        Case Else
            MsgBox "1 <> 2 or 3"
    End Select

and the "Is" tests:

    Select Case strVar
        Case Is < "i"
            MsgBox "a-h"
        Case Is < "q"
            MsgBox "i-p"
        Case Else
            MsgBox "something else"
    End Select

but not many realise that you can invert the logic of "Select Case variable, Case value" to make it "Select Case value, Case variable" to test multiple variables like so:

    Select Case True
        Case bolVar1
            MsgBox "1 is True"
        Case bolVar2
            MsgBox "2 is True"
        Case bolVar3
            MsgBox "3 is True"
        Case Else
            MsgBox "none is True"
    End Select

and you can even use a selection of tests by extending the inverted logic, like so:

    Select Case True
        Case (strVar1 = strVar2)
            MsgBox "1=2"
        Case (strVar2 = strVar3)
            MsgBox "2=3"
        Case (strVar1 = strVar3)
            MsgBox "1=3"
        Case Else
            MsgBox "nothing equal"
    End Select

 

[CephasOz]

And that's all without mentioning Select's ability to deal with both lists of values and ranges of values, such as (lists):

    Select Case strVar
        Case "a", "e", "i", "o", "u"
            MsgBox "A vowel"
        Case Else
            MsgBox "Not a vowel"
    End Select

and (ranges):

    Select Case strVar
        Case "b" To "d", "f" To "h", "j" To "n", "p" To "t", "v" To "z"
            MsgBox "A consonant"
        Case Else
            MsgBox "Not a consonant"
    End Select

as well as mixing equalities, lists, ranges, and Is tests on the same line. So Select is sort of a swiss army knife of value testing.

 

[Trueblue862]

Thanks for the explanation, I’ll try and make use of it in my future attempts at writing code.