Writing VBA Macros Record a new Macro recording
Writing VBA Macros Record a new Macro, recording only the name, shortcut and description Stop recording Open macro for editing and enter VB code Test
Moving around the Spreadsheet To move the cursor to a particular cell (starting point) Range(“A 4”). select Range(“Named. Cell”). select This is now the “Active Cell”
Referencing a Cell from Active. Cell. offset (R, C) e. g. Active. Cell. offset(0, -1) refers to the cell one to the left of the Active. Cell
To place information from a VB variable into a spreadsheet Range(“D 4”) = Tax. Rate Range(“Result”) = Sum Active. Cell = Salary * Rate Active. Cell. offset(12, 0) = total. Cost Active. Sheet. Cells(11, 9) = Total Worksheets(“My. Sheet”). Cells(1, 1) = title Sheet 2. cells(1, 3) = Title 'Refers to sheet 2 row 1, column 3
To input from a spreadsheet to variable Amount = Active. Cell. Offset(0, -3) Amount =Active. Sheet. Cells(9, 1) 'current sheet Row 9, column 1
Loops Do until Active. Cell. offset(Row, 0) = “” 'put your actions here Row = Row +1 Loop
Other Features Assigning Macros to Buttons Rather than using Ctrl + Character all the time More handy to have a button Insert Shapes Draw button Right Click on the button Assign macro Any of the VB commands can be used Input. Box Active. Cell = Input. Box(“Enter you name”, ”Namebox”) Msgbox
- Slides: 7