Archive for the ‘VBA’ Category.

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

Using controls on worksheets

Hi all,
I decided I need a bit more content on my site for the average to intermediate Excel users:
Using controls on worksheets
Apart from input cells chained to cells with formulas, there are other options in Excel to make your spreadsheet model more interactive. You can add option buttons, check boxes and list boxes to your [...]

Hello World Button on a Ribbon

Many of my spreadsheets use Excel 2003 Commandbars for application control.
Having loaded these spreadsheets into Excel 2007, I see my commandbars presented on the Add-In tab of the Ribbon.
I’m a late starter with Excel 2007 ribbons, but I guess it’s time to move.
I’ve found that the various guides on the Internet tend to throw you [...]

Multiple Substitute VBA

In regard to Multiple Substitute Formula, here’s one way to do it in VBA.
Sub RemoveStates()
   
    Dim rInput As Range
    Dim rStates As Range
    Dim vaInput As Variant
    Dim vaStates As Variant
    Dim i As Long, j As Long
    Dim sTemp As String
   
    Set rInput [...]