Daily Dose of Excel

I have four objects that are all dependent on each other. They are CInvoice has many CInvoiceLines, CInvoiceLine has many CRoyaltyLines, CRoyaltyLine has many CRoyaltyAdjustments. In order to properly terminate all of these class modules, I need to remove any and all dependencies. If I don’t, the classes will stay alive and consume memory. With four levels like this eating memory, I will eventually have to answer the question: Do I want to send an error report?

I think I have my termination sequences right, but I’ll let you be the judge in the comments. I start in the CInvoice class, which I’ll call a Parent class. It has many children, including the CInvoiceLines class (a child collection) and each individual CInvoiceLine class (a child class). The CInvoiceLine class then becomes a parent class for my next round of terminations. His children are the CRoyaltyLines class and each instance of the CRoyaltyLine class.

All of my Terminate events are structured like

I don’t use the built-in class terminate event because it doesn’t fire at the right time. When I’m in a parent class, I do these three things:

1. Call the Terminate method of the Child Collection Class
2. Set the local Child Collection Class variable to Nothing
3. Set the local Parent variable to Nothing

Items 1 and 2 are done in a parent class that is not also a child class. Item 3 is done in a child class that is not also a parent class. All three items are done in a class that is both a parent and a child. For instance, CInoviceLine is a parent with respect to CRoyaltyLines, but a child with respect to CInvoice, so all three steps must occur.

When I’m in a Child Collection Class (ex: CInvoiceLines), I do these three things:

1. Call the Terminate method for each member of the collection
2. Set the local Parent variable to Nothing
3. Set the local Collection variable to Nothing

When item 1 is executed, it may be terminating a class that’s a parent class and the whole things starts over again. Here’s an example: In the CInvoice class

Since mobjLines is a CInvoiceLines object, that Terminate method gets called first. In the CInvoiceLines class

When

is called, it’s calling the Terminate method of a class that’s both a child and a parent. In CInvoiceLine

The only difference between this and CInvoice is that I killed the parent variable because it’s also a child class. I won’t go through the rest, except to show you the last class, CRoyaltyAdjustment

Since this is a child class, but not a parent class, only killing the local parent variable is necessary.

Boy, there’s nothing more thrilling than terminating classes, is there? That’s why I added the image – to spice it up a little. For a little background, I’m abstracting my relational database into objects in VBA. That way I can reference

, which I contend is easier to code and read. But the setup is a real pain.

Hi all

I update my ADO page today.
http://www.rondebruin.nl/ado.htm

The code is also working in Excel 2007 now (Excel 2000-2007).
I use Late binding now to avoid private mails like “It is not working”
When you use the Subfolder option in Example 7 it will loop through all files in all levels below the rootfolder.

If you have problems or suggestions let me know.

I wish all readers of this blog a Merry Christmas and a Super 2008.

Ron de Bruin
http://www.rondebruin.nl/tips.htm

This report comes from Charles Williams of DecisionModels.com fame. Charles knows a thing or two about performance in Excel.

For the last few weeks I have been trying to find out why clearing a large range of cells caused Excel to hang on some PCs but not on others. This does not happen with Excel 97, but does with Excel 2000, 2002, 2003 and 2007.

It turns out that its not just Clear, but also Delete or transferring data from a variant to a range, or even just selecting a large range of cells.The further down the sheet and to the right the slower it gets. And the more recent the Excel version the slower it gets.

Someone on the newsgroups discovered that, when using VBA, you could bypass this problem by switching off EnableEvents, and then someone else discovered that the culprit was Google Desktop Search.

The problem actually turns out to be the Google Desktop Office Com Addin. When you deactivate this you get a miraculous speedup.

With Excel 2007 it is fairly easy to deactivate:
Office Button–>Excel Options–>Addins–>Com Addins and deselect Google Desktop Office Addin

With earlier versions of Excel you have to customise a toolbar and add the Com Addins dialog to it.
View–>Toolbars–>Customise–>Commands tab–>Tools then about halfway down you will find Com Addins, select and drag to the toolbar of your choice.
Then you can uncheck Google Desktop Office addin.

If you have multiple versions of Excel installed you only have to do this once.

Presumably this COM addin sets up one or more application-level events to monitor things like Selection Change and Worksheet change and then tries to trap the change in order to index it.

If you want to measure this effect you can download a Variant Benchmark Timer from my website that allows you to run a read and write benchmark with and without EnableEvents.

DecisionModels.com/Downloads

This represents an interesting new twist in the Google-Microsoft wars!

Thanks Charles.

You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.

What to do? The only way I find to be reliable is to catch all possible paste operations. But this isn’t very easy, since there are a zilion ways to paste.

I have put together a sample workbook and an explanatory article on how one could go about protecting a workbook by intercepting paste operations.

Let me know what you think!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

This one really hits home:

Click image for larger view.

Every VBA developer (which is anyone who knows how to get into the VBE I guess) develops his or her own programming habits: sparse commenting or elaborate commenting, naming convention (or no declaration at all), code indenting, preference for certain structures and methods over others. You name it. Many books have been written about this, of which I find “Code Complete” is a very good one.

Although I think I write pretty readable code, I do have one bad habit: I don’t really document what I have done and I tend to “forget” to write comments.

Sometimes a customer wants elaborate documentation of the code. But of course they forgot to ask up front…
So here I am looking at this 10.000 lines-of-code VBA project and a request to produce tech documents on what the code does and how it is structured. Including flow diagrams (preferably in Visio), call trees, the works.

At first I estimated I’d need as much as maybe half the amount of time I originally used up to write the code itself. Which was significant of course. Let’s say over a full week.

Like with any task that I find tedious: I look for a way to automate, so I dive into a search quest with Google.

Typical search strings: VBA code documenting, Document Code, Create Dependency tree, Show call stack,….

Well, I found this site.

Both their Project analyzer tool and Visustin looked like they might be a solution to my problem. But they’re both rather expensive (I’m Dutch, remember?). I calculated I’d have to invest about € 1000 to cover for these two tools in the versions I think I’d need.

Luckily Aivosto granted me a time-limited full version of the Pro version of both tools so I could thoroughly test them (and to be frank I also promised to write up my experiences).

Here they are then.

I opened Project Analyzer and since I also installed the Office VBA plug, the File menu shows “Analyse Office VBA…” as one of the options. You point it to your file and it happily imports the entire VBA Project (if you have “Allow access to VBA project” set, of course). So far so good!

Take a look at the Report Menu. It shows a myriad of reporting tools. Very impressive!

I pick the “Problem Report” and it shows me lots of useful (and maybe even embarrassing) stuff, indicating line numbers and of course the routines and module, like:

Too many parameters: WriteName2sheet
194 Consider short circuit with nested Ifs
432 Unicode function is faster: ChrW$

Function without type specification
692 Too many uncommented lines: 81 (ouch)
Dead procedure

And lots of other useful stuff. Didn’t know I produced such a load of rubbish .

OK, let’s try something else:Procedure call tree. Wow. Everything’s there:

Then I tried the graphical version of the call tree:

(Yes I blurred this one on purpose).

So far so good. I won’t bother you all with the dozens of other reports I tried and used. I got more impressed every minute I can tell you!

…Lots of copying and pasting from Project Analyzer to Word followed…

Now let’s have a look at Visustin. Ever needed to create a flow diagram? Well I haven’t, because I tend to just dive in (I know, bad habit…).

Have a look at this procedure:

So now what? Well, copy, paste and hit F5. You get this:

WOW! (also proves commenting is useful…)

So next I found myself in the process of alt-tab to the VBE, select code, control-c, alt-tab to Word, paste code, alt-tab to Visustin, control-v, F5 (builds this chart), control-c, alt-tab back to Word, paste the diagram, …..

And the fun part was creating the Visio diagrams. They didn’t turn off screenupdating and I can tell you it is great fun seeing this program spitting out these (for me) complex diagrams in seconds, which would have taken me hours and hours…

All in all, producing the entire document set took me about half a day. Man, this tool cost me money! (but I gained quite a happy customer).

And to think that the enterprise version of Project Analyzer comes with macros…

Tell me what you think and what your experiences are! Have you got similar experiences, or completely different,…
Share them here!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Via the US ISV Developer Team Blog

Microsoft Office 2007 SP1 will be available for download Dec 11. The improvements in 2007 Office System SP1 are in response to direct feedback from power users at large organizations or indirect feedback from home and Office users through the Dr. Watson bug-reporting system.

I haven’t seen any details about what’s been done, but don’t expect any UI changes or charting improvements. They might have fixed some stuff, buy I’m not getting my hopes up.

This is the coolest thing I’ve seen in a long time: Google Chart API.

The Google Chart API returns a PNG-format image in response to a URL. Several types of image can be generated: line, bar, and pie charts for example. For each image type you can specify attributes such as size, colors, and labels.

You can include a Chart API image in a webpage by embedding a URL within an IMG tag. When the webpage is displayed in a browser the Chart API renders the image within the page.

Here’s a chart that is generated on the fly:

And here’s the URL that creates it:

http://chart.apis.google.com/chart?cht=lc&chd=s:
pqokeYONOMEBAKPOQVTXZdecaZcglprqxuux393ztpoonkeggjp&chco
=FF0000&chls=4.0,3.0,0.0&chs=200×125&chx
t=x,y&chxl=0:%7CJun%7CJuly%7CAug%7C1
:%7C%7C20%7C30%7C40%7C50&chf=bg,s,efefef

You’ll find more examples here.

I wonder how difficult it would be to write VBA code that analyzes an Excel chart, and then creates a Google Chart API URL to reproduce it?

(Cross-posted at J-Walk Blog)

I was reading How to Win at Monopoly (via Kottke.org). On Table 1, a range of cells was outlined.

Strangely (or maybe not so strangely), I thought about the most efficient way to do that in Excel. I don’t think there is one. Let’s say I hold down the control key and select some cells. It might look like this:

If I hit the BorderAround toolbar or execute

in the Immediate Window, Excel borders the ranges as I selected them.

What I want, of course, is to border the selection as if it’s a single area. It turned out to be a more complicated macro than I thought. I may have made it too complicated, but I’m sure you’ll tell me if that’s the case.

My plan is to use the BorderAround method, then remove the borders that I don’t want. I’ll need to cycle through the Areas and identify common borders between two areas. When I identify those borders, I’ll need a Range and which border on that range, Top, Bottom, Left, or Right. First, I create a user-defined type to store the address of the Range and which border to remove.

Next I’ll need a function to give me the common border. I want to pass two Areas in and get a CommonBorder out.

Four blocks of nearly identical code. It’s just screaming for a loop. To determine if the two Areas share a border, I extend one of the ranges in each direction and see if there’s an Intersect. For instance, to check if the bottom border is a common border, I extend rOne down one row and see if there’s an Intersect. If there is, I return the Range that results from the Intersect and xlEdgeTop to identify which border of that Range is the right one.

Note that each border is mutually exclusive. If there is a common top border, it doesn’t check for any others. Although the function doesn’t check for it, it requires that the two Areas don’t intersect each other. They can abut each other or have no common borders, but no overlapping. That’s something that really needs to be fixed.

Now that the hard part is over, I just loop through the Areas of the Selection and remove the border for any common borders.

If tBorder.sAddress doesn’t contain anything, then there’s no common border between those two Areas. If it does, the border is removed. I get this:

Well that was fun. Two things: As I mentioned, overlapping ranges are bad. If two ranges have more than one cell in common, it doesn’t eliminate interior borders. I could just remove all borders around any intersection, but then there’s the case where the two ranges intersect AND share a common border. That leaves some unsightly holes. Since I don’t really have any use for this, I lost interest in getting it right.

Secondly, the best way to apply this type of border would be conditional formatting. I think I’d need four conditional format criteria in order to do that. In 2003, I only get three.

Hi,

A while ago I requested beta testers for a new utility, now called “ExcelRefTool”.

Thanks to my beta testers, the tool is now mature enough to be exposed to the general public.

Have a look here, download the demo if you like and give it a spin.

And thanks again, to everyone who took the trouble of beta testing this tool for me!

Regards,

Jan Karel Pieterse
www.jkp-ads.com