What is Excel VBA Variable in Range?
A Range variable in Excel VBA is a variable that is declared to store a reference to a range of cells in a worksheet. The Range data type in VBA is specifically designed for working with cell ranges in Excel. See the example below.
Create a subroutine to find the value of cell “A1” by VBA Variable Range selection with the variable “rng” and view its value in the Immediate tab using Debug.Print.
Table of contents
Key Takeaways
- A Range variable in VBA is used to represent and manipulate a range of cells in an Excel worksheet.
- It can be declared using the Dim statement, specifying As Range to indicate the variable type.
- Use the Set keyword to assign a reference to a specific range of cells.
- The Range variable allows you to perform various operations on the specified range, such as reading values, changing values, applying formulas, formatting, etc.
- It can be used to represent dynamic ranges that adjust based on the data in the worksheet.
How to use Range Variable in Excel VBA?
See how to perform the VBA Variable range in formula by following the steps below.
Step 1: Open the Excel Workbook, and then, in the title bar, choose the “Developer” tab and click on it.
After you click the Developer icon, select “Visual Basic”. It opens the VBA editor.
In the VBA Editor, in the toolbar, click the “Insert” button and select the “Module” option.
Step 2: Create a subroutine to perform VBA Variable range selection.
Step 3: Perform VBA Variable Range selection from cells “A1” to “A6.”
Step 4: Provide values throughout the range. Here, the number 34 is used.
Step 5: Using the VBA Variable Range in formula, with the range variable, to get the value of cell “A1” and multiply it by 2. With the Cells function, the result is printed in “B2” or Cells (2,2).
Code:
Sub RangeExample()
Dim myRange As Range
Set myRange = Worksheets(“Sheet2”).Range(“A1:A6”)
myRange.value = 34
myRange.Cells(2, 2).Formula = “=A1 * 2”
End Sub
Step 6: Click the “Run” icon on the activity bar in the VBA Editor. Go back to the worksheet to view the results.
Examples
View some interesting examples of how to use the VBA Range variable in Excel.
Example #1
Consider a table of binary numbers with two inputs and an output. You must simulate an “OR” Boolean gate. In this case, the output is “TRUE” if there is 1 in either of the inputs and 0 if one is in none of the inputs.
Step 1: Define a subroutine utilizing the VBA Variable Range of rows to simulate the Boolean “OR” gate.
Step 2: Set the VBA Variable Range of rows using the xlUp function.
This line finds the last non-empty row using the End(xlUp) function. It is then counted using the Rows.Count function for the column “A” in Excel.
Step 3: Define an iterative variable and initialize it from the second row (the first row has headers).
Step 4: Construct an If-Else statement to check if either input has 1 in them. If not, return “FALSE”.
Here, instead of the If-Else conditional statement, the IIf (Immediate If) is used since the condition can be condensed into one line. It is very similar to the VBA Switch function, but here, IIf has only 2 executable parts- the true and the false part.
Step 5: Continue the loop till the last row is reached.
Step 6: Press “F5” to run the code. Then, go back to the worksheet to view the results.
You have successfully implemented a Boolean “OR” function.
Even if you add additional rows and rerun the code, you can see that the code dynamically adjusts with an increase or decrease in the number of rows.
Code:
Sub OrFunctionExample()
Dim lastRow As Long
lastRow = Range(“A” & Rows.Count).End(xlUp).Row
Dim i As Long
For i = 2 To lastRow
Range(“C” & i).value = IIf(Range(“A” & i).value > 0 Or Range(“B” & i).value > 0, “TRUE”, “FALSE”)
Next i
End Sub
Example #2
In this example, given a set of numbers, you must find their sum and print it in a cell in the same worksheet. You have to find the sum of all the numbers and print it in cell “B3.”
Step 1: Create a function to find the sum in a VBA Variable range of rows.
Step 2: Define the worksheet this sub-procedure will be specifically working in.
Step 3: Find the size of the range of numbers you need to add. Make it dynamic using the xlUp function along with the End function. It will find the last non-empty row. Using Rows.Count; you’ll find the number of non-empty rows there in column “A” in that specific worksheet.
Step 4: Define the sum variable to hold the sum of the numbers as a Double variable.
Step 5: Define an iterative variable. Use it in the for-loop going through the range you defined earlier.
Step 6: Add the values in the cell to the sum variable defined earlier. Then continue the loop and do the same until the end is reached.
Step 7: Print the sum in cell “B3” using the VBA Cells function.
Step 8: Run the subroutine to get the sum of the range of numbers. Go to “Sheet4” to view the result.
Code:
Sub SumDynamicRange()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet4”)
Dim dynamicRange As Range
Set dynamicRange = ws.Range(“A1:A” & ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row)
Dim sumResult As Double
sumResult = 0
Dim cell As Range
For Each cell In dynamicRange
sumResult = sumResult + cell.value
Next cell
Cells(3, 2).value = sumResult
End Sub
Example #3
In this example, you have a set of numbers in a table. You want to identify the numbers above 50 and mark the adjacent cell in a different color to indicate that the number is greater than 50.
Step 1: Define a sub-procedure to mark adjacent cells of numbers greater than 50 using VBA Variable Range offset.
Step 2: Set the worksheet this sub-procedure will be working on.
Step 3: Get the VBA Variable range of rows by using the xlUp function. xlUp simulates the up-arrow key on your keyboard. With this, you get the last non-empty cell and get the count using the Rows.Count function in column “A.”
Step 4: Define an iterative variable to run through each cell in the range you defined in the previous step.
Step 5: Using an If-Else conditional statement, check if each of the cell values is greater than 50.
If they are greater than 50, then use the VBA Variable Range Offset function to color the adjacent cell’s interior as orange.
Step 6: Continue the FOR-loop.
Code:
Sub HighlightCellsAbove50()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet5”)
Dim dynamicRange As Range
Set dynamicRange = ws.Range(“A1:A” & ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row)
Dim cell As Range
For Each cell In dynamicRange
If cell.value > 50 Then
cell.Offset(0, 1).Interior.Color = RGB(255, 128, 0)
End If
Next cell
End Sub
Step 7: Run the subroutine above. Then, go to “Sheet5” and view the results.
Important Things To Note
- Always use the Set keyword when assigning a reference to a Range variable. It is crucial for correctly assigning object references.
- Before performing operations on a Range variable, check if the reference is not null or empty to avoid runtime errors.
- When performing multiple operations on the same range, consider using a With statement to improve code readability and efficiency.
- Try to avoid hardcoding specific range addresses, especially if the range is expected to change.
- If you’re making multiple changes to a worksheet, consider turning off automatic recalculation (Application.Calculation = xlManual) to improve performance and then turning it back on later.
- If you create and use temporary range objects, ensure that you release them from memory using Set myRange = Nothing to prevent memory leaks.
Frequently Asked Questions (FAQs)
Yes, you can use variables to dynamically define the start and end points of a range in VBA by finding the last non-empty cell in the row using the xlUp function. The last row of the table can be found by:
lastRow = Range(“A” & Rows.Count).End(xlUp).
Row The first value can be defined as shown in a FOR-loop For i = 2 To lastRow
You can dynamically adjust the size of a range based on data using the xlUp function along with the rows function, as seen below.
Set ws = ThisWorkbook.Sheets(“Sheet1”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).
Row With the last row defined as a function, the size of the range will be automatically adjusted based on data in the worksheet.
You can loop through cells within a variable range in VBA using the “For Each” VBA Variable range offset.
Dim cell As Range
For Each cell In dynamicRange
Yes, there are some limitations you need to consider when working with Variable ranges in Excel VBA.
● Dynamic ranges may take longer to process compared to fixed ranges, especially in large datasets.
● Minimize the use of Select statements, as they can slow down code execution.
● To save computational space, you will have to minimize unnecessary interactions with the worksheet to improve performance when working with variable ranges.
● For multiple changes, consider turning off automatic recalculation (Application.Calculation = xlManual) and turning it back on afterwards to enhance performance. This is not necessary in fixed ranges
Recommended Articles
This has been a guide to VBA Variable Range. Here we learn How to use Range Variable in Excel VBA along with examples. You can learn more from the following articles –
Leave a Reply