Daily Dose of Excel

I have two custom add-ins loaded that use application-level events. I only want those events to run when a workbook associated with my application is active. Otherwise, the events in my purchase order application will try and do stuff to my invoices and vice versa. Not good.

For every application, the first thing I do is test to make sure I'm dealing with an appropriate object; be it a sheet, workbook, or whatever. I use two utilities to do the testing. The first utility verifies an open workbook and the second verifies a closed one.

As described in Custom Document Properties, I use custom document properties in my templates to identify the workbook as being part of the application. The utilities check the property and return True if it's there.

In IsOpenInvoice, the property value from the supplied workbook is set to a Boolean variable. If the property value is False or if the property doesn't exist, the variable is False. Of course the property value will never be false. I created the properties in the template and set the value to True. It will either exist or not.

For closed workbooks, IsInvoice uses FileSearch to find a file with the proper name and with the correct property. The argument sName is the full path and name of the file. If the file exists in the directory and has the property, True is returned. This is typically used before a file is opened, such as after GetOpenFilename is used but before the file is actually opened.

Both functions have an optional second argument, sProperty, that defaults to the property name that identifies the application in general. I also use these utilities to identify particular templates within my app. I may need to know, for instance, if the open workbook is an invoice, a sales order, or a report in particular, rather than just part of my app in general. I have constants set up for each template type that I want to test.

Here's an example of an application level event in a custom class module that uses IsOpenInvoice:

On an ongoing project, the client uses the TM1 OLAP system. One of the interesting things I noticed was this:

A user can "slice" data from the database into Excel. The result is a new worksheet where the appropriate cell contains a formula such as =DBRW(...), which essentially looks up the TM1 database for the current value that corresponds to the specified parameters.

So far so good. But, here's the twist. One can enter a new value into the cell containing the formula. TM1 will update the OLAP database with this new value and restore the formula.

As soon as I saw what was happening I guessed how it was done. Here's the implementation of a proof-of-concept. Of course, as a proof-of-concept there are a lot of safeties, performance issues, and other niceties that are missing.

An obvious requirement is that that one must have a secondary data storage since it is impossible for a value and a formula to actually co-exist in a cell. So, that requires a backend database to store the actual value and I decided to use MS Access to create one.

The database had a single table with 3 columns: Col1, Col2, DataVal. For those who want to know how this maps to an OLAP system, think of Col1 and Col2 as dimensions in a TM1 OLAP system and DataVal as the value at the intersection of specific elements in those dimensions.

That led to the infrastructure to access data in the database. In a standard module:

OK, nothing unusual about the above. It's standard stuff to write a User Defined Function that retrieves data from an external database. Again, remember this is proof-of-concept code and leaves out a lot of safeties and performance effectiveness issues.

This is used in a worksheet cell as =DBVal(A3,B3,C3) where A3 contains the Access table being queried, and B3 and C3 the values for the 2 columns Col1 and Col2 respectively. In TM1 parlance, this would correspond to the cube name, and the two elements of the 2 dimensions in the cube.

Next, the infrastructure to allow a new value to update the database.

First, a worksheet event procedure that keeps track of the existing formula. Note that I would never deploy an event procedure in a worksheet code module, but it works well to test concepts.

Next, an event procedure that responds to a new value entered by the user.

The SelectionChange event procedure above saves the cell formula, if it has one, at the time the user selects the cell. Then, if the user enter a value, the Change event procedure uses the new value to update the database through UpdateDB and then restores the formula saved by the SelectionChange procedure.

The corresponding code for the updateDB subroutine in the standard code module:

And, for completeness, code, in the standard module, to close the database connection:

To use the above, in the Access database, a table named Table1. I had 4 records in it corresponding to values of "a" and "b" for Col1 and Col2. The associated DataVal values were 1, 2, 3, and 4, respectively.

In the Excel worksheet, Cell A3 had the value "Table1" and in B3 and C3 one could enter the values corresponding to Col1 and Col2 (i.e., either "a" or "b"). The =DBVal() formula then retrieved the corresponding value from the database.

Now, enter a new value in the cell that contains the =DBVal() formula. The code will update the database with the new value and restore the =DBVal() formula. This will show the new value in the cell.

From KeepITCool

I get NA for the array formula

=SUM(kicceiling(A17:B18,E17:E18))

but I haven't had time to sort out why.

With regard to Rewriting the CEILING function, fzz comments:

Not only does it fail to handle arrays, it also mishandles string arguments that could be converted into numbers.

Good one. I hate it when I miss the easier ones.

And Stephen Bullen comments:

Now extend it to handle array formulae!

Ouch! That complicates things. Below is my attempt at that. There's probably a better way to combine arrays, but I couldn't figure it out.

Update: Rob Bruce correctly points out that I wasn't bubbling error cells up through properly. I was also returning an one-element array with an error for any error that occurred, rather than returning the complete array with the error in the proper element. As I was fixing that, I also discovered that dates weren't working. The above code is the latest iteration and the old code is gone forever. What else did I miss?

Someone on the newsgroups wanted to rewrite the CEILING function in order to learn how to write user defined functions. Rewriting built-in functions is a good way to learn, I think. It seems like I pick up something new every time I go through the exercise,which is not that often. Ben McBen supplied the logic in answer to the post and I added the error checking. If you've ever written a worksheet function, you know it's 90% error checking and 10% logic.

I always make my arguments and return values Variants. If it's to be used in a worksheet, the user should be able to enter a range reference or a value. VBA will convert range references to their values because the Value property is the default property for the Range object. But handling the conversion explicitly inside the function gives me more flexibility in returning specific errors. For return values, I need to be able to return one or more error values, so it has to be a Variant.

Note that I have to explicitly handle Boolean data types. VBA and Excel treat True in different ways. Namely, VBA assigns True the value of -1 and Excel assigns it the value of 1. Even if the True comes from Excel (like in a worksheet function argument), VBA still treats it as -1, so I use the Abs function to make it 1.

What did I learn? I learned that I didn't really understand integer division like I thought I did. I originally used the \ operator because I thought it was the same as Int(x/y), but not so. I guess I've only every used it with Integer operands. When I passed it a Double divisor, it was converted to an Integer. Since the Integer it was converted to was zero, I got an error. So my lesson was "don't use integer division unless you're dividing integers".

Emily once commented with a nifty procedure for enumerating mapped drives. It uses the Windows Script Host Object Model. I created a function to convert mapped drives to UNC paths for an application I'm working on.

But I still have a problem. I have a list of documents and a list of links. The user can add a link by navigating to the document (using GetOpenFileName). If the document already exists in the Document table, the link is created. If not, a record is added to the Document table, then the link is created. I need to ensure there are no duplicates in the Document table.

I thought that converting paths to UNC paths would do it, but not so. The user can navigate to a document through multiple network shares. For instance, he could go to \\Actserver\AIMUSA\Mydoc.pdf or he could go to \\Actserver\AllCompanies\AIMUSA\Mydoc.pdf and he would be pointing to the same document. The paths don't match, though, so this document could be added twice.

There's only currently a few of these overlapping shares, so I could catch them "manually" in the code. That doesn't seem like a good long term solution, though, as certainly another share will crop up that breaks it. Ideas?