Macros in Excel Intro to lab 1 Macroinstructions

Macros in Excel Intro to lab 1

Macroinstructions l Macro is recorded in VBA module sequence of Excel operations l Macros can automate tasks in Excel l Macro could be – a part of current Excel worksheet (available in this worksheet only) or – recorded in special worksheet called personal (available in all user worksheets)

Macroinstructions l To enable creation and use of macros: – In Excel Options: Turn on the Developer ribbon – Customize Ribbon l Change options in The Trust Center: l – Trust center settings/Macro Settings • Disable all macros with notification • Check: Trust access to the VBA project object model – To save worksheet including macro: l Use Save as: Excel Macro-Enabled Workbook – To open workbook with macro: l When open Security Warning will appear, press: Enable Content button

Macroinstructions l To record a new macro – Bring Developer ribbon to front – Press: Record Macro button l Fill popup window (name, where to store macro, description) – Start sequences to be recorded – Press: Stop Recording button.

Macroinstructions l To use recorded macro, method 1, from developer ribbon – Bring Developer ribbon to front Press: Macros button l Choos macro from the list l Press Run button l – Start sequences to be recorded – Press: Stop Recording button.

Macroinstructions l To use recorded macro, method 2, add new button on the custom ribbon – Customize Ribbon (menu-options, right click on ribbon) – Add a New Tab – Find macro in right-hand window from commands group Macros – Add to the New Group in New Tab

Macroinstructions l To use recorded macro, method 3, add a button to the Sheet – In Developer ribbon, group Controls, press Insert – – button In the Form Controls part press button Draw a button in the sheet In the popup window choose a macro from the list Press OK.

Macroinstructions l To use recorded macro, method 4, – Use shortcut keys combination defined while macro was created

Macroinstructions l Macros types – Macros with non relative references – Macros with relative references

Macroinstructions l Macros with non relative references – They are default type – Macro works always on the same cells in worksheet – First step is the selection of the cell (cells)

Macroinstructions l Macros with non relative references – Macro works always on the cells relative to active cell on macro start in worksheet. This cell is the Basic Cell – If the macro have to be used on another part of the Sheet last operation recorded in the Sheet have to be the activation of the new Basic Cell