Archive for the ‘VBA’ Category.

Getting Array Data from a Filtered List in VBA

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
   
  [...]

IsHex Function

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 > [...]

Listing Formulas

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 [...]

List Custom Lists

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 [...]

Declaring API functions in 64 bit Office

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 [...]

VBA Page of Pages in a Cell

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). [...]

Problems related to KB973475 and KB973593

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 [...]

Opening Files on Startup

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 [...]