A Macro to Exchange the Values of Arbitrary Cells

1. A Macro to Exchange the Values of Arbitrary Cells

2. What if I want to exchange any two cells? • This is a lot harder! • But… it will let us introduce some features of VBA • The code is in the ExchangeCellValues workbook • We’ll look at it bit by bit to understand how it works • The basic concept is the same • You are not required to understand this material for the course, but it does illustrate some interesting techniques

3. First choose the cells • We need a way to pause the macro and choose the cells to be exchanged; normally you cannot do anything else in Excel while a macro is running • The code for how to do this came from the Walkenbach book • We use the InputBox function from Excel, NOT the one from VBA, because the one in Excel can return a value of the type we need • To get that function, write Application.lnputBox • Here Excel is the Application, and InputBox is one of its methods • We would do the same thing to use other Excel functions in VBA code

4. Part of the code (explanation next) Const rangeType As Integer = 8 DimfirstCell, secondCellAs Range Dim askforCell1, askforCell2, askTitleAs String askforCell1 = "Select the first cell" askTitle = "Get Cell" SetfirstCell = Application.InputBox( _ Prompt:=askforCell1, _ Title:=askTitle, _ Type:=rangeType)

5. The Declarations Const rangeType As Integer = 8 DimfirstCell, secondCellAs Range Dim askforCell1, askforCell2, askTitleAs String • We’ve set up two variables as type Range. A Range can be one cell or a group of cells • We’ve declared variables to hold the strings we’re using • We declared a constant naming the code for a Range type in Excel, which our function needs

6. Next, Set the Strings askforCell1 = "Select the first cell by clicking on it" askTitle = "Get Cell“ • These two assignment statements set the values of the strings

7. Call the InputBox function Set firstCell = Application.InputBox( _ Prompt:=askforCell1, _ Title:=askTitle, _ Type:=rangeType) • Application.InputBox calls the function. The value goes to variable firstCell

8. The Line Continuation Character Set firstCell = Application.InputBox( _ Prompt:=askforCell1, _ Title:=askTitle, _ Type:=rangeType) • We wanted a long statement, so we broke up into several lines (normally the end of a line is the end of a statement) • Do this using a blank followed by an underscore

9. We used named parameters Set firstCell = Application.InputBox( _ Prompt:=askforCell1, _ Title:=askTitle, _ Type:=rangeType) • The parameters are the arguments or values that we send to the function. Here we tell it the prompt to use, the title to use, and the type of the response

10. Next, get the second cell… askforCell2 = "Select the second cell" SetsecondCell = Application.InputBox( _ Prompt:=askforCell2, _ Title:=askTitle, _ Type:=rangeType)

11. Then Do the Exchange temp = secondCell.Value secondCell.Value = firstCell.Value firstCell.Value = temp • Variable temp was declared as type Variant. If we were sure we were working with a particular type, we could use it instead • This code has the same structure as our previous example: we have an algorithm for exchanging the values of two cells