VBA multiple if statements

user_19402004 0
Viewed 61

I have the following code which works fine. Is there a way of reducing the number of lines when using multiple if statements. None are dependent on any others, and they all refer to the same worksheet and range. It just doesn't look right when scrolling through the code. Thought there might be a more concise way? I am not using these actual variable names.

    If Not c.Offset(0, 3).Value = "" Then
        var1 = True
        var2 = c.Offset(0, 3).Value
    End If
    
    If Not c.Offset(0, 4).Value = "" Then
        var3 = True
        var4 = c.Offset(0, 4).Value
    End If
    
    If Not c.Offset(0, 5).Value = "" Then
        var5 = True
        var6 = c.Offset(0, 5).Value
    End If
    
    If Not c.Offset(0, 6).Value = "" Then
        var7 = True
        var8 = c.Offset(0, 6).Value
    End If
    
    If Not c.Offset(0, 7).Value = "" Then
        var9 = True
        var10 = c.Offset(0, 7).Value
    End If
if-statement vba excel
2 Answers

You can use a switch statement, or instead of using a new if statement for each condition, create an else if which making the code shorter and more readable.

user_186840550

Avoid Using a Ton of Variables (Array)

  • When you have the need to use a ton of variables, especially of the same data type, it's time to consider using a data structure (array, dictionary, collection...).
  • The following example explores the use of arrays. Since there is not enough information, using one of the other data structures may be more appropriate.
  • To quickly learn about arrays, you could watch the videos from this Excel Macro Mastery playlist.

enter image description here

The Code

Option Explicit

Sub ArraysInsteadOfTonsOfVariables()

    ' Using the Array function, write the column offsets to a variant array.
    ' (Using 'VBA.' in front of 'Array' ensures a zero-based array.)
    Dim ColumnOffsets() As Variant: ColumnOffsets = VBA.Array(3, 4, 5, 6, 7)
    
    ' Reference the initial cell ('iCell').
    ' ('ThisWorkbook' means the workbook containing this code.)
    Dim iCell As Range
    Set iCell = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    
    ' Write the upper limit of the 'ColumnOffsets' array to a variable.
    ' (the lower limit is '0').
    Dim UB As Long: UB = UBound(ColumnOffsets)
    
    ' Define an array of the same size as the 'ColumnOffsets' array
    ' which will hold the booleans ('var1, var3, var5, var7, var9').
    Dim Booleans() As Boolean: ReDim Booleans(0 To UB)
    
    ' Define an array of the same size as the 'ColumnOffsets' array
    ' which will e.g. hold the strings ('var2, var4, var6, var8, var10').
    Dim Strings() As String: ReDim Strings(0 To UB)
    
    ' Print the title and the headers to the Immediate window ('Ctrl+G').
    Debug.Print "For cell '" & iCell.Address(0, 0) & "'"
    Debug.Print "Index", "Offset", "Address", "Boolean", "String"
    
    Dim cCell As Range ' current (offsetted) cell
    Dim cIndex As Long ' current index
    Dim cString As String ' current string
    
    ' Loop through the elements of the arrays (they are all of the same size).
    For cIndex = 0 To UB
        ' Reference the current cell.
        Set cCell = iCell.Offset(, ColumnOffsets(cIndex))
        ' Write the current string (string representation
        ' of the current cell's value) to a variable.
        cString = CStr(cCell.Value)
        If Len(cString) > 0 Then ' is not ""
        ' or 'If cString <> ""' or 'If cString <> vbNullString'
            Booleans(cIndex) = True
            Strings(cIndex) = cString
        'Else ' is "" ' do nothing
            ' Since the 'Booleans' array is declared 'As Boolean',
            ' each of its elements is assigned the default value 'False'.
            ' Since the 'Strings' array is declared 'As String',
            ' each of its elements is assigned the default value '""'.
        End If
        ' Print what was written to the arrays
        ' to the Immediate window ('Ctrl+G').
        Debug.Print cIndex, ColumnOffsets(cIndex), cCell.Address(0, 0), _
            Booleans(cIndex), Strings(cIndex)
    Next cIndex
    
End Sub

The Results

For cell 'A1'
Index         Offset        Address       Boolean       String
 0             3            D1            True          Peter
 1             4            E1            False         
 2             5            F1            True          Helen
 3             6            G1            False         
 4             7            H1            True          John
user_98140690
Related