Excel VBA Basics Tutorial

Introduction

• Aim: Teach absolute basics of writing code in Excel VBA.
• Assumes no prior knowledge of VBA.
• Focus on:
  • Writing a subroutine or program.
  • Good practice guidelines for code layout and comments.
  • Practical applications of VBA in Excel.
  • Running and saving VBA code.
  • Handling security settings for macros.

Getting Started

Visual Basic Editor (VBE)

• Access VBE by pressing ALT + F11.
• Create a Module to hold your code.
  • Right-click in Project Explorer to insert a module.
  • Rename your module in the Properties window (e.g., VBABasics).

Writing a Subroutine

• Begin with the keyword Sub followed by a name (no spaces).
• Use descriptive names (e.g., CreateAndLabelNewSheet).
• End Sub automatically appears to close the subroutine.
• Code is written between Sub and End Sub.

Code Layout and Comments

• Indentation: Use the TAB key to indent code blocks for readability.
• Comments:
  • Use ' or REM to start comments.
  • Comments help explain code functionality.
  • Color of comments by default is green.

Writing VBA Instructions

Basic VBA Sentence Structure

• Format: Object.Method or Object.Property = value.

Creating a New Worksheet

• Use Worksheets.Add to create a new sheet.

Adding Titles to Cells

• Reference individual cells using Range (e.g., Range("A1").Value).
• Assign values to properties using =.
• Copy-paste code lines to quickly modify multiple cells.

User Values and Functions

• Use functions to add dynamic data:
  • Environ("username") for current username.
  • Date for the current date.

Formatting Cells

• Modify properties like Font.Color and Interior.Color.
• Use properties to change cell formats:
  • Font.Color: e.g., Font.Color = vbBlue.
  • Interior.Color: Use RGB or pre-defined colors like vbBlue.

Running and Saving VBA Code

Running the Code

• Save the code before running to avoid irreversible changes.
• Run from VBE:
  • Use Run menu, F5, or the green triangle.
• Verify changes in Excel (e.g., new sheet creation).

Saving the Workbook

• Save as Excel Macro-Enabled Workbook (.xlsm) to preserve macros.

Running Macros from Excel

• Developer tab or ALT + F8 to run macros.

Security Settings for Macros

Enabling Macros

• Different prompts for Excel 2007, 2010, 2013:
  • Enable Content button or Options dialog in 2007.
• Excel 2003 requires changing macro security settings to medium.

Changing Security Settings

• Access through Trust Center in Excel Options.
• Recommended setting: Disable all macros with notification.

Conclusion

• Review of security settings and Excel versions.
• Reminder of more online resources available at www.yl.c.