Excel Pragma

The Browse For Folder is particularly useful when you’re running a file analysis routine on a specified folder or trying to specify an output folder for a list of files (e.g. SWIFT files).

On a personal note, I will try to keep this blog from turning into a glorified “Favorites” folder. but there were so many times that I needed the “browse for folder” functionality and had to go search for it again and again.

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.

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 do like Infocenter. I do. It is liberating writing the queries on and on again and correcting the typos and other structural glitches. But then again, what if you had your standard queries, all of them in a dropdown box, with all of your KTP databases (production and test) in another dropdown box and you could import all of the data into an Excel spreadsheet immediately ? tada ! enters KTP Data v1.0 module.

All of the dropdown data is obviously configured in a “Config” sheet where all the KTP databases, connection strings and SQL queries are configured and obviously fed into the dropdown boxes :

Click on “Retrieve” and the data gets directly fed to the spreadsheet.

Obviously, there’s always the need to retrieve data from the same tables of two different databases, hence the options of :

IF any of you has worked on KTP, I appreciate the comments and suggestions.

Filling a one-column listbox is a relatively straight-forward job. Use the .AddItem method of a ListBox and you’re done.

Filling a multi-column Listbox is also as simple, if you know the right command.

Basically, what .AddItem does is add a row to the bottom of the list. Now what you need to do is use .List to assign the values to the 2nd, 3rd or more column.

The following code adds the values “RowXcolumn1”,“RowXcolumn2”,“RowXcolumn3” to a 3–column listbox lstMyListBox.

Remember to set the ColumnCount property of the ListBox to the number of columns you need. if you’re trying this example, set the ColumnWidths property of the listbox of 75pt.

If you’ve worked on Reuters Kondor Trade Processing (KTP) version 4.x.x especially on the SWIFT module, you probably agree that a tool for browsing through (and into) atoms would be a great help, if not essential.
Here’s where Atom Checker comes in. Originally developed on Access , I’ve started migrating the functionality to Excel due to the wider availability of the application (on Client side and Consultant side). The current Atom Checker version allows you to :

• quickly search through atoms
• dissect every atom for display in a tree-view format
• provide all the information on the atom from the param_atome table

Here’s a screen shot of Atom Checker v1.0.

The next versions (when fully migrated from Access) will allow you to :

• 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.

Send me a comment if you think there’s more atom functionality that should be exposed to excel. Unfortunately, the ultimate for me would be to be able to execute atoms from Excel but that’s turning out to be too complicated (read impossible)

No matter what type of sheets you’re developing, chances are at one point or the other, you will need to prompt the user to select a range to be used in your functions and procedures.

The code below will prompt the user to select a range and return NOTHING if the prompt was cancelled :

to call this function from other procedures, remember to check whether the prompt was cancelled by the user :