The main thing to remember when you are trying to figure out how to write a program is to think logically, and draw the flow of events on paper. Your goal is to take your thought process and translate it to Excel VBA code.
Each video in the Excel VBA tutorial will highlight one of VBA’s many benefits, which are:
– automating frequently used actions.
– eliminating repetitive keystokes in muliple areas of your worksheet.
– making a series of commands process as a unit by “replaying” those commands.
– use the features of Excel VBA to generate custome buttons, user forms, and menus.
– ability to generate custom applications in Excel containing the procedures and tools you have generated.
Here are some important definitions:
Macro – A set of instructions to tell Excel to carry out.
Procedure – A set of sub procedure or function VBA statements functioning as a unit.
Sub Procedure – A sub routine (set of statements), which are part of a bigger module.
Function Procedure – A set of statements working together to return a value.
Code – VBA statements which are necessary to accomplish tasks.
Statement – VBA code containing variables, arguments, and operators.
Object – Items like a worksheet or range, you can control in your VBA code.
Method – An action an object can perform like copying, pasting, or calculating, etc.
Property – Things that describe an object like color and size.
Modules – A collection of sub procedures. These can be worksheet specific, or workbook specific.
Running Macros
Here is an image of what a blank macro box looks like:
When you add a sub procedure to your worksheet, the box won’t appear blank anymore:
Trick: An easy way to learn how to program with Excel VBA is to use the record utility in Excel, and then analyze the VBA code steps used to record the macro.
Here is the code stored in “Module1” produced by the macro recorder:
Sub FormatMacro() ' ' FormatMacro Macro ' Macro recorded 10/21/2012 by Erik Loebl ' ' Range("A1:D1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Selection.Font.Bold = True Selection.Font.Italic = True With Selection.Font .Name = "Arial" .Size = 20 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.Copy Range("B2").Select Application.CutCopyMode = False Selection.Copy Range("A2").Select ActiveSheet.PasteSpecial Format:=4, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False Range("A1:D1").Select Selection.Copy Range("A2").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("A5:D5").Select Selection.Font.Bold = True Range("B6:D8").Select Range("D6").Activate Selection.Style = "Currency" Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)" Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)" Range("D12").Select ActiveWorkbook.Save Sheets("Sheet2").Select Range("A3").Select End Sub
Let me know if you have any questions
[simple_contact_form]