Today I am going to show you some cool VBA techniques.

I use these techniques all the time. They are simple to use and very useful.

Enjoy...

Read a range to an array in one line

You can fill an array from a range of cells and vice versa using just one line of code for each operation.

(For this code to work, create a new workbook and add a second worksheet so that you have Sheet1 and Sheet2. Add some test values to the range A1:Z1 in Sheet2.)

Public Sub UseArrays()

    ' Create array
    Dim Marks As Variant

    ' Read 26 values into array from sheet2
    Marks = Sheet2.Range("A1:Z1").Value

    ' Write the 26 values to the third row of sheet1
    Sheet1.Range("A3:Z3").Value = Marks

End Sub

Get the parent of a range

When you are debugging, it is often useful to know the workbook or worksheet that a range belongs to.

You can easily do this using the Parent property of the range and the worksheet.

Note: Debug.Print writes to the Immediate Window - Ctrl + G to view.

Sub GetParents()

    Dim rg As Range
    Set rg = ActiveSheet.Range("A1:A5")

    ' Print the worksheet name
    Debug.Print "Worksheet is: " & rg.Parent.Name

    ' Print the workbook name
    Debug.Print "Workbook is: " & rg.Parent.Parent.Name

End Sub

Simple but useful properties

The following are examples of some simple but useful properties


' Print the address of a range
Dim rg As Range
Set rg = Range(Cells(1, 1), Cells(56, 22))
Debug.Print rg.Address

' Print current user
Debug.Print Application.UserName

' Print name, path and fullname of workbook
Debug.Print ThisWorkbook.Name
Debug.Print ThisWorkbook.Path
Debug.Print ThisWorkbook.FullName

Today we looked at some useful VBA techniques, Next up.. Never look for the last row again!

Kind Regards
Paul Kelly

ExcelMacroMastery.com - The number one site for practical real-world VBA code.

The Excel VBA Handbook - A step-by-step blueprint for building VBA applications.