Author: jkpieterse

Beta testing request

Posted on by jkpieterse

Hi everyone,

I am busy building my very first COM addin for Excel and I’ve now come to the stage that I need some beta testers.

Who would be willing to run some tests on my new “Excel Formula Reference Auditing Utility” (see screenshot below)?
excelreftool.gif
If interested, send me an email:
info@jkp-ads.com

What’s in it for you? a free copy of the tool once the beta is finished.

###EDIT Oct 29, 2007###
I’d like to thank everyone who has volunteered for beta testing. For now, I have sufficient people doing testing, so the subscription is closed.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

The New Excel 2007 File Format

Posted on by jkpieterse

Most of you will know that Excel 2007 (well, Office 2007) comes with a brand new file format, based on what MSFT calls Open XML.

This suddenly enables us to write code that can easily generate/change Office 2007 files without the need for an Office installation. For instance on a server.

Whilst there is proper documentation on this file format, the document with detailed descriptions of the Open XML format (“part 4? in the aforementioned link) counts an astonishing 4721 pages !!

This is why I decided to write up a couple of basic pages on how to do stuff with these Open XML files.

My first one is about reading and editing cells:

Working With Worksheet Data In An Excel 2007 File

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Listing an Object’s Properties and Methods

Posted on by jkpieterse

Hi All,

If you do some VBA programming regularly, you’ll be acquainted with the Object browser, which neatly shows you all objects, properties and methods belonging to any object in Excel (or any other library you have referenced in your VBA project).

Whilst this is a tremendous tool to look for information on just a couple of properties I find it not very handy when you e.g. need to get all of an objects properties into your VBA code.

So I thought I’d roll my own object browser, just for the fun of it (and because it proved useful, I share it here).

The tool uses the tlbinf32.dll present on (I assume) any current Windows installation to fetch data from typelibraries. I grabbed some code from various places (amongst which Chip Pearson’s great site and some newsgroup messages like this one) and created a userform with a treeview control (because this is the type of control that can show hierarchical information so nicely).

Here is a screenshot of the tool:

objlister01.gif

Look here for a bit more information and the download link:
Object Lister

Regards,

Jan Karel Pieterse
JKP Application Development Services

Excel VBE Multiline Search And Replace

Posted on by jkpieterse

I’m a full time Excel developer.

That means I spend quite some time writing code in the Visual Basic Editor (VBE).
I don’t maintain a real code library, but I do copy lots of code from previous projects into new ones.

Whereas the VBE is quite a nice application to write code in, it does lack some functionality I needed a couple of weeks ago: the ability to do a search and replace operation on multiple lines of code in one go.

For example, many routines in my projects contain an error handling mechanism of some sort. These may contain something like this:

        Resume
    Else
        Resume Next
    End If

Now what if I want that to look like this:

    Case vbRetry
        Resume
    Case vbIgnore
        Resume Next
    Case vbAbort
        Resume TidyUp
    End Select

I wanted a tool that would let me replace a number of consecutive lines of VBA code with another set of consecutive lines.
Well, here is my first go at it:

Excel VBE Multiline Search And Replace

And here is a screenshot:

Tell me what you think of it!

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