Excel

Every once and a while I do excel consulting with my friend, Victor. And every time, bar none, it’s a huge pain in the ass cuz it’s usually time sensitive and I’m rusty. I always typically know what I need to do, but remembering how to do it is painful. So I’m going to attempt to remember some critical functions that will be used time and time again. Take the pressure off finding and implementing the functions from google. A Google search takes 2-3 minutes, maybe longer. Times that by about 80-100 and I’m simply wasting hours on google instead of pumping out code. I think in the last project I wrote one function specific to the project and the rest were helper functions in boiler plate blah blah blah.

Starting

Add Option Explicit to the top of the excel workbook. Helps with undefined variables and other crap that developers do

Debugging

Immediate Window The immediate window shows anything you Debug.Print. Turn on the Immediate window by View -> Immediate Window

Debugging Functions

Debug.Print("Ah shit")
MsgBox("This will be incredibly enjoyable!!!! NOT!!")

General Knowledge

Option Explicit

Option Explicit

Add it to the top of every module. No idea why, just do it.

Running a Macro from within a Macro

Two options…

Application.Run "OnTrack.xlsm!macroToRun"

or

Call macroToRun

The second version is shorter and more common. But I use the Application.Run if I’m referencing a macro from another workbook.

Returning a value from a function… To get a function to return a result from a function, you need to assign the function name to the result. See isMac below for example

Optional Parameters in Function

Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
End Function

If Statement

If isMac() = True Then

End If

Today’s Date in VBA

Date()
' => 4/21/2016

Private Constants These are good for keeping track of constants in a Module.

Private Const DBSHEET As String = "DB"
Private Const TS_ROW_COUNT As Long = 24

Using a Private Constant with a Range

Private Const DB_SHEET As String = "DB"
Private Const DB_HEADERS As String = "A1:J1"
Private Const DEST_SHEET As String = "Derp"
Private Const DEST_RANGE As String = "A1:J1"
Sheets(DEST_SHEET).Range(DEST_RANGE) = Sheets(DB_SHEET).Range(DB_HEADERS).Value

Using a NamedRange in VBA

FACK THAT JUST HARDCODE IN THE CELL REFERENCE.
    or... Worksheets("Sheet1").Range("Account").Interior.Color = vbYellow

Error Handling

On Error Resume Next

Opens up a block of code, where if there is an error it will resume next. This will continue indefinitely until you close the block.

On Error GoTo 0

On Error GoTo 0 tells excel to put error handling back to normal.

Working with Worksheets

When worksheets change names, that can mess stuff up if you’ve hard coded names of worksheets. Thats why it is important to work with the only constant that matters, the index.

' gets the index
Debug.Print(Sheets("Sheet2").Index)

dim ws as Worksheet
Set ws = Sheets(1)

Autofilter Basics

Set Autofilter

Range("A1").select
Selection.Autofilter

Filter By Dates 14 days ago and earlier

Selection.Autofilter Field:=4, Criteria1:="<" & Date() - 14

Common Functions that I will use over and Over again…

Open and Close Workbook

Dim wb as Workbook
wb = Workbooks.Open (s_file_absolute_path)
wb.Close SaveChange:=False

Setting a Workbook Object

Dim wb as Workbook
Set wb = Workbooks("OnTrack.xlsm")
wb.Save

The critical part is Set. This lingo must be used for objects, Workbooks, Worksheets, etc.

Finding the next blank row

' I know this can be better...
Function nextBlankRow() As Long
  nextBlankRow = Sheets("Sheet Name").Cells(Rows.Count, "A").End(xlUp).Row + 1
End Function

Delete Rows

Sub deleteRows(startingRow As String, endingRow As String)
  ' Purpose: Delete Rows on the DB sheet
  ' Created 02-06-2016

  ThisWorkbook.Sheets("Sheet name").Range(startingRow & ":" & endingRow).Delete xlUp

End Sub

Copy and Paste

Sub notBestPracticeCopy()
  Windows("my_window.xls").Activate
  Sheets("mySheet").Select
  Range("A1:A200").Select
  Selection.Copy
End Sub

Sub notBestPracticePaste()
  'Paste
  Call ThisWorkbook.Worksheets(yourWorksheet).Range(NamedRange).PasteSpecial(xlPasteValues)
End Sub

Sub BetterPracticeCopyAndPaste()
  Range("G6:G9") = Range("H6:H9").Value
End Sub

Sub OtherWayToCopyAndPaste()
  dim destinationWs As Worksheet
  dim dataWs as Worksheet

  Set dataWs = Sheets("raw" & worksheetName)
  Set destinationWs = Sheets("sorting" & worksheetName)

  Dim lColumn As Long
  Dim lRow As Long
  Dim dataRange As String

  lRow = dataWs.Cells(Rows.Count, "A").End(xlUp).Row
  lColumn = dataWs.Cells(1, Columns.Count).End(xlToLeft).Column

  With dataWs
    .Range(.Cells(1, 1), _
    .Cells(lRow, lColumn)).Copy
  End With

  With destinationWs
    .Range(.Cells(1, 1), _
    .Cells(lRow, lColumn)).PasteSpecial xlValues
  End With

  With destinationWs
    .Range(.Cells(1, 1), _
    .Cells(lRow, lColumn)).PasteSpecial xlFormats
  End With

End Sub

Operating System Checker

Function isMac() As Boolean
' Purpose: Returns True if current operating system is Mac, False for Windows
' Created 02-05-2016
' Updates

  ' Need to assign the function's name to the result for it to return anything. Please give me that hour of my life back excel.
  isMac = CBool(LCase(Left(Application.OperatingSystem, 3)) = "mac")

End Function

Find

Function Find() as Range
  Dim rFound as Range
  Dim ws as Worksheet

  ws = ActiveWorkbook.ActiveSheet

  With ws
    Set rFound = .Columns(2).Find(What:="What I want to Find", After:=.Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
  End With

  On Error GoTo 0
    If rFound Is Nothing Then
      ' do nothing, error
    Else
      Find = rFound
    End If
End Function

Worksheets: Add, Delete, Check if exists

Function addNewSheet() As Worksheet
  ' Purpose: Adds new Worksheet, and returns it as a result of the function so that it can be assigned to a variable
  ' Created 02-06-2016

  Set addNewSheet = Sheets.Add

End Function

Function deleteWorksheet(wsName As String)
  ' Purpose: Deletes a Worksheet with the Worksheet name you specify.

  Application.DisplayAlerts = False
  Sheets(wsName).Delete
  Application.DisplayAlerts = True

End Function

Sub examplesAddingAndDeletingWorksheet()
  Dim ws As Worksheet
  Set ws = addNewSheet()
  MsgBox (ws.Name)
  deleteWorksheet (ws.Name)
End Sub

Check is worksheet exists

Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
' Purpose: Check if Worksheet already exists

   Dim sht As Worksheet

   If wb Is Nothing Then Set wb = ThisWorkbook
   On Error Resume Next
   Set sht = wb.Sheets(shtName)
   On Error GoTo 0
   SheetExists = Not sht Is Nothing
 End Function

Multi File Selector Open Dialog Box Mac 2011 and 2016

  Sub macWorkbookSelector()
    ' Purpose: Allows user to select multiple workbooks

      Dim MyPath As String
      Dim MyScript As String
      Dim MyFiles As String
      Dim MySplit As Variant
      Dim N As Long
      Dim Fname As String
      Dim mybook As Workbook
      Dim rawWorkbookPath As String
      Dim workbookPath As String

      On Error Resume Next
      MyPath = MacScript("return (path to documents folder) as String")
      'Or use MyPath = "Macintosh HD:Users:Ron:Desktop:TestFolder:"

      MyScript = _
      "set applescript's text item delimiters to "","" " & vbNewLine & _
         "set theFiles to (choose file of type " & _
           " {""xlsx"",""public.comma-separated-values-text""} " & _
         "with prompt ""Please select a file or files"" default location alias """ & _
         MyPath & """ multiple selections allowed true) as string" & vbNewLine & _
         "set applescript's text item delimiters to """" " & vbNewLine & _
         "return theFiles"


      MyFiles = MacScript(MyScript)
      On Error GoTo 0

      If MyFiles <> "" Then
      With Application
          .ScreenUpdating = False
          .EnableEvents = False
      End With

      MySplit = Split(MyFiles, ",")
      For N = LBound(MySplit) To UBound(MySplit)

          Set mybook = Nothing
          On Error Resume Next

           ' Pass the opening, copying and closing to a function that can be shared by both windows and macs for Ultimate Dryness
           'Call openWbCopyDataCloseWb(workbookPath)
           Dim startingWb As Workbook
           Set startingWb = ActiveWorkbook
           Dim openedWb As Workbook

          ' Open Workbook
          If Application.Version = 14 Then
             Set openedWb = Workbooks.Open(MySplit(N))
          Else
            ' Mac 2016 opens workbooks based upon the slashes, not the colons, so replace the colons with slashes
            rawWorkbookPath = Replace(MySplit(N), ":", "/")

            ' The workbook paths also came with 'Macintosh HD' at the beginning, so remove that to get the absolute path
             workbookPath = Right(rawWorkbookPath, Len(rawWorkbookPath) + 1 - InStr(rawWorkbookPath, "/"))

             Set openedWb = Workbooks.Open(workbookPath)
          End If

    ' Do your thing 

    ' Close Timesheet Workbook
    openedWb.Close SaveChanges:=False



    ' Open Workbook
    'Workbooks.Open (s_file_absolute_path)
           'Workbooks.Open (MySplit(N)) -> works to open workbook
           'MsgBox (MySplit(N))
      Next N
      With Application
          .ScreenUpdating = True
          .EnableEvents = True
      End With
      End If
  End Sub

File Selector Windows

Sub windowsWorkbookSelector()
' Purpose: Returns an Array of Workbooks which the User has selected from an OpenDialog Box.
' openMultipleWorkbooks Macro
' Uses the Open Dialog to open Multiple Workbooks.
'
  Dim fd As FileDialog

  Dim fileName As String
  Dim i As Integer
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
      fd.InitialView = msoFileDialogViewList
      'set initial folder
      'fd.InitialFileName = "c:\dropbox\"
      'allow multiple file selection
      fd.AllowMultiSelect = True


  If fd.Show = -1 Then 'OK button clicked and File Selected
            'loop through each chosen file
            For i = 1 To fd.SelectedItems.Count


  Dim startingWb As Workbook
  Dim openedWb As Workbook

  Set startingWb = ActiveWorkbook

  ' Open Workbook
  Set openedWb = Workbooks.Open(fd.SelectedItems(i))

  ' Do your thing

  ' Close Timesheet Workbook
  openedWb.Close SaveChanges:=False


            Next i
  Else ' Cancel button clicked
            Exit Sub
  End If
End Sub

Post Content