# [ Excel Automation (VBA) ] ( CheatSheet )

1. Basic Workbook and Worksheet Operations

● Open a Workbook: Workbooks.Open "C:\Path\to\file.xlsx"

● Create a New Workbook: Workbooks.Add
● Save a Workbook: ActiveWorkbook.Save
● Close a Workbook: ActiveWorkbook.Close
● Add a New Worksheet: Sheets.Add After:=ActiveSheet
● Delete a Worksheet: Sheets("SheetName").Delete
● Rename a Worksheet: Sheets("SheetName").Name = "NewName"
● Copy a Worksheet: Sheets("SheetName").Copy
After:=Sheets(Sheets.Count)
● Move a Worksheet: Sheets("SheetName").Move
After:=Sheets(Sheets.Count)
● Activate a Worksheet: Sheets("SheetName").Activate

2. Data Selection and Range Operations

● Select a Range of Cells: Range("A1:B10").Select

● Write to a Cell: Range("A1").Value = "Hello"
● Read from a Cell: Dim val As Variant; val = Range("A1").Value
● Clear a Range: Range("A1:B10").Clear
● Copy a Range: Range("A1:B10").Copy Destination:=Range("C1")
● Paste a Range: Range("C1").PasteSpecial Paste:=xlPasteValues
● Find in Range: Range("A1:B10").Find(What:="Value")
● Filter a Range: Range("A1:B10").AutoFilter Field:=1,
Criteria1:="FilterValue"
● Sort a Range: Range("A1:B10").Sort Key1:=Range("A1"),
Order1:=xlAscending
● Merge Cells: Range("A1:B2").Merge
● Unmerge Cells: Range("A1:B2").UnMerge

3. Formatting Cells and Ranges

● Set Font Name: Range("A1").Font.Name = "Calibri"

● Set Font Size: Range("A1").Font.Size = 12
By: Waleed Mousa
 ● Bold Text: Range("A1").Font.Bold = True
● Italicize Text: Range("A1").Font.Italic = True
● Underline Text: Range("A1").Font.Underline = True
● Set Text Color: Range("A1").Font.Color = RGB(255, 0, 0)
● Set Cell Background: Range("A1").Interior.Color = RGB(0, 255, 0)
● Set Cell Borders: Range("A1").Borders.LineStyle = xlContinuous
● Set Number Format: Range("A1").NumberFormat = "0.00"
● AutoFit Columns: Range("A1").EntireColumn.AutoFit

4. Working with Formulas and Functions

● Insert a Formula: Range("A10").Formula = "=SUM(A1:A9)"

● Calculate a Range: Range("A1:A9").Calculate
● Insert an Array Formula: Range("A10:B10").FormulaArray =
"=SUM(A1:A9)"
● Replace Formulas with Values: Range("A1").Value = Range("A1").Value
● Sum a Range: Application.WorksheetFunction.Sum(Range("A1:A9"))
● VLOOKUP in VBA: Application.WorksheetFunction.VLookup(val,
Range("A1:B10"), 2, False)

5. Charts and Graphs

● Create a Chart: Charts.Add; ActiveChart.SetSourceData

Source:=Range("Sheet1!A1:B10")
● Change Chart Type: ActiveChart.ChartType = xlLine
● Add Title to Chart: ActiveChart.HasTitle = True;
ActiveChart.ChartTitle.Text = "My Chart"
● Move Chart to New Sheet: ActiveChart.Location
Where:=xlLocationAsNewSheet, Name:="NewChart"
● Delete a Chart: Charts("ChartName").Delete

6. Pivot Tables

● Create a PivotTable: Set pvtTable =

ActiveWorkbook.PivotTables.Add(PivotCache:=ActiveWorkbook.PivotCach
es.Create(SourceType:=xlDatabase, SourceData:=Range("A1:B10")),
TableDestination:=Range("D1"))

By: Waleed Mousa

 ● Add Pivot Field: pvtTable.PivotFields("Field").Orientation =
xlRowField
● Refresh PivotTable:
ActiveSheet.PivotTables("PivotTable1").RefreshTable
● Clear PivotTable:
ActiveSheet.PivotTables("PivotTable1").PivotCache.Clear

7. Advanced Data Analysis

● Create a Data Table: Range("A1").CurrentRegion.CreateNames

Top:=True, Left:=False, Bottom:=False, Right:=False
● Regression Analysis: Application.Run "ATPVBAEN.XLAM!Regress",
Range("y_Range"), Range("x_Range"), True, True, ,
"RegressionSheet", True, False, False, False, , , False
● Running Total: Range("B1").Formula = "=SUM($A$1:A1)"
● Conditional Sum:
Application.WorksheetFunction.SumIf(Range("A1:A10"), "Condition",
Range("B1:B10"))
● Descriptive Statistics:
Application.WorksheetFunction.Average(Range("A1:A10"))

8. Importing and Exporting Data

● Import Data from Text File: Workbooks.OpenText

Filename:="C:\Path\to\file.txt"
● Export Data to Text File: Range("A1:B10").Copy; Workbooks.Add;
ActiveSheet.Paste; ActiveWorkbook.SaveAs
Filename:="C:\Path\to\export.txt", FileFormat:=xlText
● Export Worksheet as CSV: ActiveWorkbook.SaveAs
Filename:="C:\Path\to\file.csv", FileFormat:=xlCSV
● Open XML File: Workbooks.OpenXML "C:\Path\to\file.xml"
● Import Data from Another Workbook: Workbooks.Open
Filename:="C:\Path\to\file.xlsx"

9. Working with External Applications

● Open Another Application: CreateObject("WScript.Shell").Run

"application.exe"

By: Waleed Mousa

 ● Send Email via Outlook:
CreateObject("Outlook.Application").CreateItem(0)
● Interact with Internet Explorer:
CreateObject("InternetExplorer.Application")
● Running Shell Commands: Shell("cmd /c dir", vbNormalFocus)
● Connect to Database: CreateObject("ADODB.Connection")

10. Error Handling and Debugging

● Basic Error Handling: On Error Resume Next; Err.Clear

● Raise an Error: Err.Raise vbObjectError + 1, "Example", "Custom
Error"
● Try-Catch Block: On Error GoTo ErrorHandler; 'code here; Exit Sub;
ErrorHandler: 'error handling code
● Toggle Breakpoint: Application.EnableEvents = False
● Debug Print: Debug.Print "Variable value: " & var

11. File and Directory Operations

● Check If File Exists: If Dir("C:\Path\to\file.xlsx") <> "" Then

● Delete a File: Kill "C:\Path\to\file.xlsx"
● Copy File: FileCopy "C:\Path\From\file.xlsx",
"C:\Path\To\file.xlsx"
● Move File: Name "C:\Path\From\file.xlsx" As "C:\Path\To\file.xlsx"
● Create New Directory: MkDir "C:\Path\To\NewFolder"

12. Working with Dates and Times

● Get Current Date and Time: Now

● Format Date and Time: Format(Now, "yyyy-mm-dd hh:mm:ss")
● Calculate Date Difference: DateDiff("d", Date1, Date2)
● Add Days to Date: DateAdd("d", 10, Date)
● Extract Parts from Date/Time: Year(Now), Month(Now), Day(Now)

13. User Interaction and Messaging

● Display a Message Box: MsgBox "Message text"

● Get User Input: InputBox("Enter value:")

By: Waleed Mousa

 ● Create a Custom Form: UserForm1.Show
● Display a Status Message: Application.StatusBar = "Processing..."
● Clear Status Bar: Application.StatusBar = False

14. Security and Protection

● Protect a Worksheet: ActiveSheet.Protect "password"

● Unprotect a Worksheet: ActiveSheet.Unprotect "password"
● Protect Workbook Structure: ActiveWorkbook.ProtectStructure = True
● Check if a Cell is Locked: Range("A1").Locked

15. Looping and Conditional Statements

● For Loop: For i = 1 To 10; Next i

● For Each Loop: For Each cell In Range("A1:A10"); Next cell
● Do While Loop: Do While condition; Loop
● If Statement: If condition Then; End If
● Select Case: Select Case variable; Case value1; Case Else; End
Select

16. Working with Arrays and Collections

● Declare an Array: Dim arr(1 To 10) As Integer

● Resize an Array: ReDim Preserve arr(1 To 20)
● Loop Through an Array: For i = LBound(arr) To UBound(arr)
● Use a Collection: Dim col As New Collection; col.Add item
● Iterate Through a Collection: For Each item In col; Next item

17. Functions and Subroutines

● Define a Subroutine: Sub MySub(); End Sub

● Define a Function: Function MyFunction() As Integer; End Function
● Call a Subroutine: Call MySub
● Use a Function: var = MyFunction()

18. Advanced Data Techniques

By: Waleed Mousa

 ● Linear Regression with LINEST:
Application.WorksheetFunction.LinEst(Range("Y"), Range("X"))
● Multivariate Regression:
Application.WorksheetFunction.LinEst(Range("Y"),
Application.WorksheetFunction.Power(Range("X"), Array(1, 2)))

19. Custom UI and Application Events

● Create a Custom Ribbon: CustomUI Editor Tool

● Handle Workbook Open Event: Private Sub Workbook_Open(); End Sub
● Trigger on Cell Change: Private Sub Worksheet_Change(ByVal Target
As Range); End Sub
● Add Custom Button to Toolbar: Application.CommandBars("Toolbar
Name").Controls.Add

20. Optimizing and Performance Tuning

● Turn Off Screen Updating: Application.ScreenUpdating = False

● Turn Off Automatic Calculations: Application.Calculation =
xlCalculationManual
● Optimize Access to Range: With Range("A1:A10000"); End With
● Reduce Flicker with Double Buffering: UserForm1.DoubleBuffer =
True

21. Integrating with Databases and Web Services

● Connect to SQL Database: CreateObject("ADODB.Connection")

● Execute SQL Query: connection.Execute("SELECT * FROM table")
● Fetch Data from Web Service: WinHttp.WinHttpRequest.5.1

22. Advanced User Forms and Controls

● Add Controls to a UserForm: UserForm1.Controls.Add("Forms.Label.1")

● Show or Hide a UserForm: UserForm1.Show, UserForm1.Hide
● Handle Control Events: Private Sub CommandButton1_Click(); End Sub
● Validate UserForm Data: If TextBox1.Text = "" Then MsgBox "Enter
data"

By: Waleed Mousa