Excel Pragma

I read a nice post about Extending Formulas on Daily Dose of Excel. The comments are full of practical tips. This got me trying out the shortcuts and I accidentally discovered that Ctrl+Alt+Tab adds left-indentation to a cell.

I am a keyboard addict so it puzzles me that I haven’t seen this shortcut before. If you’re new to shortcuts, you’ll find an excellent list at Chip Pearson’s. (The Ctrl+Alt+Tab shortcut is not in the list).

I guess there’s no point in keeping the comments off. The good comments were a big motivation to keep posting so I guess I am more than happy to suffer some comment spam.

So comments are back, keep them coming !

Thanks Rob for the note.

The treeview control is now working fine on all forms. The functionality has proved to be extremely important. Officially I should be satisfied but, alas, like every (self-confessed) excel geek knows, more is better and more is definitely more interesting. So this got me searching again for more sophistication on the treeview controls and I found the Multi-Column TreeView Control. A quick test shows that it works with VBA. Now the key is to understand the object model and get it working for a purpose. More posts on this later.

I have 50 gmail invites to give away. Let me know if you need one (or more!).

By default, forms are set to modal in Excel which means that when a form is show, the main application window (spreadsheet) is inaccessible as long as the form is shown. The main application window becomes accessible again when the form is closed.

There are cases however when both the form and the application window are needed simultaneously. In such cases, the form needs to be set as modeless through setting the “ShowModal” property of the UserForm to False.

Another breakthrough for Excel Pragma but not a happy one. I got more comment spam this morning then I got clean comments since I launched the site. I like the attention but not that kind. So here’s another unplanned to-do : try to figure out how to block the spam. I particularly like the ‘preview’ option that J-walk has used but I need to figure how this can be done.

Good luck to all of you who suffer from this.

Ever needed the values from a range of cells as a string separated by commas ? I used do this in a two step concatenate : the first step is for every cell in the range and then another concatenate for all the “concatenated cells”.

So today, I decided to create my own UDF to generate a string from a selection of cells and any “separator”.

Here’s the ConcSep function :

And here’s the output :

Obviously, this now goes into the add-in you created . and will also be part of my Utilities project.

Atom Checker v1.2 is ready. There are several new features available which were not there in Atom Checker v1.0 . The most interesting additions are :

• Search in SQL : Returns all the atoms that contain a specified string. Useful for checking with atoms retrieve the value date (date_valeur) for example. the Atom details are also provided for quick reference

• MDL Processing : Atom Checker v1.2 now provides the user to select any number of MDL files, import them into Atom Checker 1.2, parse them (and display) them line-by-line, detect all atoms per line, display the line atoms in tree-view with recursiveness.

This functionality should now help me complete the roadmap I mentioned in my previous post :

• back-track through an atom : check if any other atoms use the current atom
• back-track through the modules : check if this atom is used in any accounting configuration or SWIFT Templates.
• back-track through the MDL files to check if the atom is used in any of them.
• check if the current physical MDLs are utilised in KTP.

I was wondering what version control systems are available out there ? I have obviously developed my own Excel workbooks to monitor the different versions for different projects but I am sure there should be more elaborate and useful and hopefully free tools out there. Anybody seen any ?

A concise and clear article about what Digital Signatures are and how to set-them up on MSDN : Code Signing Office XP Visual Basic for Applications Macro Projects.

I started my search a few days ago when I decided to write my own set of utilities. I needed a way to go through the tighter security policies of some of the users (those who set the Macro Security level at High). If you’ve moved around those screens a bit, you would realize that the best way would be to become a “trusted source” and hence the jump into Digital Certificates.

The article provides a clear step-by-step guide to create and test the certificates using the free (?) SelfCert.exe. An official Digital Certificate from Verisign  for example would set you back around $400.0 . or did I get this wrong ?