Daily posts of Excel tips…and other stuff
Archive for the ‘VBA’ Category.
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 [...]
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 [...]
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 [...]
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 [...]
