18 Questions
What is the default mode in which Excel records macros?
Absolute mode
What is the purpose of the 'Use Relative References' option in macro recording?
To make the macro more flexible and adaptive
What happens when you run a macro recorded in absolute mode?
The macro produces the same result each time
Which of the following is NOT a step in recording a macro in relative mode?
Selecting a range of cells
What is the advantage of recording a macro in relative mode?
It makes the macro more flexible and adaptable
What do you need to do before selecting 'Use Relative References'?
Select any single cell
What is the main reason to learn about relative and absolute references in Excel VBA?
To understand how the Macro Recorder works
What is the difference between the code lines Range("D4").Formula = "=B3*10" and Range("D4").FormulaR1C1 = "=R3C2*10"?
The first code line uses absolute references, while the second uses relative references
What is the purpose of the FormulaR1C1 property in Excel VBA?
To enter formulas in R1C1 style
How can you make a frequently used macro easily accessible in Excel?
By adding it to the Quick Access Toolbar
What is the result of the code line Range("D4").FormulaR1C1 = "=R[-1]C[-2]*10"?
Cell D4 references cell B3, one row above and two columns to the left
In which workbook is the macro stored when you choose to store it in the Personal Macro Workbook?
All workbooks (Excel files)
What is the purpose of declaring a variable called temp in the example code?
To store the value of cell A1 temporarily
What is the result of clicking the command button twice in the example?
The values of cells A1 and B1 are swapped once
How do you open the Visual Basic Editor in Excel?
By clicking on the Developer tab and then clicking Visual Basic
What is the purpose of the code line Range("A1").Value = Range("B1").Value?
To write the value of cell B1 to cell A1
What is a procedure in Excel VBA?
A sub or a function
What is the name of the procedure created in the example code?
Cyan
Study Notes
Macros in Excel
- Macros in Excel are code that can be run to perform specific tasks, and they are only available for the specific sheet they are assigned to.
Recording Macros
- Excel records macros in absolute mode by default, but it can be changed to relative mode.
- To record a macro in absolute mode: • Click Record Macro • Select a cell and perform actions • Click Stop Recording • The macro will always produce the same result
- To record a macro in relative mode: • Select "Use Relative References" • Select a cell and perform actions • Click Stop Recording • The macro will produce results relative to the initial selected cell
FormulaR1C1
- FormulaR1C1 is a style in Excel VBA that refers to cells in a specific way.
- A1 style:
Range("D4").Formula = "=B3*10" - R1C1 style:
Range("D4").FormulaR1C1 = "=R3C2*10"(absolute reference) - RC style:
Range("D4").FormulaR1C1 = "=R[-1]C[-2]*10"(relative reference)
Adding a Macro to the Toolbar
- To add a macro to the Quick Access Toolbar: • Record an empty macro • Name the macro and store it in Personal Macro Workbook • Close the Visual Basic Editor • Click the command button on the sheet • The macro will be available in the Quick Access Toolbar
Visual Basic Editor
- The Visual Basic Editor is where you can edit and run VBA code in Excel.
- To open the Visual Basic Editor: • On the Developer tab, click Visual Basic
Swapping Values
- To swap two values in Excel VBA: • Declare a variable called temp of type Double • Initialize the variable temp with the value of cell A1 • Write the value of cell B1 to cell A1 • Write the value of temp to cell B1 • Click the command button to run the code
Running Code from a Module
- To run code from a module in Excel VBA: • Open the Visual Basic Editor • Click Insert, Module • Create a procedure (macro) called Cyan • Run the code by clicking the Run button or pressing F5
Learn about the different formula styles in Excel VBA, including A1, R1C1, and RC. Understand how to use each style to reference cells and perform calculations. This quiz covers the basics of Excel VBA formula syntax and styles.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
