VBA: Turn vba if statement into function

[FryGirl]

I have this code working fine, but would love to turn the if statement case statement into a function. Can somebody show me how to turn the if statement into a function. In essence I'm just trying to compartmentalize the code into small procedures.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim X       As Variant: X = Target.Value '<<<<< save the new value
    Dim MyCol   As Long: MyCol = 26
    Dim rng     As Range: Set rng = Me.Range("E:F, H:J")
    If Intersect(Target, rng) Is Nothing Or Target.CountLarge > 1 Then Exit Sub
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Undo '<<<<< restore the previous value
    End With
        
        Target.Value = X '<<<<< restore the new value
        
[COLOR=#ff0000]        If X Like "Per*" Then
            Select Case X[/COLOR]
[COLOR=#ff0000]                Case "Per Year": X = "PY"
                Case "Per Quarter": X = "PQ"
                Case "Per Month": X = "PM"
                Case "Per Day": X = "PD"[/COLOR]
[COLOR=#ff0000]            End Select[/COLOR]
[COLOR=#ff0000]         End If[/COLOR]

                If Cells(Target.Row, Target.Column + MyCol).Value = "" Then
                    Cells(Target.Row, Target.Column + MyCol).Value = X '<<<<< old value
                ElseIf Cells(Target.Row, Target.Column + MyCol).Value <> "" Then
                    Cells(Target.Row, Target.Column + MyCol).Value = Cells(Target.Row, Target.Column + MyCol).Value & ", " & X
                End If
        ThisWorkbook.Worksheets("Sheet1").Range("AE:AF,AH:AJ").EntireColumn.AutoFit
        
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

 

[lrobbo314]

Something like this. I have the main sub below for testing.

Sub main()
Dim X As Variant: X = "Per Day"
X = xAdd(X)
Debug.Print X

End Sub

Function xAdd(X As Variant) As Variant
If X Like "Per*" Then
   Select Case X
       Case "Per Year": X = "PY"
       Case "Per Quarter": X = "PQ"
       Case "Per Month": X = "PM"
       Case "Per Day": X = "PD"
   End Select
End If

xAdd = X
End Function

 

[FryGirl]

Thank you so much lrobbo314. That works perfectly.

 

[Rick Rothstein]

Here is another function that you can consider...

Function xAdd(ByVal X As Variant) As String
  If InStr("|Per Year|Per Month|Per Quarter|Per Year|", "|" & X & "|") Then xAdd= "P" & Mid(X, 5, 1)
End Function

 

[FryGirl]

Thanks Rick. That works just as well.

 

[mikerickson]

Or you could eliminate the IF from the OP code and just let the Select case stand alone.

 

[FryGirl]

Hi Mike, can you elaborate here. I'm not sure I understand.

 

[Rick Rothstein]

Hi Mike, can you elaborate here. I'm not sure I understand.

Mike was referring to this part from the code you posted...

        [B][COLOR="#FF0000"]If X Like "Per*" Then[/COLOR][/B]
            Select Case X
                Case "Per Year": X = "PY"
                Case "Per Quarter": X = "PQ"
                Case "Per Month": X = "PM"
                Case "Per Day": X = "PD"
            End Select
         [B][COLOR="#FF0000"]End If[/COLOR][/B]

You do not need the If..Then statement nor its companion End If statement. Your If..Then statement is filtering for the word "Per", but if you passed the value in X that did not contain that word, the Select Case statement would not be adversely affected as none of its Case blocks would react to it... the non-Per text would simply be ignored, so there is no reason to pre-filter the value of X beforehand.

 

[FryGirl]

Got it. Thanks Rick.