VBA - Suppress Alert Messages

Suppressing alert messages is useful when you want your MACRO to do things like delete a worksheet without user input. To turn off the message simply run:

Application.DisplayAlerts = False

Be sure to re-enable Alerts after you are done:

Application.DisplayAlerts = True

VBA - Find the number of days in a Month and Return the number of a Month

Two simple functions for finding the number of days in a given month name and the numerical order that month comes in:

Function ReturnMonthNumber(ourMonth) As Integer
    ReturnMonthNumber = Month(DateValue("01 " & ourMonth & " 2000"))
End Function

Function NB_DAYS(ourMonth)
    NB_DAYS = Day(DateSerial(Year(DateValue("01 " & ourMonth & " 2000")), Month(DateValue("01 " & ourMonth & " 2000")) + 1, 1) - 1)
End Function

Sub TestFunctions()
    MsgBox ("The number of days in May is: " & NB_DAYS("May"))
    MsgBox ("April is the " & ReturnMonthNumber("April") & " month")
End Sub

VBA - Loop through all elements of an Array

Simple code to loop through the values of an Array:

Sub LoopArray()
    MyArray = Array("January", "February", "March", "April", "May", _
        "June", "July", "August", "September", "October", _
        "November", "December")
    
    For x = LBound(MyArray) To UBound(MyArray)
        MsgBox ("On element " & x & " which has a value of " & MyArray(x))
    Next
End Sub

VBA - Copy all Visible Cells in a Range

Quick snippet to copy all visible cells in a given range. Useful when filtering data and wanting to only move the visible data:

myrange = "A1:B2"

Range(myrange).SpecialCells(xlCellTypeVisible).Copy

VBA - Opening another workbook via a prompt

A snipet of code I use to have a user open files I want my MACRO to get data out of:

vFile = Application.GetOpenFilename("Excel-files,*", _
        1, "Select the file you want to open", , False)
'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile, UpdateLinks:=xlUpdateLinksAlways