Friday, July 25th, 2008 36 views
If you are working on code for any Office application, you might need to reference an object library for another Office application. I've been working on code for a new add-in for Excel, and needed to write code that would reference the appropriate object library, depending on the version of ...
Posted in AddIns, Excel, VBA, automation | 6 Comments »
Friday, July 11th, 2008 50 views
So I was getting tired of constantly formatting the header row on whatever spreadsheet I am working on, hence this short sub was born. It pretties up the header row and adds autofilter arrows. Most importantly it autosizes the columns so all the information is visible. I chose Teal (ColorIndex ...
Posted in Excel, VBA | No Comments »
Tuesday, June 24th, 2008 1,447 views
In the spirit of my last post Calculate Working Days Minus Holidays in VBA, here is a function I wrote recently that returns the number of working hours between two date/time values passed to the function as arguments. It is self-contained and works independently of whatever existing code you have, ...
Posted in Excel, UDF, VBA | 6 Comments »
Wednesday, May 28th, 2008 92 views
In keeping with my Website XML parsing post, here is a short function that returns the body text from a webpage as a string, on which you can use other functions like Instr, Left$, Mid$, etc, to extract necessary data.
Function URLText(sURL As String) As String
' check website.com using xml
' early ...
Posted in Excel, Internet Explorer, VBA, automation | 1 Comment »
Monday, April 14th, 2008 121 views
About halfway down the Outlook page, there is some VBA code for opening an Excel workbook attached to an email, running a macro on it, then cleaning up and moving the email to another folder.
Here is some more sample code to make your macro even more intelligent. This code opens ...
Posted in Excel, Outlook, VBA, automation | No Comments »
Tuesday, April 8th, 2008 1 views
As you may have noticed, I decided to update the blog, because it was getting hard to navigate. The color scheme was just bringing me down, so I spent about 4-5 hours changing it. That might seem like a lot of time, but I'm a perfectionist so once I start ...
Posted in Excel, UDF, VBA, administrative | No Comments »
Monday, April 7th, 2008 28 views
Here's a series of short UDFs that will return the page headers and footers for the active worksheet.
Function GetLeftHeader()
GetLeftHeader = ActiveSheet.PageSetup.LeftHeader
End Function
Function GetCenterHeader()
GetCenterHeader = ActiveSheet.PageSetup.CenterHeader
End Function
Function GetRightHeader()
GetRightHeader = ActiveSheet.PageSetup.RightHeader
End Function
Function GetLeftFooter()
GetLeftFooter = ActiveSheet.PageSetup.LeftFooter
End Function
Function GetCenterFooter()
GetCenterFooter = ActiveSheet.PageSetup.CenterFooter
End Function
Function GetRightFooter()
...
Posted in Excel, UDF, VBA | No Comments »
Monday, April 7th, 2008 141 views
If you want to programmatically set the "Rows to Repeat At Top" option on the Sheet tab in Excel's Page Setup dialog box, here are two ways to do it.
If you already know what range you want, you could use this code:
ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
This would set row 1 (which would ...
Posted in Excel, VBA | 1 Comment »
Thursday, April 3rd, 2008 4 views
The Advanced Excel class tutorial is almost finished. I still have to write the section on 'Macros'. I'm considering just cutting and pasting from the web, because I'm not sure I have the strength to write something from the ground up. Or it might just be a brief introduction with ...
Posted in Tutorial | No Comments »
Wednesday, April 2nd, 2008 38 views
Here is a simple sub that deletes conditional formatting from a selection. The code does some prelim checking to see that it is actually acting on a range, and it mimics Excel's usual behavior (like a good VBA procedure should, if I may say so) by acting on the entire ...
Posted in Excel, VBA | 3 Comments »
