Excel Pro Logo

Dev Tips - Working Efficiently

"Work smarter not harder", what a great quote. Sure, genius is one percent inspiration and all that but if you can do more with less it's a no brainer, right? Ok the metaphors are getting a bit thick, the point is... don't grind away for hours when there are quick and safe shortcuts to take. Here's a few of my favourate time savers.

Formula Reference Style (identify columns quickly)

Using the Cells property rather than the Range object has a number of advantages, notably that you can perform calculations on the reference, and of course looping is a breeze, just increment the row or column number. For instance:

For x = 0 to Ubound(myArray)

Cells(x + 10, 1) = myArray(x)

Next x

This is the same as saying

Range("A10") = myArray(0)

Range("A11") = myArray(1)

Range("A12") = myArray(2)

Range("A13") = myArray(3)

"Having a quick way to identify column numbers can speed up development time greatly"

... and so on, up to the the Ubound of the array.

Often you might find yourself drag-selecting columns to get the column number in the Name Box, or even pointing to the screen and counting under your breath..! Having a quick way to identify column numbers can speed up development time greatly. One way is to change the cell reference style to show column numbers instead of letters (odd that I couldn't find a guide on how to do this on support.office.com, come on Microsoft, get with it!) but this can be a pain if you quickly need to see column letters too as you have to go back into File > Options yada, yada, yada.

The Application class has a ReferenceStyle property which is what gets set when you choose one or the other in File > Options, so we can set up a code snippet tied to a button on the toolbar or a hotkey to do this quickly instead of going into Options:

Sub ToggleFormulaStyle()

' Change formula style (A1 / R1C1) and column headers (letters / numbers)

If Application.ReferenceStyle = xlA1 Then

Application.ReferenceStyle = xlR1C1


Application.ReferenceStyle = xlA1

End If

End Sub

This will change back and forth between A1 and R1C1 styles. No more counting column numbers under your breath.

What if you need to quickly convert a column letter to a number? Use this formula:

Function ColumnNumber(colname As String) As Integer

' Returns a column number from a column name

ColumnNumber = Range(colname & 1).Column

End Function

Use this for the other way round, to convert a column number to a letter:

Function ColumnLetter(colNum As Long) As String

' Returns a column letter from a column number

Function ColumnLetter(colNum As Long) As String

Dim arr() As String

Function ColumnLetter(colNum As Long) As String

arr = Split(Cells(1, colNum).Address(True, False), "$")

ColumnLetter = arr(0)

End Function

Relative formulas (quickly convert to absolute)

A relative formula adapts to changes in the sheet so '=A1+A3' will automatically change to become '=A1+A2' if row two is deleted. An absolute reference - where the row and / or column is prefixed with a dollar sign - will continue to point at the original target regardless of any changes to the structure of the sheet, so '=$A$1+$A$3' will still be '=$A$1+$A$3' if row two is deleted.

A while back I had a need to convert a large number of different relative formulas to absolute across a number of worksheets so I started to write a Sub to do so. It worked well enough but there was so much code it looked like if statement hell. Looking for an alternative way I discovered the Application class already has a method to do it; Application.ConvertFormula. This was a reminder to never reinvent the wheel!

Sub AbsoluteFormulas()

' Converts relative formulas in selected cells to absolute

For Each cell In Selection

cell.Formula = Application.ConvertFormula(Formula:=cell.Formula, _

fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute)

Next cell

End Sub

Add shapes, controls and charts (quickly with the various .Add methods)

Often I'm called to create high volume output, by which I mean I large number of sheets containing a large amount of objects. For instance a survey company asked me earlier this year to create over 600 hundred charts from scratch from the data for one of their surveys. Clearly an automated solution was required as even adding a dozen charts or form controls by hand can eat up precious time, so it's worth getting to know the various 'Object.Add' methods like ChartObjects.Add, Shapes.AddShape and CheckBoxes.Add.

On one job to create a number proforma with hundreds of check boxes I saved hours by using a simple bit of code:

Sub AddCheckBoxes()

' Adds a checkbox to each cell in selection

Dim bx As CheckBox

For Each cell In Selection

Set bx = ActiveSheet.CheckBoxes.Add(cell.Left + 5, cell.Top - 5, 24, 24)

bx.Caption = ""

Next cell

End Sub

Comments on this article
Add a comment