About David Richardson

Posts by David Richardson:

Dynamic Named Ranges: Advanced Material

Excel, Microsoft, Office, Software, Spreadsheet, VBA , , , , , , , , , 0 Comments

This is the fourth and final 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, some of the more advanced aspects of dynamic named ranges.

Advanced Material

Match

When defining a dynamically named range, one method you can use is the Offset – CountA example that I talked about earlier. This is great, BUT if there are blank cells in the column or row, there are problems. An alternative to the “Offset – CountA” method is the “Match” method. Simply replace the CountA portion of the formula with:

MAX(IFERROR(MATCH("*",A:A,-1),0),IFERROR(MATCH(9E+307,A:A,1),0))

This rather complicated formula will provide the row number of the last non blank cell in the column!

Single Column:

Having used a dynamically named range to define the entire sales table, you may find that you need a particular column (or row) of the table for various reasons. This CAN also be defined by name, and can be linked to the previous name. A simple index function can return a particularly numbered column (or row) from a table. For example:

=INDEX(SalesTable,0,7)

returns the 7th column of the table. However, if you “move things around”, the 7th may no longer be relevant. My rule of thumb is that “1st” is acceptable, because you almost NEVER move the first column of a table, but anything else should be referenced with a Match. For example if we want a reference to the “Total” column of sales table, we can use the Match function to find the column with “Total” as the heading. Like so:

=INDEX(SalesTable,0,MATCH(“Total”,OFFSET(INDEX(SalesTable,1,0),-1,0),0))

Remembering that we have not been including the headings in our named range, we reference it by getting the first row:

INDEX(SalesTable,1,0)

and offsetting this by -1 rows (ie up 1 row). Of course if we are going to be using the headings frequently, we could always define them as their own named range:

=OFFSET(INDEX(SalesTable,1,0),-1,0)

called “SalesTable_Heading” and the “Total” named range would look like:

=INDEX(SalesTable,0,MATCH(“Total”,SalesTable_Heading,0))

Relative Named Ranges

By default all named ranges are absolute, and generally this is a GOOD thing. However you CAN make relative named ranges. There are VERY few times this is worthwhile, but for the sake of completeness here are some examples that no one has ever actually used. EVER.

Right

If you select cell A1 and then use the Name Manager to define a named range called “right”. Define this as B2 (ensuring that you have no $ signs), and the formula “=right” will return the value in the cell to the right.

There are two things to note here. The first is that if you use this formula in the last column, you will get an error. Also note that Excel REQUIRES all named range cell references to be sheet qualified, so this range would need to be worksheet scoped.

Sheet1

If you select A1 on Sheet2 and define a named range “Sheet1” that equals Sheet1!A1 (again, no dollars signs) then the formula “=Sheet1” would return the value in Sheet1 that corresponds to your current cell. Note that if you use this formula on Sheet1, you will get a circular reference.

The one (COMPLEX) time I have used this:

Imagine a worksheet containing an unknown number of datasets. Each dataset is a “mini table” of 10 rows and 5 columns, and there is a blank row between each dataset. The first row of each data set has, in column 2, a reference id. In several places in the dataset, I need to use that reference number in a formula. One way to do this, is to use =a2 in the first dataset, =a11 in the second, =a23 in the third, etc. I don’t like this for several reasons. A COMPLEX alternative is to define a named range called “Dataset” with the following formula:

=OFFSET(Sheet1!$A$1,TRUNC((ROW()-1)/11)*11,0,11,5)”

Thus, the formula “=Dataset” will always return the 11×5 table (including the blank row) of the CURRENT dataset. This then allows you create (for example) a named range “ID” defined as:

=INDEX(ID,1,2)

which would always be the id from the current dataset. If you modify the worksheet to include more rows per dataset, or have the ID somewhere else in the dataset or whatever, then this would no longer work, but you would only have 1 place where you needed to modify it, rather than in numerous scattered formulae.

Combination

Of course you can combine relative and dynamic ranges, giving you an AWFUL (but effective) named range such as this:

=OFFSET(Sheet1!$A$1,TRUNC((row()-1)/MATCH(Sheet1!$A$1,OFFSET(Sheet1!$A$1,1,0,500,1),0))* MATCH(Sheet1!$A$1,OFFSET(Sheet1!$A$1,1,0,500,1),0),0, MATCH(Sheet1!$A$1,OFFSET(Sheet1!$A$1,1,0,500,1),0),COUNTA(Sheet1!$1:$1))

which would return the current dataset regardless of how many rows or columns there are in each dataset (as long as they are the same)

NOTE: Do NOT use relative named ranges from VBA. Just don’t.

UDFs

I don’t use many UDFs in Excel, but there are a couple that are particularly helpful in terms of Named ranges.

Blank

Returns the index of the first blank cell in a 1 dimensional range. This works exactly like the Match function, but looks for an empty cell (which you can’t do with match)

Public Function Blank(rData As Range) As Variant
    If rData.Columns.Count <> 1 And rData.Rows.Count <> 1 Then
        Blank = CVErr(xlErrNA)
        Exit Function
    End If
    If rData.Cells(1, 1).Formula = "" Then
        Blank = 1
        Exit Function
    End If
    If rData.Cells(IIf(rData.Columns.Count = 1, 2, 1), IIf(rData.Rows.Count = 1, 2, 1)).Formula = "" Then
        Blank = 2
        Exit Function
    End If
    If rData.Columns.Count = 1 Then
        If Not Intersect(rData.Cells(1, 1).End(xlDown).Offset(1, 0), rData) Is Nothing Then Blank = rData.Cells(1, 1).End(xlDown).Offset(1, 0).Row + 1 - rData.Cells(1, 1).Row
    Else
        If Not Intersect(rData.Cells(1, 1).End(xlToRight).Offset(0, 1), rData) Is Nothing Then Blank = rData.Cells(1, 1).End(xlToRight).Offset(0, 1).Column + 1 - rData.Cells(1, 1).Column
    End If
End Function

IndirectD

Allows the use of dynamic and non-contiguous ranges with indirect functionality

Public Function IndirectD(sRange As String) As Variant
    Set IndirectD = Application.Caller.Parent.Range(sRange)
End Function

 

Dynamic Named Ranges: VBA

Excel, Hints and Tips, Microsoft, Office, Software, Spreadsheet, VBA , , , , , 0 Comments

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

Dynamic Named Ranges: Discussing how, scope, and limitations

Excel, Microsoft, Office, Software, Spreadsheet, VBA , , , , , , 0 Comments

This is the second 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 would you go about creating a dynamic named range, what scope is in a named range context, and some limitations that dynamic named ranges have.

How do I go about creating a dynamic named range?

Dynamic named ranges are generated using the Name Manager, or the Define Name button. Set the “Name” and “Scope” (see below) as usual, but in the “refers to” space, enter a formula instead of a cell reference.

The most common formula for a dynamic named range is the offset formula. This formula returns a range from a specific cell. This is a sample formula for a dynamic named range:

=OFFSET($A$1,1,0,MAX(1,COUNTA($A:$A)-1),MAX(1,COUNTA($1:$1)))

This formula basically states:

  • Start in A1
  • Go down 1 cell
  • Go across 0 cells

Return a range that starts HERE, and has:

  • Rows equal to 1 less than the number of values in column A (but at least 1) and
  • Columns equal to the number of values in Row 1 (but at least 1)

In a standard table, with headings, starting in A1, this would return the entire table, not including the headings, and as rows or columns were added or removed from the table, the range would STILL return the entire table, and nothing but the table!

Why “at least 1”?

It is generally a bad idea to have named range (dynamic or otherwise) that does not actually return a range. Using this range would generate a #name error, instead of a more relevant error (such as #N/A for a vlookup). It can also cause VBA exceptions. Generally it is better to return a range.

Why start from A1, when we actually want from A2?

One of the “issues” with named ranges, is that if you delete the cell(s) the range refers to (the ACTUAL cells, not simply the contents), the named ranged will reference #REF and have to be manually adjusted. In this case, although the named range “starts” in A2, the reference uses A1, so as long as A1 remains present, the range will work correctly. If we had referenced A2, then deleting all rows in the table (a common enough occurrence) would “break” the named range.

Scope

In Excel, a named range can “belong” to either a workbook or a worksheet. The default is workbook and this is usually the better idea. Generally the only time I use a worksheet scoped named range is when I have very similar worksheets in a workbook and I want to use the same named range on multiple sheets.

Limitations

Unfortunately there ARE some limitations to named ranges:

Limited Naming Options

There are a variety of characters that cannot be used in the name of a named range, including spaces.

Array Formulae

These CANNOT be used to define a named range, which is unbelievably irritating.

Indirect

Indirect sadly does not play well with named ranges. You CANNOT use indirect to reference either a dynamic named range, or a non contiguous named range (one where the range does not form a perfect rectangle). Luckily, there is a VBA work around, which I will be going over in the next post.

Charts

Charts do not play well with named ranges. Unfortunately this is a lot harder to define than the issues with indirect as some things work and some things don’t. It also changes SIGNIFICANTLY between Office 2007 and Office 2010, so just be aware that they may not work the way you expect.

Dynamic Named Ranges: What are they, and why would you use them?

Excel, Microsoft, Office, Software, Spreadsheet, VBA , , , , , 0 Comments

This is the first in a series of posts in which I will introduce you to the Excel concept of dynamic named ranges, which is a very useful piece of knowledge to have at your disposal. In this week’s post, I’ll discuss what dynamic named ranges are, and why you would want to use them.

What is a dynamic named range?

In Excel, a group of cells is referred to as a range of cells (for example the range A7:G42). Any range of cells can be given a name, such as “SalesTable” (making it a “named range”), so that the name can be substituted in place of the range reference (i.e. instead of typing A7:G42 into a formula you can use “SalesTable”).

A “dynamic” named range is one where the size or shape of the range changes based on a formula. So instead of saying “SalesTable” is from A7 to G42, you would say that “SalesTable” is from A7, down by the number of entries in column A, and across by the number of entries in row 7. This of course means that whenever information is added immediately below or immediately right of the range, the size of the “SalesTable” range automatically extends!

Why would I use a dynamic named range?

There are a variety of reasons to use dynamic named ranges. The three main ones are:

Readability

A2:G47 doesn’t mean anything, but SalesTable does.

Efficiency

Without using a dynamic named range, it is very tempting to use an entire column or row reference in a formula (A:A), so that when rows are added to a table, all the formulae relying on that table still work correctly. This does of course mean that each of those formulae are calculating across several MILLION rows. Excel is reasonably smart about doing this, but it WILL eventually slow right down.

Accuracy

Ever transposed a couple of numbers in a cell reference and spent 4 hours trying to work out why nothing adds up? Named ranges avoid this. The #Name error makes it REAL obvious you’ve made a mistake in typing the name of the range, so it’s easy to correct. In fact from 2007 onward Excel will even provide “code completion” in the formula bar so that you can select the named range once you have started typing.

 

Moving VBA code between 32-bit and 64-bit Microsoft Office

Moving VBA code between 32-bit and 64-bit Microsoft Office

Excel, Software, VBA , , , , , , 0 Comments

Introduction

In the last week I’ve had a client upgrade to 64-bit Microsoft Office and it has caused some havoc with parts of my VBA. It’s the first time this has been an issue, and I thought I’d detail some of the effects. If you don’t care about reasons or explanations, skip to the summary to see how to avoid or fix the issues.

There are actually very few issues with moving code from 32-bit Office to 64-bit Office, but a couple of them are significant. More →