This is the third in a series of posts in which I will introduce you to the Excel concept of dynamic named ranges, which are a very useful piece of knowledge to have at your disposal. In this weeks post, how VBA ties in with dynamic named ranges and some useful tips and tricks.

Is this relevant in VBA?

Yes, very relevant actually. If you are using Excel as your Development environment, it is highly likely that you are going to be interacting with worksheets and ranges. Either reading or writing data to ranges, or detecting changes or whatever. If you are using named ranges, then you can head off a lot of VBA problems, not the least of which being a single incorrect cell reference that can throw your code into disarray.

Use “ThisWorkbook.Names(“SalesTable”).RefersToRange” to return a reference to the named range “SalesTable”. This is a better idea than any other I’ve found to reference a cell or group of cells. Using the range method ‘Range(“A7”)’ or the cells property ‘cells(1,4)’ FREQUENTLY causes issues if you move anything around, as the parameters always reference a particular position even if you have inserted or removed data.

VBA Tips and Tricks

Here below are some of the more helpful code snippets I’ve used:

Sub DelStringsToRange(ByVal sValues As String, Delimiter As String, ByVal rdata As Range)
Dim vdata As Variant
Dim vTemp As Variant
Dim vkey As Variant
Dim i As Integer 'blah blah
Dim iOffset As Integer
    vTemp = Split(sValues, Delimiter)
    vdata = rdata.Value
    If LBound(vTemp) = 0 Then iOffset = 1 Else iOffset = 0
    For i = LBound(vTemp) To UBound(vTemp)
        vdata(1, i + iOffset) = vTemp(i)
    Next
    rdata.Value = vdata
End Sub
Set rlast = ThisWorkbook.Names("SalesTable").RefersToRange.cells(ThisWorkbook.Names("SalesTable").RefersToRange.rows.count, ThisWorkbook.Names("SalesTable").RefersToRange.columns.count)
'Returns the last cell in the table

ThisWorkbook.Names(“SalesTable”).RefersToRange.value = “”
‘Empty the table

In a worksheet change (or selection change) event:

If not intersect(Target,ThisWorkbook.Names(“SalesTable”).RefersToRange) is nothing then …….
‘If the target cell is IN the above mentioned named range then do stuff