Chapter 31: Absolute and Relative Formulas

Overview

As you know, formulas can be entered using absolute or relative addressing for cell references. For example, the address A1 in a formula is relative; that is, if you copy the formula to a new location, the cell address will change relative to where you copy it. If you use an absolute address such as $A$1, however, it will always reference cell A1, no matter where it is copied to on the spreadsheet.

It is easy enough to change one cell formula to be absolute, but what if you have a whole range of formulas that needs to be changed? You end up having to change every formula in each cell manually, and mistakes can creep into your spreadsheet.

You can write code to automatically convert formulas to use absolute references in one procedure based on a user selection, and what is more, you can offer users options about how they want absolute and relative formulas to appear in the spreadsheet. For example, you can offer the choice of the column and row both being absolute ($A$1), the column relative and the row absolute (A$1), the column absolute and the row relative ($A1), or the column and row both relative (A1). There are four options involved:

Absolute and Relative States	Format
Relative Row, Relative Column	A1
Relative Row, Absolute Column	A$1
Absolute Row, Relative Column	$A1
Absolute Row, Absolute Column	$A$1

To offer the user a choice on this, you need to insert a UserForm. You will also need to define a global variable called canc (short for Cancel) to check whether the user clicks the Cancel button on the form. You need to put the following code in the declarations part of your module:

 Global canc As Integer 

See the example of the form in Figure 31-1.

Figure 31-1: Setting up the UserForm to provide the GUI interface

A set of radio buttons based within a frame control makes it easy for the user to see what is happening. Two command buttons (OK and Cancel) have also been added. You can set one of the radio buttons on by default by setting its Value property to True so you do not have to worry about the user selecting a button.

Drag the controls onto the form from the toolbox. You need two command buttons, a frame, and four radio buttons. You can change the captions on the radio buttons and the form by changing the Caption property on each control. If you have any difficulty doing this, see Chapter 9 on form construction.

The command buttons need code behind them, or they will do nothing. Double-click the command button, which will take you into the code window showing the Click event for that button. Enter the following code:

 Private Sub CommandButton1_Click() 
 UserForm1.Hide 
 canc = 0 
 End Sub Private Sub CommandButton2_Click() 
 UserForm1.Hide 
 canc = 1 
 End Sub 

In each case, the UserForm is hidden using the Hide method, which makes it invisible to the user and passes execution back to the code that called the form.

The variable canc , which you defined as a global variable, is set to 0 if the user clicked OK and 1 if the user clicked Cancel. This is so you can tell from the code what action the user took and then act accordingly .

The main code for the Absolute and Relative routine must be inserted into a module as follows :

 Sub conv_formula() 
 UserForm1.Show 
 If canc = 1 Then Exit Sub 
 If UserForm1.OptionButton1.Value = True Then act = xlRelative 
 If UserForm1.OptionButton2.Value = True Then act = xlRelRowAbsColumn 
 If UserForm1.OptionButton4.Value = True Then act = xlAbsolute 
 If UserForm1.OptionButton3.Value = True Then act = xlAbsRowRelColumn 
 For Each window In Windows 
 For Each Worksheet In window.SelectedSheets 
 
 For Each cell In Application.Selection 
 
 addr = Worksheet.Name & "!" & cell.Address 
 
 If Range(addr).HasFormula = True Then Range(addr).Formula = _ 
 Application.ConvertFormula(Formula:=Range(addr).Formula, _ 
 fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=act) 
 
 Next cell 
 
 Next worksheet 
 Next window 
 End Sub 

The first thing the code does is show the UserForm you created in the form designer and display it using the Show method. This then hands execution over to the form, which waits until one of the two buttons (OK or Cancel) is clicked.

The user selects a radio button for an option and clicks OK, which hides the form. The main code starts executing again. The variable canc is checked to see if the user clicked the Cancel button. If the value of canc is 1, the Cancel button was selected and a simple Exit Sub is all that is required to ensure that nothing takes place. However, if the user clicked OK on the form, then canc will be 0 and the user has chosen to execute the macro.

The next four rows check which action the user took on the radio buttons. Note that only one radio button can have a True value because as soon as another one is clicked, it takes the value True and gives all the others the value False.

Depending on which radio button has the True value, a variable called act is loaded with a constant value. The constants used here are already defined in VBA and work with the ConvertFormula method.

The code then cycles through all the windows in the Windows collection, through each worksheet within the selected sheets of that window, and then through each cell within the selection.

A variable called addr is loaded with the worksheet name and the cell address. The code then checks that the cell in question has a formula (because there is no point trying to convert something that is not a formula). The formula is then converted using the ConvertFormula method and the constant provided by the radio buttons.

Run the code, and the UserForm will appear as shown in Figure 31-2.

Figure 31-2: The GUI interface displayed

Make your selection from the radio buttons and click OK; check the formulas selected. The $ will have been added all the way through the selected range as appropriate.