Hi all,
An update to version 4 of the Name manager is out.
I have worked hard to weed out the bugs reported to me so far. Things are definitely looking better and better.
Also I have added a feature so you can see where a name is in use. Press the “Is name used” button to get this dialog:
Have fun trying the new features!
Download here
Regards,
Jan Karel Pieterse
JKP Application Development Services
In Dynamic Tables, I describe a method that I used to show errors in a data entry table using conditional formatting. I had a small problem with that method that I didn’t address in that post, and now that problem just doubled.
My original problem had to do with the order in which the user would enter data. There is one key field, Invoice, and if that field was empty, but there was anything else in the row, it would show an error. The problem is that Invoice is not the first field to be filled in, the Customer is. The user fills in a customer id and the row turns red because there’s data that’s not an invoice. There really isn’t an error, though, it’s just that the user hasn’t made it to the second field yet (which is the Invoice field). A simple fix, you say, make Invoice the first field. For various reasons, include my lack of a spinal cord, I’m not willing and/or able to change the order of the columns. So I lived with it and so did the users. I suspect that a little piece of the user died each time the row turned red, and it was all my fault.
The doubling occurred when I discovered another necessary bit of data validation that wasn’t included. The user has to enter a Customer, Invoice, and some other information. They must enter exactly two pieces of other information. No more. No less. It’s fairly trivial to do a COUNTA function on the other fields and make sure it’s two. However, it presents yet another timing problem. The user can hardly enter two pieces of information without entering the first piece. And once they do that, it’s red again.
I need a way to determine which row the user is on, and suspend any error messages until they’ve left that row. I thought about moving the whole darn thing to VBA, but I like the formula/conditional formatting approach because it’s quick and other people can maintain it if necessary. I use the CELL function to determine the active row and incorporate that into the CF formula. The upside is that I can omit the second argument, reference, from the formula and it will act on the active cell. The downside is that CELL becomes (or always is) a volatile function. Volatile functions recalculate whenever the worksheet recalculates whether any of their dependencies changed or not. Basically, much of the energy spent on recalculating volatile functions is wasted because the result doesn’t change.
That seems like a winner, except for one little hitch. Moving the cursor around an Excel worksheet doesn’t force a recalc. A formula like =CELL(“row”) will return the row number of the cell you were in when Excel recalculated last. It creates kind of a delayed effect and could conceivably not be caught at all.
Here’s a mock up of the situation. I assure that the actual formatting is far more gaudy than this.
The formulas:
I’ve just entered something in F5 and the error doesn’t show because I’m still in that row. What’s your opinion of this technique?
Tomkat is trying to cap mileage paid based on production. The mileage cap varies based on production and site. Read this newsgroup post for details if you like. This newsgroup post is where it all started.
This calls for a VLOOKUP/MATCH combo if I ever saw it.
The formula in E15 (and filled down column E) is
MATCH: The MATCH function returns the column that matches the site entered. It’s used as the col_index_num argument of VLOOKUP, that is, which column VLOOKUP retrieves. You have to add two to the result because the range of “Sites” starts two cell right of the vlookup range.
VLOOKUP: The left-most column of the mileage cap table is filled with the lowest number of the appropriate range of numbers. Column B shows the range of numbers, but it’s just there for informational purposes. Because there is no fourth argument in the VLOOKUP, it will find the largest number that is not greater than the number it’s looking for. If, as in row 15, we’re looking for 342, it will find 300 because that’s the largest number in the list that’s not greater than 342.
MIN: The whole thing is wrapped in a MIN because it’s a cap. If the person claims less than the cap, then that value should be used, not the cap.
I hope that helps Tomkat.
I don’t know many people know this, but I find it extremely useful when I’m designing a form.
If you select a control, you can then press the TAB key to cycle to the next control (and on) to see what the flow of the form is, just like if you were using the form live. It’s very easy to spot errors in the TabIndex properties this way.
But sometimes I wish we also (and note the *also*, I don’t want that to be the *only* way) had a feature like Adobe has for its forms, where you just click the tab order of the controls.
Add-ins – Working with the registry in Windows
In this second post about Add-ins and loading Excel via automation I’ll discuss how we can manipulate the Windows Registry without using Windows API.
To take part of the first part please see the following post:
Add-ins Where does Windows store the information?
Regobj.dll
The Regobj.dll is an ActiveX Server that gives us an object model and a structure which reduce the complexity when working with the registry. A Word document is shipped together with the DLL file which gives a general description about the RegObj.dll. It’s available for download at the following place: RegObj
Locate the path to Excel’s main folders
For many applications it’s common to create flexible solutions in that we need to consider the existence of several versions of Excel. In addition, we cannot assume that Excel is always installed in the default location. So when we raise the question how to locate the paths to Excel main folders we are forced to consider these conditions. The goal of this process is to retrieve the names of XLAs/XLLs that are shipped with Excel and their respectively pathways.
I consider the following folders as the main folders for XLAs /XLLs shipped with Excel (in US/English version of Excel):
-Library
-Analysis
-SOLVER
-XLSTART
The below code is created with classic VB and in order to locate the main folder for Excel (based on which version we want to locate) we need to do a two steps approach. First locate the CLSID in the HKEY_CLASSES_ROOT and then retrieve the pathway from the subkey “LocalServer32” in the same section of the registry.
stXLversion = Me.Combo1.Text
If Locate_XLAs_XLLs_Take_One(stXLversion) = False Then
MsgBox “The selected version is not available on the computer.”, vbInformation
Exit Sub
Else
‘Do other stuff
End If
End Sub
‘A reference to the Registration Manipulation Classes must be set.
‘A reference to the Microsoft Scripting Runtime must be set.
Function Locate_XLAs_XLLs_Take_One(ByVal stVersion As String) As Boolean
‘Notable: The string variable stVersion refers to “9”, “10”, “11” etc.
Dim stSubRoot As String, stCLSID As String, stExcel As String
Dim stMainPath As String, stLibrary As String, stAnalysis As String
Dim stFoldersArr(0 To 3) As String
Dim stSolver As String, stXLStart As String
Dim objRootKey As RegKey, objStartKey As RegKey
Dim fsoObj As Scripting.FileSystemObject
Dim fsoFolder As Scripting.Folder
Dim fsoFile As Scripting.File
Dim iCounter As Integer
On Error GoTo Error_Handling
‘Instantiate the FileSystemObject.
Set fsoObj = New FileSystemObject
‘Define the subkey.
stSubRoot = “Excel.Application.” & stVersion & “CLSID”
‘Define the rootkey.
Set objRootKey = RegKeyFromHKey(HKEY_CLASSES_ROOT)
‘Parse the subkey.
Set objStartKey = objRootKey.ParseKeyName(stSubRoot)
‘Retrieve the CLSID for the wanted Excel version.
stCLSID = objStartKey.Value
‘Redefine the subkey.
stSubRoot = “CLSID” & stCLSID & “LocalServer32”
‘Parse the subkey
Set objStartKey = objRootKey.ParseKeyName(stSubRoot)
‘Clean up the obtained value.
stExcel = Replace$(objStartKey.Value, “/automation”, “”)
‘Extract the main path in which Excel.exe is located in.
stMainPath = Mid$(stExcel, 1, InStrRev(stExcel, “”, , vbBinaryCompare))
stLibrary = stMainPath & “Library”
stAnalysis = stLibrary & “Analysis”
stSolver = stLibrary & “SOLVER”
stXLStart = stMainPath & “XLSTART”
‘Populate the array with the pathways to the main folders.
stFoldersArr(0) = stLibrary
stFoldersArr(1) = stAnalysis
stFoldersArr(2) = stSolver
stFoldersArr(3) = stXLStart
‘Iterate through the collection of folders and print
‘name and pathway for the located XLAs/XLLs in the immediate window.
For iCounter = 0 To 3
If fsoObj.FolderExists(stFoldersArr(iCounter)) Then
Set fsoFolder = fsoObj.GetFolder(stFoldersArr(iCounter))
For Each fsoFile In fsoFolder.Files
If LCase$(fsoFile.Name) Like “*xla” Or LCase$(fsoFile.Name) Like “*.xll” Then
Debug.Print fsoFile.Name & “;” & fsoFile.Path
End If
Next fsoFile
End If
Next iCounter
Locate_XLAs_XLLs_Take_One = True
‘Release objects from memory.
ExitHere:
Set fsoFile = Nothing
Set fsoFolder = Nothing
Set fsoObj = Nothing
Set objStartKey = Nothing
Set objRootKey = Nothing
Exit Function
Error_Handling:
‘Error 35006 indicates that the choosen version is not available.
If Err.Number = 35006 Then Locate_XLAs_XLLs_Take_One = False
Resume ExitHere
End Function
An alternative approach to search for files in the folders is to identify each add-in’s CLSID and retrieve the pathway in HKEY_CLASSES_ROOT. However, the downside is how we should do to solve it for custom’s add-ins.
Locate the present user’s path
Below is a snippet code to locate the present user’s path and to retrieve the XLAs/XLLs names and pathways in the user’s folder (if any add-ins exists at all):
BuffLen = 100
GetUserName Buffer, BuffLen
stUserName = Left(Buffer, BuffLen – 1)
stUserFolder = “C:Documents and Settings” & stUserName & _
“Application DataMicrosoftAddIns”
If fsoObj.FolderExists(stUserFolder) Then
Set fsoFolder = fsoObj.GetFolder(stUserFolder)
For Each fsoFile In fsoFolder.Files
If LCase$(fsoFile.Name) Like “*xla” Or _
LCase$(fsoFile.Name) Like “*.xll” Then
Debug.Print fsoFile.Name & “;” & fsoFile.Path
End If
Next
End If
Change load behavior of COM add-ins
COM Add-ins load behavior can be controlled by either changing the LoadBehavior subkey in the registry setting or by using the Connect property of the COM add-ins as the following snippet code shows:
However, for COM add-ins that target the VB-editor the load behavior seems to be only controlled via the registry setting as the following sample shows:
Sub Write_To_Registry()
Dim objRootKey As RegKey
Dim objStartKey As RegKey
Dim objCOMKey As RegKey
Dim objCOM As RegValue
Set objRootKey = RegKeyFromHKey(HKEY_CURRENT_USER)
Set objStartKey = objRootKey.ParseKeyName(stSubKey)
Set objCOMKey = objStartKey.ParseKeyName(“IndenterVBA.Connect”)
Set objCOM = objCOMKey.Values(“LoadBehavior”)
‘Change the load behavior to connected | bootload.
objCOM.Value = 3
Set objCOMKey = Nothing
Set objStartKey = Nothing
Set objRootKey = Nothing
End Sub
RegObj.dll create a less complex code writing then with Windows API however the use of the DLL as well as the use of Microsoft Scripting Runtime can cause an overhead in the application that may or may not be a problem.
For more information about Windows API and additional information about the subject please see the following articles:
How To use the Registry API to Save and Retrieve Setting
How to Determine the Path for an Office Application
How to build an Office 2000 COM add-in in Visual Basic
INFO: Excel COM Add-ins and Automation Add-ins
How to run multiple versions of Excel on the same computer
In the third and final post I’ll present a standalone utility that lets us choose which add-ins to be loaded or not when loading Excel through automation.
Kind regards,
Dennis
As you may recall, I’ve written about registering User Defined Functions before.
The problem with the trick I used in that post, was (and this isn’t trivial): it crashed Excel.
KeepITCool came up with yet another trick to make this work, which I have described in
this article.
Enjoy!
Regards,
Jan Karel Pieterse
JKP Application Development Services
Add-ins Where does Windows store the information?
The purpose with this post is to give a basic understanding about where Windows registry store information about add-ins in order to load the add-ins when we start Excel through automation. It’s the first out of three posts on the subject. As for the Excel versions the 97 and 2007 are excluded.
Types of Add-ins
Since Excel can handle several different types of add-ins the best approach to try to answer the question is to use these categories.
In general add-ins can be divided into the following categories:
XLA Add-ins (Created with Excel)
XLL Add-ins (Created with C/C++)
Automation Add-ins (Usually created with classic VB or with C++)
COM Add-ins for Excel (as above)
COM Add-ins for the VB-editor (as above)
XLA
XLAs are easy to locate in Windows registry as they only appear in two places.
If they are available and activated Windows stores the information at the following place:
HKEY_CURRENT_USERSoftwareMicrosoftOfficeversionExcelOptions
If they are available but deactivated Windows stores the information at the following place:
HKEY_CURRENT_USERSoftwareMicrosoftOfficeversionExcelAdd-in Manager
“Version” refer here to the internal number versions of Excel like “9.0”, “10.0”, “11.0” etc.
The XLAs that are shipped with Excel is not located in the Add-in Manager section when deactivated instead they can be found in the HKEY_CLASSES_ROOT like the following example:
HKEY_CLASSES_ROOTInstallerComponentsC848331AADAA4D11298D0001A58916F6
XLL
Despite the technical differences between XLAs and XLLs Windows registry store the information about XLLs in an identical way as for the XLAs.
Automation Add-ins
This type of add-ins is only available from 2002 and on forward. Compared with XLAs/XLLs the only differences are the following:
* Instead of filenames they are referred with their ProgID (Servername.Classname).
* When activated the string”/A” is added in front of the ProgID.
COM Add-ins for Excel
This category of COM Add-ins is accessed via Excel’s standard UI. Unlike the other add-ins above the subkey’s names are identical to the COM Add-ins ProgIDs and each COM Add-in has at least the following accessible information available:
– Description / Friendly Name / LoadBehavior
Unlike the other add-ins above the information for the COM Add-ins can be found in one of the following sections in the registry:
– HKEY_CURRENT_USERSoftwareMicrosoftOfficeExcelAddins
-HKEY_USERS.DEFAULT SoftwareMicrosoftOfficeExcelAddins
-HKEY_LOCAL_MACHINESoftwareMicrosoftOfficeExcelAddins
Information about third-part COM Add-ins (like SnagIt etc) tends to be stored in the HKEY_LOCAL_MACHINE, which may be the preferable location (at least from my point of view).
COM Add-ins for the VB-editor
This group of add-ins target the VB-Editor in Excel and the information about them are stored (in an identical way as for the COM Add-ins for Excel) in:
HKEY_CURRENT_USERSoftwareMicrosoftVBAVBE6.0Addins
However, in addition to this location information is also stored in, when the COM Add-ins are activated: HKEY_CURRENT_USERSoftwareMicrosoftOfficeversionExcelOptions
Not only that, the “/A”-switch is also added to the ProgID which means that they are similar to Automation Add-ins, at least on their faces. I’ve not been able to understand the logic for this behavior so if anyone can explain it to me I would be very pleased.
Additional information about XLLs, Automation Add-ins and COM Add-in is stored in the HKEY_CLASSES_ROOT section of the registry.
For additional information please see articles listed at the following URL:
A collection of useful Excel Articles
Please let me know if I’ve missed any essential as it can be rather confusing when ‘diving’ into the registry.
In the next coming post I’ll discuss an alternative approach to Windows API when working with the Windows Registry.
Kind regards,
Dennis
In Testing for Empty Cells, I described the IsEmpty function and how it will tell you if a cell is truly empty. If you need to determine if a whole range is empty, IsEmpty won’t do the trick. IsEmpty returns TRUE if the variable passed to it is uninitialized. The Value property of a Range object meets that criterion, but the Value property of a multi-cell Range object is an array. Arrays are never empty (I think) even when there’s nothing in them. Presumably this is because they are initialized when they are created. I really don’t have a complete understanding of this whole process, but I do know that IsEmpty doesn’t work.
There has to be something that works, though. I set up this procedure and looked at the Locals Window in the VBE.
I got as far as the F’s when I found the holy grail. It seems that the FormulaArray property provides the information I want. If the range actually contains an array formula, then Len(rRng.FormulaArray) will return a non-zero number. If the range contains some data, that code will return Null. If the range is empty, the return value is zero.
