Daily posts of Excel tips…and other stuff
Archive for the ‘VBA’ Category.
Getting data from an Excel sheet into an array is usually best accomplished with a statement that looks something like this:
vMyArray = Sheet1.UsedRange.Value
I thought it would be keen to fill an array from a filtered list, so I coded
Sub ArrFilteredList()
Dim vArr As Variant
vArr = Sheet1.UsedRange.SpecialCells(xlCellTypeVisible).Value
[...]
I have a need to verify some user input is hexadecimal (0-9 and A-F).
Public Function IsHex(sInput As String) As Boolean
Dim lResult As Long
On Error Resume Next
lResult = CLng("&H" & sInput)
IsHex = sInput = "0" Or lResult > [...]
This code will list all the formulas in the selection in the Immediate Window.
Sub ListFormulas()
Dim rCell As Range
If TypeName(Selection) = "Range" Then
For Each rCell In Selection.Cells
Debug.Print String(4, " ") & rCell.Address(0, 0), rCell.Formula
Next [...]
DataPig posted about Custom Lists in Excel and his inability to delete them. I agree on all points; I’ve never needed Sun-Sat and we should be able to delete them if we want.
Here’s a macro to list all the Custom Lists to the Immediate Window.
Sub FindCustomListNumber()
Dim i As Long, j [...]
Hi everyone,
The release date of Office 2010 is closing in and with this new version we’ll have a new programming challenge. Office 2010 comes in a 32 bit and a 64 bit version.
Especially API function declarations need to be adjusted for the 64 bit environment.
We’ll have to change a declaration like this one:
Private [...]
In Page of Pages in a Cell, Jan Karel uses defined names and Excel 4 Macro commands to return the current page and total pages in a workbook. The problem is that it doesn’t work properly when the cell is repeated (e.g. File - Page Setup - Sheet - Rows to Repeat at Top). [...]
Hi All,
Many users (including customers of mine) have experienced trouble with two recent security updates by Microsoft, see:
http://support.microsoft.com/kb/973475
and
http://support.microsoft.com/kb/973593
There seem to be two hotfixes to this problem:
KB973475 Excel 2003 hotfix package :
http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=978908&kbln=en-us
KB973593 Excel 2007 hotfix package :
http://support.microsoft.com/default.aspx?scid=kb;EN-US;978522
If you experience problems with your Excel, check if you have this update and if so, download the hotfix.
For other [...]
Let’s say that you want to open a specific file when you start Excel. Here are some options:
XLSTART
If you have Excel installed, you have a folder called XLSTART. Mine’s here C:\Documents and Settings\Dick\Application Data\Microsoft\Excel\XLSTART. On my Windows 7 machine running Office 2010 Beta, it’s here C:\Program Files\Microsoft Office\Office14\XLSTART\. (Are we putting [...]
