Thursday, February 6, 2014

VBA - Return the RGB Value of a Cell's Font

This returns the RGB values of cell A1:
sFontColor = Hex(Range("A1").Font.Color)
sFontColor = "000000" & sFontColor
sFontColor = Right(sFontColor, 6)
R = CInt("&H" & Right(sFontColor, 2))
G = CInt("&H" & Mid(sFontColor, 3, 2))
B = CInt("&H" & Left(sFontColor, 2))

Wednesday, January 22, 2014

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

Thursday, January 16, 2014

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

Wednesday, January 15, 2014

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

Friday, December 27, 2013

VBA - Determine if worksheet of a given name exists

The following function takes a worksheet name as input and returns True/False whether or not a worksheet of that name exists:


Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean

    On Error Resume Next
        WorksheetExists = (Sheets(WorksheetName).Name <> "")
    On Error GoTo 0

End Function