taktran/vba_cheatsheet.md

## vba_cheatsheet.md

      
    Raw
  

              vba_cheatsheet.md
            
          
    Debugging

Console log
Debug.Print "Hello"

Message box
MsgBox "Hello

Operators


Not equals: <>


Concatenate: &


Not: Not


And: And


Boolean


If statement

If condition [ Then ]  
    [ statements ]  
[ ElseIf elseifcondition [ Then ]  
    [ elseifstatements ] ]  
[ Else  
    [ elsestatements ] ]  
End If  

Declare variables

Dim wSheet As Worksheet
Dim curTeam As String
Dim curRow As Long
Dim EMPTY_ROW_CHECK_START_COL As String: EMPTY_ROW_CHECK_START_COL = "J"

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/dim-statement
Arrays

Collection


coll.Count


For Each:
For Each team In teams
Next team


Loops

Do while
Do While curTeam <> ""
    Debug.Print curTeam

    curRow = curRow + 1
    curTeam = wSheet.Range("A" & curRow).Value
Loop

For loop
Dim i As Integer
For i = 1 To maxRowsToCheck
    ' Can also exit with
    Exit For
Next i

Cells

.Cell
Subroutines/Functions

Sub SelectTeam()

End Sub

Function GetNumTeams() As Integer

End Function

Workbooks

Set focus
Workbooks("BOOK4.XLS").Activate

Close and save changes
Workbooks("BOOK1.XLS").Close SaveChanges:=True

Get current path
ActiveWorkbook.Path & Application.PathSeparator & fileName & ".xlsx"

Copy and paste to Worksheet
firstSheet = "Sheet1"
thisWorkbook.Worksheets(SUMMARY_WORKSHEET_NAME).Range("A1:Q1000").Copy
With teamTemplate
    .title = fileName
    .Worksheets(firstSheet).Paste
    .SaveAs fileName:=filePath, FileFormat:=xlOpenXMLWorkbook, AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
    .Close
End With

Delete entire row
ActiveWorkbook.Worksheets(summaryWorksheetName).Rows(row).EntireRow.Delete

Application

Don't show error messages
Application.DisplayAlerts = False

Troubleshooting

Argument not optional

When using ByRef with collections
https://stackoverflow.com/questions/31321816/vba-argument-not-optional
Rather than have CopyWorksheets (teams), have CopyWorksheets teams