Outputting data without using a loop

Seungjoo Kwag — Wed, 17 Apr 2024 19:00:19 +0000

If you are creating a table in a worksheet, you can use a template sheet with pre-made column names, or sometimes the column names are generated dynamically.

If you want to print the column names in VBA code, you can either print the column names for each cell individually, or you can smartly put them in an array and loop through them if there is too much code.

The following examples show how to output multiple values in a range of cells without using a loop, including outputting a single value, using a VBA array, using an Excel array literal, mixing two arrays, and using worksheet functions TODAY() and NOW().

Sub demoResize()
  Dim myStr As String

  myStr = "a string from vba"
  With Sheet1.Range("A1")
    .Offset(0, 1).Resize(1, 2) = "A value"
    .Offset(1, 1).Resize(1, 2) = Array("Price", "Delta")
    .Offset(2, 1).Resize(3, 2) = [{"Zip", "22150";"City", "Springfield"; "State", "VA"}]
    .Offset(5, 1).Resize(1, 3) = Array([Today()], [Now()], myStr)
  End With
End Sub

If you are working on a repetitive and complex M&A valuation, you can use the Rezise function as follows. The code below is very simple to demonstrate the use of the function, but if you are working on a real-world scenario, you will want to create more complex code.

Sub demoResize2()

    With Sheet1.Range("A10")
        .Offset(0, 1) = "RETURN ON EQUITY (ROE)"
        .Offset(1, 1).Resize(1, 7) = Array("Year", 0, 1, 2, 3, 4, 5)
        .Offset(2, 1).Resize(1, 7) = Array("Profit after tax (net profit)", 0, 190, 266, 354, 457, 578)
        .Offset(3, 1).Resize(1, 7) = Array("Equity", 1100, 1290, 1556, 1910, 2367, 2945)
        .Offset(4, 1).Resize(1, 7) = Array("Return on equity", 0, 16, 19, 20, 21, 22)
    End With
End Sub

One Thing For Each Next Lacks

Seungjoo Kwag — Wed, 17 Apr 2024 18:14:09 +0000

One of the interesting things about the For~Next Loop in VBA is the Step.

For counter [ As datatype ] = start To end [ Step step ]
    [ statements ]
    [ Continue For ]
    [ statements ]
    [ Exit For ]
    [ statements ]
Next [ counter ]

Step defaults to 1, but Step 2 will cause the loop to run while incrementing the counter variable by 2, and Step -1 will cause the loop to run in reverse, decrementing by 1.

For index As Integer = 1 To 5
    Debug.Write(index.ToString & " ")
Next
Debug.WriteLine("")
' Output: 1 2 3 4 5

For number As Double = 2 To 0 Step -0.25
    Debug.Write(number.ToString & " ")
Next
Debug.WriteLine("")
' Output: 2 1.75 1.5 1.25 1 0.75 0.5 0.25 0

However, For Each~Next, which iterates over the objects in a collection, does not have a Step. For example, you can't iterate over the range of cells A1:A100, skipping even or odd rows. However, you can create such a loop with the Do Loop and Offset methods instead of For Each. The following loop iterates between A6 and A22, taking only the addresses of the even rows.

  Set rngBegin = Sheet1.Range("A6")
  Set rngEnd = Sheet1.Range("A22")
  Set rng = rngBegin

  Debug.Print rngBegin.Address
  Debug.Print rngEnd.Address
  step = 2
  Do
    Debug.Print rng.Address
    Set rng = rng.Offset(step, 0)
    If rng.Address = rngEnd.Address Then Exit Do
  Loop

This is off topic, but I should have written "If rng Is rngEnd Then Exit Do" instead of "If rng.Address = rngEnd.Address Then Exit Do". There is no runtime error when executed, but it does not exit at rngEnd, i.e. A22, as expected.
The cell addresses of rng and rngEnd in the last iteration are both 'A22', but they are not actually the same object variable. When declaring the object variables, we did the following

  Dim rng As Range
  Dim rngEnd As Range

These two variables are on the stack, but they will have different memory addresses. Therefore, although they may have the same cell address, 'A22', they will have different addresses, so they cannot be the same object.

DEV Community: Seungjoo Kwag

Outputting data without using a loop

One Thing For Each Next Lacks