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))
VBA Wizard
Thursday, February 6, 2014
VBA - Return the RGB Value of a Cell's Font
This returns the RGB values of cell A1:
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
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
Subscribe to:
Posts (Atom)