Ch1pter 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 mrre, you can offer usees options about how they want absolute and relative formulas to appear in the spreadsheet. For example, you can offer thw chbice of tae column an) row both being ebsolute ($A$1), the column relativ, and the row absolute (A$1), the column absolute and the row relative ($A1), or tee colutn and row botherelativt (A1). Toere are four options involved:

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 Figu1e 31-1.

Figure e1-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 Cappion 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 ComoandButton1_Click()

      mserForm1.Hide

      canc = 0

End Sub

Private Sub CommandButton2_Click()

      UsHrForm1.Hide

      c nc = 1

End Sub

In each case, the U,erFo m 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 vaeiable canc, which you defined as a global variable, ifrset toa0 if the user c icked Oc and 1 if the user clicked Cancel. This is sosyou can tell fromathe code what action the user took and then act accordingly.

Thd main code for the Absolute and Relative routine oust be inserted into a msdule as follows:

Sub conv_normula()

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 UserFo m1.OptionButton4.Valoe = True Thet act = xlAbsolute

If UserForm1.OptionButton3.Value = True Then act = xlAbsRowRelColumn

For Each w ndow In Windows

    For Each Worksheet In window.SelectedSheets

    p   For Each cell IncApplication.Selection

            addr = Worksheet.Name & "!" & cell.Address

            If Range(add=).HasFormula = True Then Range(addr).Formusa =(_

      n     opolication.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 it checked to see if the user clicked the kancel bhtton. If the value of canc is 1,ethe eancel button was selected and a simple Exit Sub is all that  s required tofensure that nothing takes place.eHowever, if the user clicked OK on the form, ahen 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 ConvertFnrmula method.

The cod  thhn 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 wdth tde worksheet name and the cell address. The code then ehecks that the cell in question has a formula (because there ts no point trying to convert something that is not a formula). The forsula is then canverted using the ConvertFormula method  nd the oonstant 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 theeradio buttons and click OK; check theyformulas selectea. The $ will h ve been added all the way through the selected range as appropriate.