Excel VBA(Redirected from Microsoft Excel Programming Recipes)
Microsoft Excel is a deep program rich in features and functionality. One of the most powerful features of Excel is the ability to write programs in Visual Basic for Applications that run "behind" your worksheets to turn Excel into a calculation-oriented development platform for creating special purpose spreadsheets which can function as applications in their own right.
- 1 Service Pack
- 2 Macro Security
- 3 Macro Recording
- 4 Making an XLA
- 5 Accessing the Registry
- 6 Prevent Confirmation Popups In Excel
- 7 Making Cells Read-Only
- 8 Finding Non-Empty Portion of Worksheet
- 9 Using Events
- 10 Caveat: Uncaught Exceptions
- 11 Caveat: Online-Help Typos
- 12 Iterating Over MultiSelected Cells
- 13 Exporting VBA Code
- 14 Resize a Named Range
- 15 Creating a Named Range
- 16 Reading a File
- 17 Writing to a File
- 18 File Existence
- 19 Directories
- 20 Grep
- 21 Clipboard
- 22 Range
- 23 Worksheet
- 24 Searching
- 25 Cell Formatting
- 26 Color
- 27 Visibility
- 28 Hyperlink
- 29 Temporary file
- 30 Command Output
- 31 Dimensions
- 32 Comment
- 33 Add-Ins at Startup
- 34 Chart direct from VBA array data
- 35 Nuking Retained State
- 36 Related Wikibooks
- 37 External links
A service pack (in short SP) is a collection of updates, fixes or enhancements to a software program delivered in the form of a single installable package.
A service pack can be incremental, which means it only contains the updates that were not present in the previous service packs or, it can be cumulative, which means it includes the contents of all its predecessors. In the case of Microsoft's products, an incremental update was called a service release. For example, Office 2000 must be upgraded to service release 1 (SR-1) before one can install SP2.
Macro security settings are located in the Trust Center. However, if you work in an organization, your system administrator might have changed the default settings to prevent anyone from changing any settings and execute macros.
A great way of learning about Excel VBA is using its macro recording function. With the function, you tell Excel to start recording, then perform various steps as if you were working without a macro recorder, and finally, tell Excel to stop recording. VBA code corresponding to what you did using Excel GUI has been recorded by Excel. While the code often cannot be meaningfully used without a modification, by starting from it and modifying it you can save a lot of time that would otherwise be spent reading the VBA documentation.
- Excel 2000, 2003: Tools > Macro > Record New Macro.
- Excel 2007: View (tab) > Macros (group) > down-pointing triangle below Macros button > Record Macro
- Excel 2007: Developer (tab) > Code (group) > Record Macro
- Use Microsoft Office Excel 2007 macros to speed up work at microsoft.com
- Create a macro (Excel 2003) at microsoft.com
- Record and use Excel macros (Excel 2000) at microsoft.com
Making an XLAEdit
An XLA is one way to make a VBA code-library. It's basically just a normal spreadsheet(.xls file) but has its worksheets hidden. Here's how to create a new one:
- New Workbook
- Save-As ...Name it whatever
- Hit Alt-F11
- In the Project-tree, select VBAProject(whatever.xls)/ThisWorkbook
- Hit F4 to get Properties View
- Find Property IsAddin and set to True
- Hit Save
- Close Excel
- Rename whatever.xls to whatever.xla
Alternatively, you can use Save As / Excel AddIn.
Accessing the RegistryEdit
- Works on: Microsoft Excel 2002 SP-2
This recipe is for reading/writing keys local to your application -- this is to provide persistent settings for your VBA app. It does not cover arbitrary access to the Registry (i.e. looking at any key).
The VBA sub/functions are
GetSetting. You can type the following into the Immediate window to get a feel for how these work:
SaveSetting "MyApplicationName", "MyConfigSection", "MyKeyName", "Hello World" MsgBox GetSetting("MyApplicationName", "MyConfigSection", "MyKeyName")
If you want to iterate over all keys in a given section you can do so as follows:
Sub ShowAllKeys() Dim mySettings As Variant mySettings = GetAllSettings("MyApplicationName", "MyConfigSection") If Not IsEmpty(MySettings) Then Dim counter As Integer For counter = LBound(mySettings) To UBound(mySettings) Dim keyname As String: keyname = mySettings(counter, 0) Dim keyval As String: keyval = mySettings(counter, 1) MsgBox keyname & "=" & keyval Next End If End Sub
You can also delete a registry key as follows:
DeleteSetting "MyApplicationName", "MyConfigSection", "MyKeyName"
FYI: Excel/VBA sticks this in the following registry location:
MyComputer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MyApplicationName\MyConfigSection
MyConfigSection are whatever you specified in your
They end up in HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MyApplicationName\MyConfigSection.
Prevent Confirmation Popups In ExcelEdit
- Works on: Microsoft Excel 2002 SP-2
Make the following call from VBA:
Application.DisplayAlerts = False
Making Cells Read-OnlyEdit
- Works on: Microsoft Excel 2002 SP-2
Sub ProtectMe() Range("A1:IV65536").Locked = False Range("A1").Locked = True ActiveSheet.Protect Password:="Test" End Sub
Finding Non-Empty Portion of WorksheetEdit
A Worksheet has a maximum size of 65536 rows by 256 columns. However if you want to iterate over all cells you probably don't want to visit all of the empty ones. For this purpose the Worksheet provides the UsedRange property. For example:
tells you how many non-empty rows are in the given worksheet. Empty rows which are inbetween the first and last used row are counted as well. Example: If a given Worksheet has entries in cells A7 and B16, used range is considered to be A7:B16, which counts for a total of 10 rows.
- Works on: Microsoft Excel 2002 SP-2
Consider the following class definition -- Assume its a class called CMyClass:
Option Explicit Dim WithEvents mySheet As Worksheet Public Sub Init(aWS as Worksheet) Set MySheet = aWS End Sub Private Sub mySheet_SelectionChange(ByVal Target As Range) Dim MyRange As Range For Each MyRange in Target Debug.Print CStr(MyRange) Next End Sub
The main ideas here are:
- By declaring mySheet WithEvents, you're saying that CMyClass is listening to mySheet's events.
- By declaring the member sub mySheet_SelectionChange you're indicating how an instance of CMyClass should react if mySheet experiences a selection change (i.e. the user selects a new cell or range of cells); The general pattern for events is sub memberVarName_EventName(params).
- You can disconnect the eventing between the given worksheet and CMyClass by setting mySheet = nothing;
- You can create classes that throw events of your design using:
- You would declare at the top of the class: Public Event SomeNiceEventName(YourFavoriteParam1 as WhateverType, etc...),
- You could then raise that event (i.e. firing it to whatever listeners your class has) using RaiseEvent SomeNiceEvent("Some nice event happened.");
- VBA in Excel doesn't like the letters r or c used as variables. Those letters mean 'row' and 'column' elsewhere.
A little more detail is given here: []
Caveat: Uncaught ExceptionsEdit
Caveat: Uncaught exceptions in event-handlers cause VBE to mysteriously reset. If you are causing an uncaught exception in an event-handler, you probably won't get an error popup. Instead, VBE will just reset. For this reason you should make sure you're catching exceptions in all of your event handlers.
Caveat: Online-Help TyposEdit
Some versions of Excel can have typos in the F1-help. Here's an example of a Click handler with the correct parameters:
Private Sub clicksrc_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean) MsgBox "I got a click!" End Sub
Iterating Over MultiSelected CellsEdit
- Works on: Microsoft Excel 2002 SP-2
The following code-snippet writes "YAY!" in each cell selected by the user:
For Each Cell in Selection Cell.Value = "YAY!" Next
Exporting VBA CodeEdit
- Works on Microsoft Excel 2002 SP-2
The following code provides a very primitive routine to write serializes the VBA code in your modules to files:
Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim objModule As Object On Error Resume Next For Each objModule In ThisWorkbook.VBProject.VBComponents DoEvents If objModule.CodeModule.CountOfLines > 0 Then objModule.Export objModule.Name & ".bas" End If Next objModule End Sub
Resize a Named RangeEdit
- Excel 2003
Note that the Resize property of Range objects does not change the size of the range object. It returns a new anonymous Range object. Easiest way to do this is to set the .Name property of the Resized range:
Sub ResizeRange(ByVal RangeName As String, _ Optional NewRowCount As Long = 0, _ Optional NewColumnCount As Long = 0) Dim oRange As Range Set oRange = Range(RangeName) If NewRowCount = 0 Then NewRowCount = oRange.Rows.Count End If If NewColumnCount = 0 Then NewColumnCount = oRange.Columns.Count End If oRange.Resize(NewRowCount, NewColumnCount).Name = RangeName End Sub
Creating a Named RangeEdit
- Excel 2002
Named Ranges allow the user to refer to a cell or range of cells by name instead of by their cell address. This name can be used in other cell formulas as well as in VBA (e.g. using the
]). There are two sorts of named ranges: Workbook Names and Worksheet Names.
To create a Workbook Name you can select the cell you want to name, pull down Insert-->Name-->Define... This will bring up the "Define Name" dialog. Here you can enter the new name for your cell.
The create a Worksheet Name you follow the same procedure but precede the name with Sheetname
Sheet1!InitialValue to create a named-range only visible from within worksheet
When there are two variables with the same name, one local (Worksheet Name) and one global (Workbook Name), the spreadsheet uses the local variable.
There is no way to visualize a named range. The closest thing is to pull down again Insert-->Name-->Define..., but this method does not show if a variable is a local Worksheet Name or a global Workbook Name.
The named range can be a single cell, part of a row, part of a column or a rectangular group of cells. Each one behaves differently:
- A single cell can be referenced anywhere in the worksheet or, if it's defined globally (workbook name), anywhere in any worksheet.
- A group of cells composing part of a row can only be referenced in a parallel row. For example, if the named variable is mass and it spans the cells C5:L5, then a reference to mass' at cell E8 (say, a formula like =mass * (3e8)^2) would take the value at C8, but a reference to mass at cell M9 would return an error
- Similarly, a group of cells composing part of a column can only be referenced in a parallel column. Cells outside the range will return an error
- A group of cells that define a rectangular array of sides greater than one is only useful to be referenced in other worksheets - so, there's no point in defining them locally (worksheet name). For example, if covmatrix are the cells Sheet1!B2:D4, then if cell Sheet2!C3 has the formula =1/sqrt(covmatrix), then it will return 1/sqrt(Sheet1!C3).
Reading a FileEdit
Reading a file line by line AKA for each line of a file:
MyFileName = "C:\Users\Joe\Desktop\File.txt" FileNo = FreeFile() Open MyFileName For Input As #FileNo While Not EOF(FileNo) Line Input #FileNo, MyLine MsgBox MyLine Wend Close #FileNo
Writing to a FileEdit
Writing to a file:
MyFileName = "C:\Users\Joe\Desktop\File.txt" FileNo = FreeFile() Open MyFileName For Output As #FileNo For I = 1 To 10 Print #FileNo, Str(I); ' The semicolon above prevents printing of a newline Next Close #FileNo
Writing the tab-separated content of the current worksheet to a text file, disregarding some of cell content formatting such as percentages:
MyFileName = "C:\Users\Joe\Desktop\File.txt" FileNo = FreeFile() Open MyFileName For Output As #FileNo RowCount = ActiveSheet.UsedRange.Cells.Rows.Count ColumnCount = ActiveSheet.UsedRange.Cells.Columns.Count For RowNo = 1 To RowCount For ColNo = 1 To ColumnCount Print #FileNo, Cells(RowNo, ColNo); ' The semicolon bars newline printing If ColNo < ColumnCount Then Print #FileNo, vbTab; End If Next If RowNo < RowCount Then Print #FileNo, vbNewline; End If Next Close #FileNo
Testing whether a file exists:
If Dir(MyFileName) <> "" Then MsgBox "The file exists." End If
Making a directory:
Removing a directory:
Changing to a directory:
Changing the current drive:
Listing the content of a directory, using a custom filter encompassing two file extensions:
Directory = "C:\Users\Joe Hoe\" Set Files = New Collection Set FileFullPaths = New Collection MyFile = Dir(Directory) While MyFile <> "" Extension = LCase(Right(MyFile, 4)) If Extension = ".txt" Or Extension = ".bat" Then Files.Add MyFile FileFullPaths.Add Directory & MyFile End If MyFile = Dir() 'Next file or folder Wend
- ChDir at Visual Basic for Applications Reference, msdn.microsoft.com
- ChDrive at Visual Basic for Applications Reference, msdn.microsoft.com
- Dir at Visual Basic for Applications Reference, msdn.microsoft.com
- MkDir at Visual Basic for Applications Reference, msdn.microsoft.com
- RmDir at Visual Basic for Applications Reference, msdn.microsoft.com
Searching for a regular expression in the lines of the files of a directory aka grepping:
Directory = "C:\Users\Joe Hoe\" PatternString = "target.*path" MyFile = Dir(Directory) Set Lines = New Collection Set RegExp = CreateObject("VBScript.RegExp") RegExp.Pattern = PatternString RegExp.IgnoreCase = True While MyFile <> "" Extension = LCase(Right(MyFile, 4)) If Extension = ".txt" Or Extension = ".bat" Then MyFullFileName = Directory & MyFile FileNo = FreeFile() Open MyFullFileName For Input As #FileNo While Not EOF(FileNo) Line Input #FileNo, MyLine If RegExp.Test(MyLine) Then Lines.Add MyLine End If Wend Close #FileNo End If MyFile = Dir() 'Next file or folder Wend 'Lines is a collection of the matching lines
Prerequisites: Accessing the clipboard from an Excel sheet requires that a reference to MSForms (Microsoft Forms Object Library) is set in the sheet. You can set the reference by adding and subsequent removing of a user form, via Insert > UserForm in a pop-up menu. To check the presence of a reference, see Tools > References menu.
Placing text on the clipboard:
Set MyClipboard = New MSForms.DataObject MyClipboard.SetText "My string" MyClipboard.PutInClipboard
Getting text from the clipboard:
Set MyClipboard = New MSForms.DataObject MyClipboard.GetFromClipboard TextContent = MyClipboard.GetText
- DataObject Class at msdn.microsoft.com; contains a section on Visual Basic, whose applicability to Excel VBA is unclear
A range is a set of cells. The cells in a range do not need to be adjacent. However, the cells in a single range do need to belong to a single worksheet.
Defining a new range:
Set MyRange = Selection 'The current selection, possibly of multiple cells Set MyRange = ActiveCell 'The single active cell Set MyRange = Cells(1, 2) 'Row=1, Column=2 AKA B Set MyRange = Cells(1, 1).Offset(0, 1) '=Cells(1, 2) Set MyRange = Cells(1, 2).Offset(0, -1) '=Cells(1, 1) Set MyRange = Cells(1, 1).Offset(0, -1) 'An error Set MyRange = Range("A1:C2") 'Letters indicate columns; numbers indicate rows Set MyRange = Range("A1:A3,C1:C3") 'A discontinuous range Set MyRange = Range("B2:C2").Cells(1, 1) '=Cells(2, 2) =Range("B2") Set MyRange = Rows(1) 'An entire row Set MyRange = Columns(1) 'An entire column Set MyRange = Cells(2,2).EntireRow Set MyRange = Cells(2,2).EntireColumn Set MyRange = Range("B1:C1").EntireColumn 'Two entire columns Set MyRange = Range("B2:D4").End(xlUp) '=Cells(1, 2) =Range("B1") Set MyRange = Range("B2:D4").End(xlToLeft) '=Cells(2, 1) = Range("A2") Set MyRange = Range("B2:D4").End(xlDown) '=Cells(<last row number>, 2) Set MyRange = Range("B2:D4").End(xlToRight) '=Cells(2, <last column number>)
Iterating a range AKA for each cell in a range:
Set MyRange = Selection For Each Cell in MyRange MsgBox Cell Next
Iterating rows and iterating columns AKA for each row of a range and for each column of a range, even if discontiguous:
Set Rng = Range("A1:B1,D3:E3") 'Discontiguous range For Each Row In Rng.Rows RowNo = Row.Row Next For Each Column In Rng.Columns ColNo = Column.Column Next
Making an union (a range subsuming both) or intersection (a range covering only cells in common) of two ranges:
Set MyRange = Range("A1:C2") Set MyRange = Union(MyRange, Range("A5:C5")) MyRange.Interior.Color = RGB(230, 230, 0) Set MyRange = Intersect(MyRange, Columns(2)) MyRange.Interior.Color = RGB(230, 100, 0)
Selecting a range:
Set MyRange = Sheets(1).Range("A1:B1,D1:E1") MyRange.Select 'Even a discontinuous range can be selected
Activating a cell:
Range("A1:B2").Select 'Affects Selection, generally of multiple cells Range("A2").Activate 'Affects ActiveCell, the single one
Finding out about a range AKA learning about a range, including the number of cells AKA cell count, the first row, the last row, the first column, the last column, row count, and column count:
Set Rng = Range("B2:D4") 'Contiguous range NumberOfCells = Rng.Cells.Count FirstRowNo = Rng.Row LastRowNo = Rng.Row + Rng.Rows.Count - 1 'Only for contiguous ranges FirstColNo = Rng.Column LastColNo = Rng.Column + Rng.Columns.Count - 1 'Only for contiguous ranges Set Rng = Range("A1:B1,D1:E1") 'Discontiguous range BrokenLastColNo = Rng.Column + Rng.Columns.Count - 1 'Only for contiguous ranges 'Do it the correct way for discontiguous range LastColNo = 0 For Each Cell in Rng If Cell.Column > LastColNo then LastColNo = Cell.Column End If Next Set RangeWorksheet = Rng.Worksheet
- Range Collection at Excel 2003 VBA Language Reference at msdn
- Referring to Multiple Ranges at Excel 2003 VBA Language Reference at msdn
- End Property at Excel 2003 VBA Language Reference at msdn
- Intersect Method at Excel 2003 VBA Language Reference at msdn
- Union Method at Excel 2003 VBA Language Reference at msdn
To create, access, or delete worksheets, you can use the methods of Worksheet objects. Examples follow.
Set MyNewWorksheet = Sheets.Add 'Create Set MyNewWorksheet2 = Sheets.Add(After:=Sheets(Sheets.Count)) 'Create and place as the last sheet MyNewWorksheet.Name = "My Sheet" Set IndexedWorksheet = Sheets(1) 'Access by index Set NamedWorksheet = Sheets("Name") 'Access by name Set NamedWorksheet2 = Worksheets("Name") 'Does the same thing as the line above MyNewWorksheet.Delete Sheets("Name").Cells(1,1) = "New Value" 'Access the cells of the worksheet Sheets("Name").Cells.Clear 'Clear an entire worksheet, including formatting and cell values Sheets("Name").Columns(1).Sort key1:=Sheets("Name").Range("A1") 'Sort the first column Sheets("Name").Columns(1).Sort key1:=Sheets("Name").Range("A1"), _ order1:=xlDescending, header:=xlYes 'Use descending instead of ascending; do not sort ' the first cell, considering it a header MyNewWorksheet2.Visible = xlSheetHidden MyNewWorksheet2.Visible = xlSheetVisible
Obtaining an existing sheet by name or creating it if it does not exist:
NewSheetName = "My Sheet" Set MySheet = Nothing On Error Resume Next Set MySheet = Sheets(NewSheetName) On Error GoTo 0 If MySheet Is Nothing Then Set MySheet = Sheets.Add(After:=Sheets(Sheets.Count)) MySheet.Name = NewSheetName End If
You can search for values in a sheet as follows:
Dim SoughtString As String SoughtString = "London" Set ForeignKeySheet = Sheets("CitySize") Set FoundCell = ForeignKeySheet.Columns(1).Find(SoughtString, LookAt:=xlWhole) If Not FoundCell Is Nothing Then 'The value associated with the key is in column 2 CitySize = FoundCell.Offset(0, 1) End If
If you want to have a substring match, drop "LookAt:=xlWhole" or use "LookAt:=xlPart".
You can format cells including text color, background color, font properties and border, but also formatting as a number, percent or text from VBA as follows:
Selection.Characters.Font.Color = RGB(0, 0, 255) 'Foreground color AKA text color Selection.Interior.Color = RGB(230, 230, 230) 'Background color Selection.Characters.Font.ColorIndex = xlAutomatic 'Reset foreground color Selection.Interior.Color = xlAutomatic 'Reset background color Selection.Font.Name = "Verdana" 'Font face Selection.Font.Size = 8 'Font size Selection.Font.Bold = True Selection.Font.Italic = True Selection.Font.Underline = True 'Selection.Font.Strikethrough = True Selection.Font.Name = Application.StandardFont 'See also ClearFormats below Selection.Font.Size = Application.StandardFontSize 'See also ClearFormats below 'Selection.Borders.LineStyle = xlLineStyleNone or xlDouble or xlDashDotDot or other Selection.Borders.Weight = xlMedium ' xlHairline, xlThin, xlMedium, or xlThick 'Selection.Borders(xlEdgeBottom).Weight = xlThick ' LineStyle and Weight interact in strange ways. Selection.Borders.Color = RGB(127, 127, 0) 'Will be overridden below; applies to all borders Selection.Borders(xlEdgeBottom).Color = RGB(255, 0, 0) Selection.Borders(xlEdgeTop).Color = RGB(0, 255, 0) Selection.Borders(xlEdgeLeft).Color = RGB(0, 0, 255) Selection.Borders(xlEdgeRight).Color = RGB(0, 127, 127) Selection.Borders(xlInsideHorizontal).Color = &H7FFF00 'A tricky hex matching RGB(0, 255, 127) Selection.Borders(xlInsideVertical).Color = RGB(255, 127, 0) Selection.NumberFormat = "General" Selection.NumberFormat = "00" 'As a number with zero decimal places, showing at least two digits Selection.NumberFormat = "0.000" 'As a number, showing three decimal places and no more Selection.NumberFormat = "0.0%" 'As a percent with one decimal place Selection.NumberFormat = "@" 'As text Selection.NumberFormat = "0.00E+00" 'As a number in scientific notation, 'the string before E formatting the significand Selection.NumberFormat = "m/d/yyyy" 'As a date; whether "/" is shown depends on locale Selection.NumberFormat = "d. mmmm yyyy hh:mm:ss" 'As date, showing the month using a word, 'also showing time Selection.ClearFormats 'Remove formatting, keeping cell content. 'Removes also the formatting set using NumberFormat.
- Font Object (Excel 2003 VBA Language Reference) at msdn
- Borders Collection (Excel 2003 VBA Language Reference) at msdn
- LineStyle Property (Excel 2003 VBA Language Reference) at msdn
- Weight Property (Excel 2003 VBA Language Reference) at msdn
- NumberFormat Property (Excel 2003 VBA Language Reference) at msdn
In Excel VBA, RGB colors are plain numbers rather than objects. Some color examples are listed in #Cell Formatting.
Selection.Characters.Font.Color = RGB(0, 0, 255) 'Foreground color AKA text color Selection.Interior.Color = RGB(230, 230, 230) 'Background color Selection.Characters.Font.ColorIndex = xlAutomatic 'Reset foreground color Selection.Comment.Shape.Fill.ForeColor.RGB = RGB(220, 255, 160) 'The above is the fill color, that is, the background color Selection.Comment.Shape.TextFrame.Characters.Font.ColorIndex = 3 'Red per default 'Selection.Comment.Shape.TextFrame.Characters.Font.Color = RGB(255, 0, 0) 'Does not work in Excel 2007 If False Then ActiveWorkbook.Colors(3) = RGB(200, 0, 0) 'Make the red in the index 5 a bit darker End If
Setting the background color of the cells of the 1st column to the row number color index:
For ColorIndex = 1 to 56 Cells(ColorIndex,1).Interior.ColorIndex = ColorIndex Next
Color index in the default palette:
- 0 - Automatic
- 1 - Black
- 2 - White
- 3 - Red
- 4 - Green
- 5 - Blue
- 6 - Yellow
- 7 - Magenta
- 8 - Cyan
- 9 - Dark red
- 10 - Dark green
- 11 - Dark blue
- ... etc., through 56
Finding all cells whose text color approaches green:
TargetColor = RGB(0, 255, 0) Tolerance = 200 'Extract the color components. The extraction is unneeded, but if the target 'color came from the color of a selected cell, it would be needed. TmpColor = TargetColor TargetColorRed = TmpColor Mod 256 TmpColor = TmpColor \ 256 TargetColorGreen = TmpColor Mod 256 TmpColor = TmpColor \ 256 TargetColorBlue = TmpColor Mod 256 For Each Cell In ActiveSheet.UsedRange.Cells MyColor = Cell.Characters.Font.Color 'Color is a number 'Extract the RGB components of the color Red = MyColor Mod 256 MyColor = MyColor \ 256 Green = MyColor Mod 256 MyColor = MyColor \ 256 Blue = MyColor Mod 256 'Find the distance from the target color Distance = ((Red - TargetColorRed) ^ 2 + _ (Green - TargetColorGreen) ^ 2 + _ (Blue - TargetColorBlue) ^ 2) ^ 0.5 If Distance < Tolerance Then Cell.Interior.Color = RGB(230, 230, 230) 'Mark the cell using its background color End If Next
- Color Property at Excel 2003 VBA Language Reference at msdn
- ColorIndex Property at Excel 2003 VBA Language Reference at msdn
- Colors Property at Excel 2003 VBA Language Reference at msdn
- ColorType Property at Excel 2003 VBA Language Reference at msdn
- PatternColor Property at Excel 2003 VBA Language Reference at msdn
- PatternColorIndex Property at Excel 2003 VBA Language Reference at msdn
Hiding a row (hide a row, hide row):
Rows(2).Hidden = True 'Rows(2).Hidden = False 'Show it again
Hiding several rows at once:
Range("A1:A3").EntireRow.Hidden = True 'Hide rows 1, 2, and 3
Hiding the rows of the currently selected cells:
Selection.EntireRow.Hidden = True
Looping over rows that are visible AKA shown AKA not hidden:
For RowNo = 1 To 10 If Not Rows(RowNo).Hidden Then 'Do something on the row End If Next
Toggling the visibility of rows:
For RowNo = 1 To 10 If Not Rows(RowNo).Hidden Then Rows(RowNo).Hidden = True Else Rows(RowNo).Hidden = False End If Next
Hiding a column (hide a column, hide column):
Columns(2).Hidden = True 'Columns(2).Hidden = False 'Show it again
Hiding several columns at once:
Range("A1:C1").EntireColumn.Hidden = True 'Hide columns 1, 2, and 3
Hiding the columns of the currently selected cells:
Selection.EntireColumn.Hidden = True
Other tricks pertaining to visibility of columns work in a direct analogy to the examples for rows above.
Opening or following a hyperlink (open a hyperlink, follow a hyperlink, open hyperlink, follow hyperlink):
Opening the Wikipedia article for the article title found in the single active cell by opening a put-together URL:
ActiveWorkbook.FollowHyperlink "http://en.wikipedia.org/wiki/" & ActiveCell
Opening a Wikipedia article per article title in any of the currently selected cells:
For Each Cell In Selection ActiveWorkbook.FollowHyperlink "http://en.wikipedia.org/wiki/" & Cell Next
Opening a local hyperlink, with a possible pop-up asking for confirmation for security reasons:
ActiveWorkbook.FollowHyperlink "file://C:\Users\Joe Hoe\Desktop\Test.txt"
Getting a temporary file, with unclear robustness of the following method, which uses random numbers and tests for file existence:
Function GetTempFile(Prefix As String, Suffix As String) As String TempFolder = Environ$("tmp") Randomize While True TempFileName = TempFolder & "\" & Prefix & CStr(Int(10000000 * Rnd)) & Suffix If Dir(TempFileName) = "" Then 'Then the file does not exist GetTempFile = TempFileName Exit Function End If Wend End Function
- How To Use GetTempFileName API to Create a Unique Temporary File at Microsoft.com
If you do not mind a popping up console window, here is a way of obtaining output of a command from Excel VBA:
Set MyShell = CreateObject("WScript.Shell") Set ExecObject = MyShell.Exec("tasklist /v") ' AllText = ExecObject.StdOut.ReadAll Do While Not ExecObject.StdOut.AtEndOfStream Line = ExecObject.StdOut.ReadLine() If InStr(Line, "AcroRd32.exe") > 0 Then 'Do something End If Loop
If having a console window popping up is not acceptable and you are okay with creating a temporary file, here is another way of obtaining output of a command from Excel VBA:
'Summary: Run "attrib" on the file in column A (1) of the row 'of the currently selected cell, writing the result into 'column B (2) of the row. 'Get temp file name TempFolder = Environ$("tmp") Randomize TempFileName = "" While TempFileName = "" TempFileNameCand = TempFolder & "\" & "mytmp" & CStr(Int(10000000 * Rnd)) & ".tmp" If Dir(TempFileNameCand) = "" Then 'Then the file does not exist TempFileName = TempFileNameCand End If Wend 'Run the command Set MyShell = CreateObject("WScript.Shell") MyCommand = "cmd /c attrib """ & Cells(Selection.Row, 1) & """ >" & TempFileName MyShell.Run MyCommand, 0, True '0 = show no window 'Although attrib has an exe file, we need to use "cmd" for the 'redirection to work. FileNo = FreeFile() Open TempFileName For Input As #FileNo While Not EOF(FileNo) Line Input #FileNo, MyLine Cells(Selection.Row, 2) = MyLine Wend Close #FileNo Kill TempFileName 'Delete the file to clean up, although not strictly necessary
Using cmd /c, you can run chains of commands connected using & or | as well:
Set MyShell = CreateObject("WScript.Shell") Set ExecObject = MyShell.Exec("cmd /c cd /d C:\Users\Joe Hoe & findstr /s knowledge *.txt") ' AllText = ExecObject.StdOut.ReadAll Do While Not ExecObject.StdOut.AtEndOfStream Line = ExecObject.StdOut.ReadLine() 'Do something Loop
- Capture output value from a shell command in VBA? at stackoverflow
Row height and column width:
Selection.RowHeight = 15 Cells(1,1).RowHeight = 15 'Can be applied to cells, not only to rows Rows(4).AutoFit 'Automatically adjust row height 'Cells(4, 1).AutoFit 'Error Cells(4, 1).EntireRow.AutoFit Selection.EntireRow.AutoFit 'Auto fit the row height of the current selection ActiveSheet.UsedRange.Rows.AutoFit 'Auto fit the row height of the entire sheet Selection.RowHeight = ActiveSheet.StandardHeight Columns(1).ColumnWidth = 70 Cells(1,1).ColumnWidth = 70 'Can be applied to cells, not only to columns Columns(2).AutoFit 'Automatically adjust column width Selection.EntireRow.AutoFit 'Auto fit the column width of the current selection ActiveSheet.UsedRange.Columns.AutoFit 'Auto fit the column width of the entire sheet Selection.ColumnWidth = ActiveSheet.StandardWidth
Working with comment aka note:
If Cells(1,1).Comment Is Nothing Then Cells(1,1).AddComment Text:="Hey" 'AddComment throws an error if the cell already has a comment 'Range("A2:A3").AddComment Text:="Hey" 'Error 'AddComment throws an error if applies to more than one cell at once. End If Cells(1,1).Comment.Text Text:=Selection.Comment.Text & " there" Cells(1,1).Comment.Visible = True 'Prevent the comment from autohiding Cells(1,1).Comment.Visible = False 'The default setting Cells(1,1).Comment.Shape.Fill.ForeColor.RGB = RGB(220, 255, 160) 'The above is the fill color, that is, the background color Cells(1,1).Comment.Shape.Height = 60 Cells(1,1).Comment.Shape.Width = 80 Cells(1,1).Comment.Shape.TextFrame.Characters.Font.Name = "Verdana" Cells(1,1).Comment.Shape.TextFrame.Characters.Font.Size = 9 Cells(1,1).Comment.Shape.TextFrame.Characters(1, 3).Font.Bold = True If False Then 'Selection.Comment.Delete Cells(1,1).ClearComments Range("A1:A2").ClearComments 'Can apply to several cells at once Cells(1,1).PasteSpecial Paste:=xlPasteComments End If
Collecting all comments of the sheet into a string:
CommentString = "" For Each Comment in ActiveSheet.Comments CommentString = CommentString & " " & Comment.Text Next
Add-Ins at StartupEdit
Controlling what add-ins Excel opens at startup:
Microsoft Excel 2003: Configure loaded add-ins via Tools -> Add-ins. Their list is mirrored in the following Registry key, but there's no advantage in editing the Registry:
Microsoft Excel 2002 SP-2:When you start Excel, it may automatically load addins (i.e. those you added from Tools -> Add-Ins). The list of add-ins that get loaded come from the following registry key:
Under this key you may find a list of string-variables:
The values of these variables is the name of the add-in. Excel, on start-up, will attempt to load the add-in in string-variable OPEN first, then OPEN1 (if present), onward until it runs out of such string-variables. It seems like Excel will automatically renumber the keys if they are not consecutive (i.e. OPEN1, OPEN3, OPEN4 will become OPEN1, OPEN2, OPEN3).
Note also the list of add-ins presented when you do Tools -> Add-Ons is, in part, populated by the contents of the following key:
See also the following MS KB article: How to Remove Entries from Add-Ins Dialog Box.
Chart direct from VBA array dataEdit
Charts need not be based on the values in cells of a spreadsheet, but can also be made directly in VBA from an array. The following code makes a chart of the relative frequency of characters in a string, in percent of the total, or normalized to a maximum of unity. There is also an option to sort the display and the content can be changed by modifying the content or sequence of the vRef array. Procedures to remove charts and test the functions are included.
Sub TestChartOfStrFreq() 'run this to make a chart Dim str As String, n As Long, c As Long 'place user string here str = "" 'if no user string use these random charas If str = "" Then Do DoEvents Randomize n = Int((127 - 0 + 1) * Rnd + 0) Select Case n 'numbers, and upper and lower letters Case 48 To 57, 65 To 90, 97 To 122 str = str & Chr(n) c = c + 1 End Select Loop Until c = 1000 End If If ChartOfStrFreq(str, 1, 1) Then MsgBox "Chart done..." End Sub Sub DeleteAllWorkbookCharts5() 'run this to delete all charts Dim oC Application.DisplayAlerts = False For Each oC In ThisWorkbook.Charts oC.Delete Next oC Application.DisplayAlerts = True End Sub Function ChartOfStrFreq(sIn As String, Optional bSort As Boolean = False, Optional bNormalize As Boolean = False) As Boolean 'makes Excel bar-graph chart for percentage incidence of vRef charas in string (or normalized to max value= 1) 'bSort = True for descending percent otherwise vRef sequence 'PREP Dim vRef As Variant, LBC As Long, UBC As Long, LBR As Long, UBR As Long Dim vW() As Variant, x() As Variant, y() As Variant Dim sUC As String, nC As Long, n As Long, sS As String, nS As Long Dim vR As Variant, bCond As Boolean, SortIndex As Long, temp As Variant Dim t As Variant, i As Long, j As Long, q As Long, max As Variant Dim bXValueLabels As Boolean, sT As String, sX As String, sY As String If sIn = "" Then MsgBox "Empty input string - closing" Exit Function End If 'load the intended x-axis display set here...add to it and delete as required vRef = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", _ "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", _ "0", "1", "2", "3", "4", "5", "6", "7", "8", "9") 'set axis labels etc... sT = "Selective Distribution from a " & Len(sIn) & "-Character String" sX = "Character Set of Interest" If bNormalize Then sY = "Count Divided by Maximum Value" Else sY = "Percentage of Original String" End If bXValueLabels = True LBC = LBound(vRef): UBC = UBound(vRef) ReDim vW(0 To 2, LBC To UBC) LBR = LBound(vW, 1): UBR = UBound(vW, 1) ReDim x(LBC To UBC) ReDim y(LBC To UBC) 'COUNT sUC = UCase(sIn) nC = Len(sIn) For n = LBC To UBC vW(0, n) = vRef(n) 'all charas to first row sS = vW(0, n) 'count hits in string for each chara in ref set vW(1, n) = UBound(Split(sUC, sS)) - LBound(Split(sUC, sS)) 'count hits 'calculate hits as percentages of total chara count vW(2, n) = Round(((vW(1, n)) * 100 / nC), 2) Next n 'NORMALIZE If bNormalize Then max = vW(1, FindMax(vW, 1)) For n = LBC To UBC temp = vW(1, n) vW(2, n) = Round((temp / max), 2) Next n End If 'SORT If bSort Then SortIndex = 2 'descending sort, on rows For i = LBC To UBC - 1 For j = LBC To UBC - 1 bCond = vW(SortIndex, j) < vW(SortIndex, j + 1) If bCond Then For q = LBR To UBR t = vW(q, j) vW(q, j) = vW(q, j + 1) vW(q, j + 1) = t Next q End If Next Next End If 'CHART 'transfer data to chart arrays For n = LBC To UBC x(n) = vW(0, n) 'x axis data y(n) = vW(2, n) 'y axis data Next n 'make chart Charts.Add ActiveChart.ChartType = xlColumnClustered 'column chart 'assign the data and labels to a series With ActiveChart.SeriesCollection If .count = 0 Then .NewSeries If bXValueLabels Then .Item(1).ApplyDataLabels Type:=xlDataLabelsShowValue .Item(1).DataLabels.Orientation = 60 End If If Val(Application.Version) >= 12 Then .Item(1).Values = y .Item(1).XValues = x Else .Item(1).Select Names.Add "_", x ExecuteExcel4Macro "series.x(!_)" Names.Add "_", y ExecuteExcel4Macro "series.y(,!_)" Names("_").Delete End If End With 'apply title string, x and y axis strings, and delete legend With ActiveChart .HasTitle = True .ChartTitle.Text = sT .SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis) 'X .Axes(xlCategory).AxisTitle.Text = sX .SetElement (msoElementPrimaryValueAxisTitleRotated) 'Y .Axes(xlValue).AxisTitle.Text = sY .Legend.Delete End With ActiveChart.ChartArea.Select ChartOfStrFreq = True End Function Public Function FindMax(arr() As Variant, row As Long) As Long Dim myMax As Long Dim i As Long For i = LBound(arr, 2) To UBound(arr, 2) If arr(row, i) > myMax Then myMax = arr(row, i) FindMax = i End If Next i End Function
Nuking Retained StateEdit
- Works on: Microsoft Excel 2002 SP-2
- OS: Windows XP
Nuking Excel's Retained State: Excel remembers all sorts of things between runs: What addins to load, what buttons and menus to display, etc. Sometime you'll want to clean all of this stuff up and bring Excel back to its factory state.
Nuking Excel Checklist:
- Make sure the following directories are empty:
- C:\Program Files\Microsoft Office\Office10\xlstart
- C:\apps\xp\application data\Microsoft\xlstart
- Nuke the auto-open keys from the registry(as shown below);
- Nuke all .xlbs – For example check here:
- C:\apps\xp\application data\Microsoft\Excel
- The Mr. Excel Forum: This is a great super-active forum where Excel power-users can often be found.
- Woody's Lounge
- J Rubin's ExcelTip.com
- Chip Pearson
- Ron de Bruin
- BygSoftware.com: This site has loads of practical examples showing Excel's potential
- Aspose.Cells for .NET/Java : A component available for .NET and Java that can be used by developers to create and manipulate Excel files without the need for MS Office to be installed on the system.