Excel & VBA

Exercises in VBA for Microsoft Excel.

Excel

Documentation

Installation

• Try Microsoft 365 for free
• Download and install or reinstall Microsoft 365 or Office 2021 on a PC of Mac
• Update Office with Microsoft Update

Excel help & learning

• Get started with Excel
  • Save a workbook in another file format
  • File formats that are supported in Excel
• Formulas and functions
  • Functions
    • AVERAGE
    • COUNTIFS
    • DATE
    • FILTER
    • IFERROR
    • MEDIAN
    • QUARTILE.EXC
    • ROUND
    • STDEV
    • STDEV.P
    • STDEV.S
    • SUM
    • VLOOKUP
    • YEAR
  • More functions
    • Round a number
• Import and analyze data
  • Import data
  • Tables
    • Sort data in a table
  • Sort and filter
    • Filter data in a range or table
  • Charts
  • PivotTables
    • Create a PivotTable to analyze worksheet data
    • Calculate values in a PivotTable
    • Change the source data for a PivotTable
    • Create dynamic charts by using PivotCharts
    • Filter data in a PivotTable
    • Group or ungroup data in a PivotTable
  • Data models
• Enter and format data
  • Format
    • Use conditional formatting to highlight information
      • Highlight patterns and trends with conditional formatting
  • Layout
    • Freeze panes to lock rows and columns
      • Freeze columns and rows in Excel
• Troubleshoot
  • Repairing a corrupted workbook
  • Fixes or workarounds for recent issues in Excel for Windows

colors

Excel & Python

[ d ][ g ] Python in Excel

• [ b ] Microsoft Excel. (22 Aug 2023). "Announcing Python in Excel: Combining the power of Python and the flexibility of Excel".
• [ b ] Anaconda. "The Versatility of Excel + The Power of Anaconda".

[ h ] Zurnstein, Felix. (2021). Python for Excel: A Modern Environment for Automation and Data Analysis. O'Reilly.

VBA

Documentation

Language reference for Visual Basic for Applications (VBA)

• Visual Basic conceptual topics
  • Declaring arrays
  • Declaring constants
  • Declaring variables
  • Looping through code
  • Using For...Next statements

Getting started with VBA in Office

Concepts (Excel VBA reference)

• Programming for the Single Document Interface in Excel

[ d ] VBA for Excel

• [ d ] Range object
  • [ d ] Range.AutoFit method
  • [ d ] Range.Count property
  • [ d ] Range.End property
  • [ d ] Range.NumberFormat property
    • [ d ] Number format codes
  • [ d ] Range.NumberFormatLocal property
  • [ d ] Range.Row property
  • [ d ] Range.Style property
• [ d ] Sheets object
• [ d ] Style object
• [ d ] Styles object
• [ d ] WorksheetFunction.Match method
• [ d ] Worksheet objet
  • [ d ] Worksheet.Name property
• [ d ] Worksheets object

data types

• [ d ] Currency

[ d ] Error messages

• [ d ] Error 009 Subscript out of range
• [ d ] Error 013 Type mismatch
• [ d ] Error 450 Wrong number of arguments

Terms

• [ w ] Basic
• [ w ] Cell
• [ w ] Column Label
• [ w ] Conditional Formatting
• [ w ] Filter
• [ w ] Formula
• [ w ] Freeze
• [ w ] Function
• [ w ] Macro
• [ w ] Microsoft Excel
• [ w ] Microsoft Office
• [ w ] Module
• [ w ] Pane
• [ w ] Pivot Chart
• [ w ] Pivot Table
• [ w ] Row Index
• [ w ] Sheet
• [ w ] Spreadsheet
• [ w ] Subroutine
• [ w ] Tablular Data
• [ w ] VBScript
• [ w ] VB Visual Basic
• [ w ] VB .NET
• [ w ] VBA Visual Basic for Applications
• [ w ] Worksheet

Notes

.xlsx Excel Workbook .xlsm Excel Workbook (macro-enabled)

Module (right-click > "Insert" > "Module")

• an organizational unit of VBA code that's usually attached to a workbook or worksheet

Specify cells of a spreadsheet

• Cells(<row>, <col>) numeric, coordinate-based
  • Cells(1, 1) designates row 1, col 1
• Ranges
  • F5:F7 contiguous
  • R2,D2 noncontiguous
• .Value

clears

• .Clear
• .ClearContents
• .ClearFormats
• ActiveSheet.UsedRange.Delete
• Sheets("Sheet1").Cells.Clear

Comments '

Variable Declaration Dim <name> As <type>

Types

• Double
• Integer
• String
  • Split

Cast

• int to string Str()
• string to int Int()

Arrays

• zero-indexed

Conditionals If Then End If ElseIf Else And Or For Each