Excel With VBA

Excel VBA Tutorial 1

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:

select cell macro 1

When you add a sub procedure to your worksheet, the box won’t appear blank anymore:

select cell macro 2

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]

admin