Excel Vb a Quick Reference

5 steps for creating a macro

Declare variables

1. 2. 3. 4. 5.

Dim

Procedure variable

Static

Procedure variable that retains value

Private

Module variable

Public

Globally used variable

Know what you want Know how to do it without a macro Record or write a macro Test the macro and clean up the code Share with others

How to record a macro

Declare procedures

1. Choose

Private Function

Function can only be called by other procedures in the same module

Public Function

Function can be called by the user and any procedure

Private Sub

Sub procedure can only be called by other procedures in the same module

Public Sub

Sub procedure / macro can be called by the user or Excel and any procedure

 |

2. Enter a macro name. No space allowed. 3. Optional: Specify a keyboard shortcut, macro location, and/or description. 4. Click . 5. Perform the actions 6. Choose  | .

Decision Structures

VBA terms Object ............. Noun Property .......... Adjective Method ........... Verb Argument........ Adverb

Statement ...... Sentence Procedure ...... Paragraph Module ........... ........... Chapter Project ............ Book

VBA code colors

If Condition1  Then statement(s) ElseIf Condition2  Then statement(s) Else statement(s) End If Select Case Variable Case expression1 statement(s) Case expression2 statement(s) Case else statement(s) End Select

Black ............... Regular code Blue.................Keywords Green .............. Comments Red ................. ................. Errors

Variable data types bln

Boolean

TRUE (1) or FALSE (0)

byt

Byte

0 and 255

int

Integer

-32,768 and 32,767

lng

Long

-2,147,483,648 and 2,147,483,647

sng

Single

Up to 7 decimal places

dbl

Double

Up to 15 decimal places

cur

Currency

Money – up to 4 decimal places.

dtm

Date / Time

1/1/0100 to 12/31/9999 00:00:00 and 23:59:59

str

String

Text up to 2 billion characters.

wkb

Workbook

A workbook

wks

Worksheet

A worksheet

rng

Range

One or more cells

Loop Structures For counter   =  = Start t Start to o Stop Step Stop Step Increment Statement(s) Next counter For Each object variable in collection Statement(s) Next Do While | Until condition Statement(s) Loop Do Statement(s) Loop While | Until condition

Page 1 TechMentors Computer Training & Consulting Services © Copyright 2011 All rights reserved

www.TheTechMentors.com

408.375.6190

ActiveChart ActiveSheet AutoUpdateFrequency Charts FileFormat FullName HasPassword HasVBAProject Name Names Path ReadOnly Saved Sheets Worksheets

AutoFilter Cells CircularReference Columns Comments Count DisplayPageBreaks EnableAutoFilter Hyperlinks Name Names PageSetup Parent Range Rows Shapes Sort StandardHeight StandardWidth  Visible

AddIndent Address AllowEdit Borders Cells Characters Column Columns ColumnWidth Comment Count CurrentRegion Dependents End EntireColumn EntireRow Font Formula FormulaR1C1 HasFormula

Height Hidden Horizontal Alignment Hyperlinks IndentLevel Interior Item Locked MergeArea MergeCells Name NumberFormat Offset Orientation OutlineLevel PageBreak Parent PivotCell PivotField

PivotItem PivotTable Precedents PrefixCharacter Range Resize Row RowHeight Rows Text UseStandardHeight UseStandardWidth  Validation  Value  VerticalAlignment Width Worksheet WrapText

Activate AddToFavorites ApplyTheme BreakLink Close OpenLinks PrintOut PrintPreview Protect Save SaveAs Unprotect

Activate Add Calculate Copy Delete Move Paste PasteSpecial PivotTableWizard PrintOut PrintPreview Protect ResetAllPageBreaks SaveAs Select Unprotect

Activate AdvancedFilter AddComment ApplyNames AutoFill AutoFilter AutoFit BorderAround Calculate CheckSpelling Clear ClearComments ClearContents ClearFormats ClearHyperlinks Copy

CreateNames Cut DataSeries Delete FillDown FillLeft FillRight FillUp Find FindNext FindPrevious Insert InsertIndent Justify Merge PasteSpecial

PrintOut PrintPreview RemoveDuplicates RemoveSubtotal Replace Select Sort Subtotal TextToColumns UnMerge

Activate AfterSave BeforeClose BeforePrint BeforeSave NewChart NewSheet Open SheetCalculate SheetChange WindowActivate WindowDeactivate WindowResize

Activate BeforeDoubleClick BeforeRightClick Calculate Change PivotTableUpdate The range object has no events.

Page 2 TechMentors Computer Training & Consulting Services © Copyright 2011 All rights reserved

www.TheTechMentors.com

408.375.6190