Spot the 10 differences

Hi everyone!

Yesterday I experienced the tedious task of figuring out what the differences are between the VBA projects of two different versions of a customer project I built a long time ago. Luckily there are tools to compare differences in text files, such as ExamDiff.
But the VB editor doesn’t give you an easy way to export all of your code into a single textfile. So I figured I’d roll my own. At first I simply let the routine run through all VBComponents of the VBA project.

To my surprise, the order of the components in the generated textfiles wasn’t the same for both workbooks, even with the exact same components in there.
So I decided I’d better first make a list of all VBComponents, sort that list and then export the content to a textfile.

Download ExportVBAProject here.

Enjoy!

Jan Karel Pieterse

Name Manager Updated

Hi All,

I’ve just updated Charles Williams and my Name Manager utility.
New in this version is that when you delete a range name, you will be asked whether you want to unapply the range name in your formulas. This should effectively prevent any #NAME! errors from surfacing when you remove a range name.

I’m now off to remove one feature request from the list.

Any wishes? For Name Manager that is :-)

Regards,

Jan Karel Pieterse
www.jkp-ads.com

A Hundred Thousand Name Managers!

Hi folks,

Many of the regulars here probably know the Name Manager utility, which Charles Williams and I created and give away for free on our websites.

Rumour has it this is one of best tools ever built for the Excel developer. I won’t argue with that!

Anyway, as I was looking at my web stats today I discovered a nice feat: We’ve just passed the 100,000 download count on the tool (this excludes the downloads from Charles’ site, so we can safely assume the true number is at least 50% more than that). Time for a celebration:

Hurray!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Preventing Workbook_Open events from running (2)

In Preventing auto_open and Workbook_Open events from running I descibed how to do exactly that. Well, it appears that the methods I gave do not always work with Excel 2007.
A visitor of my website complained he could not prevent his Auto_Open macro from running and during a BeamYourScreen session I took over his desktop and confirmed he was right (of course he was).
The situation where we couldn’t prevent an Excel automacro from running was like this:

– You have set up a trusted folder
– The folder is on a network share
– You’re using the UNC path to that folder.

Since I don’t have a network, I can’t easily test this.

Can anyone confirm whether the shift key trick works in these cases:

– Trusted folder on network (but with an assigned drive letter)

Same as above, but for Office 2010?

Thanks!
Jan Karel Pieterse
www.jkp-ads.com

Performance of linked pictures

I frequently use the camera tool to create pictures linked to cell ranges.
For example to be able to have different column widths beneath each other on one sheet.

They have one major drawback however: they can slowdown VBA performance (when updating cells) enormously (update time may go up from .2 secs to as much as 8 seconds for the same code).

My workaround:

Each camera tool object uses a defined name, defined such as:

=IF(PicsOn=1,Sheet2!$A$1:$C$5,“”)

Then I use these two tiny subs to turn the picture updating on and off:

Sub TurnOffPictures()
    ThisWorkbook.Names(“PicsOn”).RefersTo = “0”
End Sub

Sub TurnOnPictures()
    ThisWorkbook.Names(“PicsOn”).RefersTo = “1”
End Sub

Works a treat.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Importing text files in an Excel sheet

Hi there!

Many times it is a newsgroup post which triggers me to do a writeup on a specific subject. This time, a user asked how he could import a csv file every month, without having to go through the hassle of renaming the file and re-defining the import settings each time.

Opening the file in Excel directly gave unwanted results, similar to this:
Importing CSV files into Excel can be cumbersome...

I have added an article to my website which details out how to set things up properly to save you some work:

Importing text files in an Excel sheet

Regards,
Jan Karel Pieterse
www.jkp-ads.com

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 Declare Function GetWindowLongptr Lib “USER32” Alias _
“GetWindowLongA” (ByVal hWnd As Long, ByVal nIndex As Long) As Long

To:

    Private Declare PtrSafe Function GetWindowLongptr Lib “USER32” Alias _
“GetWindowLongA” (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr

So how do we know whether a Long needs to be changed to LongPtr, or to LongLong, or maybe even can be left unchanged? I decided it would be useful to gather a list of declarations on my website so we have a one-stop place where we can find the proper syntax for these things.

See:

Declaring API functions in 64 bit Office

If you have some additional API functions I could include, please let me know!

Regards,

Jan Karel Pieterse
www.jkp-ads.com