Dutch Gemini's Weblog Pages

March 19, 2012

Excel VBA Oddity – Replace function not returning entire string

I just boomed into this funny oddity using VBA with Excel.

I wanted to generate data to a text file in the TEMP folder, so I used the FileSystemObject’s GetSpecialFolder() to obtain the TEMP folder name and I crafted a file name using the system time. Then I joined the 2 strings with the backslash character (\).

To avoid problems with double backslash characters, I used Replace() to replace them with single backslash. Since I did not want to replace them at the beginning of the string because I am not sure if the temp folder is not something like for instance in ‘\\server\share\...‘, I decided that I would start replacing from position 3 onwards, like in:

sFileName = VBA.Replace(sFileName, "\\", "\", 3, -1, vbTextCompare)

From Help, I read (and highlight only the relevant parts):


Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description

start Optional. Position within expression where substring search is to begin. If omitted, 1 is assumed.

Reading this, I presume that the Replace() statement above should start replacing only from position 3 onwards. Well, it does not. What happens is this:

Replace() replaces ‘find‘ in ‘expression‘ with ‘replace‘, starting at position ‘start‘ but returns a string that does not contain the characters up to ‘start‘.

In other words, it appears that VBA first Mid()‘s the string at position 3, then starts replacing, and finally returns the shortened string. See here as well (includes workaround).


PS: if you read Help to the end, it says «If start > Len(expression) then zero-length string is returned». This implicitly confirms that if you want something from the string back using Replace() then you should definitively set start to a value below Len(Expression). And I say that if you want back everything (obviously, with the replacements done), set start to 1.

October 28, 2011

Weird bug in Excel’s Application.GetSaveAsFileName() function on Vista and 7

Filed under: Excel,VBA,Windows — dutchgemini @ 2:55 pm
Tags: , , , ,

I have found a bug and a strange problem while using Excel’s native Application.GetSaveAsFileName() function.

First to the bug.

Using Excel 2007 on top of Windows 7, this dialogue cleared the initial name I provided (obtained manipulating the current workbook’s name) while it was working fine on Windows XP. A quick test on Vista made me aware that this was also occurring on that OS.

Obviously, by coincidence, the answer:

  • if the extension of the filemane you provide is the same as the last one in the filter criteria, the name is not cleared
  • if you do not provide an extension on the filename, the name is also not cleared
  • if you supply an extension that is either not in the list of wildcard extensions or not the last one, the name is cleared (in XP the full name would be enclosed in double quotes)

To replicate this behaviour do the following:

  1. In a virgin workbook open the code pane for ThisWorkbook
  2. Paste the following code:
    Public Sub TestGetSaveAsFileName()
    Dim sName As String
    Dim sFilter As String
    sFilter = "Excel Macro Enabled filter,*.xlsm;*.xlsb;*.xls,All Files,*.*"
    sName = "File name (no ext) (filter is xlsm & xlsb)"
    Debug.Print "1) " & Application.GetSaveAsFilename(sName, sFilter, 1, "Test")
    sName = "File name (xlsm) (filter is xlsm & xlsb).xlsm"
    Debug.Print "2) " & Application.GetSaveAsFilename(sName, sFilter, 1, "Test")
    sName = "File name (xlsb) (filter is xlsm & xlsb).xlsb"
    Debug.Print "3) " & Application.GetSaveAsFilename(sName, sFilter, 1, "Test")
    sName = "File name (xls) (filter is xlsm & xlsb).xls"
    Debug.Print "4) " & Application.GetSaveAsFilename(sName, sFilter, 1, "Test")
    End Sub
  3. Run the code and watch the file’s name in the dialogue.

I presume this bug is not caused by Excel 2007 since the behaviour is consistent on XP but not on Vista nor on Windows 7, so for me this is a Vista/7 bug. I ran a quick test with Excel 2003 on Vista and 7 and there also the names disappear when the above conditions are met so this confirms it.

Now the strange behaviour.

According to the documentation (Excel 2000 up to Excel 2010) the GetSaveAsFileName() defines the following for the FileFilter parameter:

This string passed in the FileFilter argument consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters, text and addin: “Text Files (*.txt), *.txt, Add-In Files (*.xla), *.xla”.

To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, “Visual Basic Files (*.bas; *.txt),*.bas;*.txt”.

I have found that this is not true. If you use a file filter in the dialogue’s label, this file filter gets priority over the real file filter(s). For example

“Text Files (*.pdf), *.txt;*.log, Add-In Files (*.doc), *.xla”.

will show a dialogue filtering on either “*.pdf” (from first label) instead of “*.txt” or “*.log” or on “*.doc” (from second label) instead of “*.xla”. Not convinced? Try by yourself! Solution? Do not use wildcard extensions in the label of the filter criteria.

On Microsoft XP and 7 the wildcard extension will automatically be appended to the filter’s label. However, on Vista the wildcard extensions are never displayed, so if you open the dialogue then you can only guess what wildcard extension is being applied. I have tried not hiding known extensions but on Vista I was nevertheless unable to see what wildcard extensions were associated to each entry.

A programmer friend of mine confirms that the bug and the strange behaviour are caused by the Common Dialog control of Windows Vista and Windows 7 which is used by the Excel function. He also mentioned a third one (which I have not seen yet, could related to a localised version of Windows) being that under circumstances the wildcard extension is appended to the label and therefore appears twice on the entry. For instance this filter “Text Files (*.txt),*.txt” would display as “Text Files (*.txt) (*.txt)”. This strengthens the recommendation for not putting any wildcard expressions in the label.

To avoid the bug mentioned in the beginning of my post, i.e. the filename that disappears when not with the correct extension, I recommend splitting filter definitions with multiple wildcard expressions into many single definitions with only one wildcard extension each.

This would be incorrect:

“Excel Macro Enabled files,*.xlsm;*.xlsb;*.xls,All Files,*.*”

This would be correct:

“Excel Macro Enabled document,*.xlsm,Excel Macro Binary document,*.xlsb,Excel 2003 document,*.xls,All Files,*.*”

It’s a little cumbersome but effective. Obviously, if you use filter preselection on the dialog, make sure the correct one is selected when setting the filename.


September 6, 2011

Excel 2007 stops working using the Object Browser on Windows 7

Filed under: Excel — dutchgemini @ 3:19 pm
Tags: , , , ,

I encounter a severe crash in Excel 2007 while browsing my project’s objects in the Object Browser. This crash occurs only on my Windows 7 PCs (Vista not tested) and does NEVER occur on XP.

These are the steps to reproduce it:

  • Start Excel
  • Open the VB-IDE
  • Go to Tools | References
  • Click Browse… and add COMDLG32.OCX
  • Click Ok
  • Open the Object Browser (F2)
  • Keeping <All Libraries> search for “Fonts“; you will see that “FontsConstants” is listed from MSComDlg
  • Open the Library Selector and select MSComDlg

At that point, Excel instantly crashes (“Excel has stopped working”).

In the Event Viewer I have the following data:

Faulting application name: EXCEL.EXE, version: 12.0.6425.1000, time stamp: 0x49d64dd6
Faulting module name: OLEAUT32.dll, version: 6.1.7600.16385, time stamp: 0x4a5bdaca
Exception code: 0xc0000005
Fault offset: 0x00021e1a
Faulting process id: 0xed0
Faulting application start time: 0x01cc6c9c11c61a50
Faulting application path: C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
Faulting module path: C:\Windows\system32\OLEAUT32.dll
Report Id: fcab5220-d88f-11e0-98d2-0003ffc68011

What turns me mad is that I can use the objects from MSComDlg in my code, IntelliSense works normally, and the code runs without problem.

I have deleted EXD files, re-registered components, ran cleaners, etc. without success. I hunted all possible sites and resources for a clue but nothing, nothing at all.

Does anyone have a tip?


January 20, 2011

Custom Excel 2007 Ribbon and VBA

Filed under: Excel,Programming,VBA — dutchgemini @ 4:04 pm
Tags: , , , ,

This page is not yet another treaty on the “how to” of the Office ribbon —many books have been written on this subject— but more a collection of my experience with and pitfalls of the Fluent Ribbon in combination with VBA.

I have (finally, can we say that?) started programming the Excel 2007 ribbon. Since now, I have been busy building Add-Ins with VBA using old-style CommandBars and was fairly happy with it, mainly because most customers for one reason or the other were still using Office 2003. Nevertheless, Microsoft had made to move to the so much-advertised Fluent Ribbon at the end of 2006, so I thought the time was finally ripe to start building my own ribbon, also because I didn’t like the visual of my CommandBar on the Add-Ins tab of Excel 2007.

The first aim of this exercise was to understand the Fluent Ribbon and how I could customise it. I bought books on the subject and scanned the Internet. Finally I decided to keep the Add-In as it was so it could continue to work on every PC running whatever release of Excel, and elected to create a Ribbon interface that would copycat my own CommandBar.

It took me finally full 2 weeks to get to a decent result and during that time I have learned a lot of things about the Fluent Ribbon, about its customisation possibilities and the interaction with VBA. Honestly, I am not at all impressed by the Ribbon and I think Microsoft has still a lot of work to do. The part I like less is the interface for customisation using only VBA: it is simply not possible.

Just to give you an idea about what you should do to start customising the Ribbon (copied from http://msdn.microsoft.com/en-us/library/aa338202.aspx and adapted for Excel):

  1. Create a folder on your desktop named customUI.
  2. Create the customization file in any text editor by writing XML markup that adds new components to the Fluent UI, modifies existing components, or hides components. You can use the XML markup from the previous example, to test the behavior. Save the file as customUI.xml (or any other name) in the folder you just created.
  3. Validate the XML markup against your custom Fluent UI schema (optional).
  4. Create a document in Excel, and then save it as an Office Open XML Formats file with the .xlsm extensions. File name extensions for files that contain macros have an “m” suffix. These files can contain procedures that can be called by Ribbon extensibility commands and controls.
  5. Exit Excel.
  6. In Microsoft Windows Explorer, add the file name extension .zip to the document file name, and then double-click the file to open it as a compressed folder.
  7. Add the customization file to the container by dragging the customUI folder from the desktop to the compressed folder.
  8. Drag the _rels folder to the desktop. A folder named _rels containing the .rels file appears on the desktop.
  9. Open the new folder, and then open the .rels file in a text editor.
  10. Between the final <Relationship> element and the closing <Relationships> element, add a line that creates a relationship between the document file and the customization file. Ensure that you specify the folder and file names correctly (the Id attribute supplies a unique relationship ID for the customUI—its value is arbitrary).
  11. Save the .rels file.
  12. Drag the .rels file from the desktop to the _rels folder in the compressed file, replacing the existing .rels file.
  13. Remove the .zip extension from the container file.

Have you noted that, apart from step 4, all other steps are actually performed outside of Excel?

And I have not yet spoken about the VBA CallBacks needed in your code. The CallBacks are used by Excel to obtain customisation information about each control. Since Excel provides different types of ribbon controls it also calls these CallBacks with varying parameters, some ByVal, others ByRef. You need to find which parameters are passed and what value they expect back.

Issue 1: You cannot create ribbon elements dynamically in VBA

It is not possible to create ribbon elements dynamically via code as with Office 2003, where you could manage your own CommandBars and CommandBarButtons.

In Excel 2007 each ribbon element (Tab, Group, Buttons, etc.) needs to be defined statically and embedded in the Excel document using a specially crafted XML file and with quite a few manual steps, including renaming and modifying contents of the Excel document —factually a ZIP with the XLSM or XLAM extension.

Once you have embedded your ribbon definition in the Excel file then you can use VBA to control size (small or large), labels, visibility, enabled and toggled status but other things, like controlling the position on the ribbon, is not possible.

Issue 2:  Assigning custom icons dynamically is cumbersome

Excel offers the possibility to change the image of a ribbon element at run-time via a “getImage” VBA CallBack. The VBA CallBack has a ByRef parameter that allows you to specify the image you want to use. This parameter accepts 2 types of value: string or object.

When you specify a string value (eg. “FileSave”) then Excel looks up the value in the list of internal images (the so-called Mso images) and draw the corresponding icon on the ribbon element. As long as you want to use the  Mso images then there is little problem. If, however, you do not like the internal images and want something of your own mill then you are in trouble.

You can embed images in the Excel workbook but Excel can only “see” these images when you are using them statically on the ribbon element. If you want to use the VBA CallBack to get another custom image on the ribbon then you must provide, for the ByRef parameter, an object in IPictureDisp format. VBA’s LoadPicture() returns such object but it can’t read all picture formats (the recommended PNG for instance are excluded) and it also needs a solid file to read from. LoadPicture() cannot extract the image file from the Excel document, which means that you must find a way to make your custom icon available in solid form at run-time (either during installation or when booting Excel).

And if your icon file is in a non-readable format for LoadPicture, then you must use the GDI+ to convert the picture for you (look at LoadPictureGDI on Stephen Bullen’s Excel Page).

Issue 3:  If you use custom icons you may receive errors when trying to Customise the QAT

When your ribbon tab uses custom images (static or dynamic makes no difference) then as soon as you try to customise the Quick Access Toolbar (QAT) you will get prompted with an empty error for each non-Mso image on your ribbon:

Error message when customising QAT

This error occurs when your custom image is not 16×16 pixels. To obtain this error I have used, because I needed “large” buttons, only images with 32×32 pixels. On a few ribbon items I used 16×16 images and these were not triggering errors.

Issue 4: Tag property of Ribbon elements pointing to item with idMso not propagated

When you need a standard Excel ribbon element on your custom tab, forget about the Tag property. I have tried using it in my VBA CallBack but unfortunately the value was not propagated. I now check the Id property instead, which contains the internal Id (eg. “FileSave”).

Issue 5: You cannot hide item Separators dynamically at run-time

If you use separators in ribbon groups and want to hide them at run-time because the element at its right is being hidden, forget about it. There is a bug in Excel 2007 that does not permit setting the visibility of a separator with a VBA CallBack. If have reverted to a using a Ribbon Label made up of 6 non-breakable spaces (ASCII 255) that I show or hide upon need.

Issue 6: If you use a SplitButton don’t use the ‘getVisible’ CallBack of the button’s items

A SplitButton is made of a Button and a Menu. If you click the button its ‘onAction’ executes, and when you click the menu, a pop-up menu is displayed offering other options. The Paste button on Excel’s Home tab is such button.

Technically, the SplitButton contains a Button ribbon element and a Menu ribbon element. You are allowed to control the visibility of the SplitButton via VBA CallBack but it is not allowed to assign a VBA CallBack to control the Button.

Issue 7: The “List” items use a different Option Base

On the old-style CommandBar, controls that contain lists such as the Combo Box start with their first entry at 1 (one). In the new-style Ribbon, the first element has index 0 (zero).

Recommended Tools and Tips

Microsoft does not offer an integrated solution for editing the ribbon but luckily the community has made available tools that bring you really much ahead. I recommend the following free-as-beer products:

The Custom UI Editor is “endorsed” by Microsoft. This tool is fast, proofs the customisation of your XML against the schema. Lacks a visual designer and the entire definition is hand-written, which does not offer a guarantee that it will work in Excel, as a matter of fact mine crashed regularly until I discovered that one of the custom image ID’s started with a number (“3DIcon” — after I changed it to “_3DIcon” everything worked again). It also keeps track of the custom image resources that you are using. However when you remove one of such resources the tool does not properly remove all the links and you may need to manually correct the “customUI.xml.rels” file located under the “customUI/_rels” folder in the Excel document.

The RibbonX Visual Designer made by Andy is an excellent Office Add-In (sits on the Developer tab) enabling you to “design” the ribbon customisation using a tree-like structure. It also offers “preview”. I have found some small problems with this tool, such as no validation of XML schema, deleting entries crashes the Add-In, regularly I get Zip/UnZip errors and once in a while Excel disappears but I have learned to Save regularly. Overall this tool has greatly supported my transition to the Ribbon interface and I will always grateful to Andy for making this tool available to us.

Both tool generate the code for the VBA CallBacks in a separate window. You need to copy this code and paste it in the file’s code module. However, if you add more ribbon elements in the above tools you can’t copy the entire VBA CallBacks to the code module anymore, but need to add only the missing CallBacks.

It would be nice if —and as an Excel Add-In Andy’s tool could do this— the VBA CallBacks could be generated automatically in the file’s code modules, closing the loop from this point of view.

Caution: do not use these tools at the same time on the same files. Sounds strange, but I had the same file open in the UI Editor and in Excel with the RibbonX Designer. At some point I saved the file in Excel and —without reopening it in UI Editor— I started making some changes which inevitably destroyed the modifications I had saved just minutes earlier in Excel.

NotePad++ is an excellent replacement for NotePad and has the necessary XML syntax highlighting. I also recommend IcoFX (www.icofx.ro) if you want to make fancy looking icons.

Tip 1: Names of VBA CallBacks

I recommend creating a single VBA CallBack procedure for each property you want to control at run-time for similar types of ribbon items. For instance, the visibility CallBack for each button ribbon item I use is named “AllButtons_getVisible” and in this procedure I use a Select Case based on the control’s Id.

This also helps keeping your VBA code module manageable and small.

Tip 2: Create a dummy Code Module with all VBA CallBacks to import in your document

I have available a VBA code module (.bas) with all possible callbacks already in place, using the indications in Tip1.  Each CallBack returns a default value that allows the ribbon to become visible and the ribbon elements to be evaluated in a proper manner. For instance, all Boolean CallBacks receive True, all textual ones receive a proper text such as “Label”, “ToolTip”, “SuperTip” and images receive “HappyFace”

When I want to create a new Ribbon, I import this code module in my macro-enabled Office document before I start working on the customisation. In this way, I can immediately preview the ribbon without error messages about missing procedures being generated.

Before I finalise the customisation, I use a Debug.Print "name_of_procedure" statement to verify whether a particular routine is being used or not and remove the unused ones.

You may also create a template file with all the necessary stuff inside and use that as a starting point.

Tip 3: Wrap a Ribbon Object made in Excel 2007 around your Excel 2003 style CommandBar

If you have an Add-In designed for pre-2007 Excel also, that creates its own CommandBar, do not embed the ribbon customisation in this file but create a separate Excel macro-enabled workbook containing the ribbon customisation. In this workbook’s Open event, hide the original CommandBar, and use the ribbon buttons’ onAction property to ‘click’ the corresponding button on the old-style CommandBar.

Tip 4: Use Windows Explorer’s Zip functionality

I recommend using Windows Explorer’s compression and decompression functionality to pack or unpack your Office documents. This functionality is normally available on the right-click contextual menu.

Explorer uses the same compression routines —and thus compression method— used by Excel. This will minimise the chances that your document gets “corrupted” by an “unknown” algorithm.

This I learned the hard way after I used IZArc (I’m a addicted freeware junkie) to re-compress a folder structure to an Excel 2007 file and, as you might expect, Excel didn’t like it too much. Probably if I’d used different parameters in IZArc the problem would never popped up, but at least now I know from the beginning that this is a potential for problems.

Tip 5: Keep it simple

In my first attempt I set VBA CallBacks with individual names for all items on my custom Ribbon. This generated a massive 2300+ line code module and I totally lost overview. Without doubt quite an achievement, and the code still didn’t do anything!

Now my entire module with comments and shared VBA CallBacks scores a little above 800 lines of code.

Tip 6: Re-think your CommandBar

The Ribbon offers a new way to present your CommandBar buttons. I think this is probably the most but only appreciated feature of this object.

Tip 7: After invalidating the *entire* Ribbon, issue a ‘DoEvents’

In Tip #3, I recommend wrapping the ribbon around the CommandBar. During the development, working with an XLSM workbook, everyhing worked without problem. The various tabs, panels, groups, labels, etc., are read at run-time from the equivalent values of the hidden, Excel pre-2007, CommandBar.

I created the final (password-protected) XLAM of my Ribbon wrapper and started a new round of tests and at that point Excel locked up almost completely. Although Excel was flashing on the TaskBar, I could not get control back, the only possibility was killing it the hard way via Windows Task Manager.

Then I opened and unlocked the Add-In in the VBIDE before running the test and there it was: the same blank VBA error message shown in Issue #3. After some googling with “excel 2007 blank vba error message” I was able to focus the problem. Excel refreshes the Ribbon in an asynchronous thread and probably during this thread it cannot access the [hidden] CommandBar it is reading from. This results in the blank VBA error message. Unfortunately Excel appears to popup this error while running macro code and when the VBIDE is *not* open, the message is shown in an unaccessible and invisible layer.

The solution that worked for me was adding a ‘DoEvents’ statement right after invalidating the entire Ribbon:

' In a Code Module of the Ribbon Wrapper
Public mRibbonUI As Office.IRibbonUI

Public Sub InitialiseAddInsRibbon(ribbon as IRibbonUI) ' Initialise CallBack
    Set mRibbonUI = ribbon
End Sub

Public Sub RefreshAddInsRibbon()
    ' Code for refreshing the Ribbon.
On Error Resume Next

    ' May not exist yet.
    If mRibbonUI Is Nothing Then Exit Sub

    ' Invalidate the entire ribbon.

    ' --------------------------------------------------------------------- '
    ' Without this next statement, Excel can run out-of-sync, popup empty '
    ' vbCritical errors, lockup and/or destroy the custom ribbon. '
    ' --------------------------------------------------------------------- '
    ' --------------------------------------------------------------------- '
End Sub

Nevertheless, re-thinking the layout of your CommandBar, of the buttons (and icons) can generate a very positive return on the acceptance of the application by your customers. Your customers don’t see and likely do not understand the triple salto’s your code makes but they will absolutely be impressed by how it presents. A well-designed look of an application often let people presume that the same cure was dedicated to the underlying code (this is no guarantee, the Ribbon itself demonstrates that this can also be the other way around).

Honestly, would you use or buy an ugly-looking software even though it promises miracles? I don’t think so, why would software vendors otherwise put so much emphasis on screenshot?


January 5, 2011

VBA Run-time error ‘Automation error Library not registered.’

Filed under: Programming,VBA — dutchgemini @ 4:49 pm
Tags: , , , ,

If you encounter the  VBA Run-time error ‘Automation error Library not registered.’ then between your VBA project’s references there is a reference to a file that is “MISSING”.

This is the case when for example a DLL, OCX or another VBA project used while developing in your application is not installed on the computer the application is being used. Obviously, you should never distribute applications without making sure that the components used are available on the target computers, but there are cases in which VBA takes care of resolving the problem automatically, one example are the Microsoft Office applications, where each version installs in a separate folder containing a number (for instance for Office 2003 (Release 11) you have C:\Program Files\Microsoft Office\Office11)

In general, each reference is correctly mapped to the right -and probably also most recent- component, since the GUID of each application does not change. But there are cases where the component does not correctly remap (because the GUID has changed or because it is not available). If this occurs, then you will see such “MISSING” reference.

To correct the problem, open the VB-IDE, go to your project references (Tools -> References), either scroll the list and put or remove a checkmark in front of the desired component or browse for a file on your disk.

Ah, if you have access to the project, otherwise you’re out of luck.

Tip: before you distribute your application, run a compile (Debug -> Compile). If there is a missing reference, VBE may make this problem evident by issuing a compile error “Can’t find project or library” on a function that you know is correct (I had that on the String() function, not to confuse with VBA.String()). When you close the compiler error, VBE will open the references dialog for you.

When the ‘Automation error Library not registered.’ triggers on registered libraries

The funny thing is that a missing reference impacts on your code when addressing the other references in the project. Suppose you want to set a reference to “Visual Basic for Applications” then you would write the following statement (“VBA” is the codename for this reference):

Dim oReference As Object
Set oReference = ThisWorkbook.VBProject.References("VBA")

Using a named index with missing references triggers the error. However, if you use a numeric index to obtain a reference object, the error does not trigger.

Dim oReference As Object
Set oReference = ThisWorkbook.VBProject.References(1) ' Usually VBA is number 1

Even next code may trigger the error. Note the use of ‘may’ and not ‘will’. This is because the ‘.Name‘ property does not exist for missing references, so as long as the item’s counter is smaller than the missing reference item you are indeed able to query this property. The code stops running as soon as ‘lItem’ matches the index of the missing reference:

Dim oReference As Object, lItem As Long
For lItem = 1 To ThisWorkbook.VBProject.References.Count
    If (ThisWorkbook.VBProject.References(lItem).Name = "VBA") _
    Then Set oReference = ThisWorkbook.VBProject.References(lItem)
Next lItem

If you really want to play safe, use the ‘.Guid‘ property. This property does always exist, but is also means that you need to know it:

Dim oReference As Object, lItem As Long
For lItem = 1 To ThisWorkbook.VBProject.References.Count
    If (ThisWorkbook.VBProject.References(lItem).Guid = _
       "{000204EF-0000-0000-C000-000000000046}") _
    Then Set oReference = ThisWorkbook.VBProject.References(lItem)
Next lItem

Of course, you can always iterate through the references using For…Each, but remember to use the ‘.Guid‘ property:

Dim oReference As Object
For Each oReference In ThisWorkbook.VBProject.References
    If (oReference.Guid = "{000204EF-0000-0000-C000-000000000046}") _
    Then Exit For
Next lItem

The Guid is needed when you want to programmatically add references (the 0,0 at the end instructs VBA to load the most recent reference for this Guid):

Dim oReference As Object
For Each oReference In ThisWorkbook.VBProject.References
    If (oReference.Guid = "{000204EF-0000-0000-C000-000000000046}") _
    Then Exit For
Next lItem
If oReference Is Nothing _
Then Set oReference = ThisWorkbook.VBProject.References.AddFromGuid("{000204EF-0000-0000-C000-000000000046}", 0, 0)

You can get a list of the Guids used by your application by iterating the collection of references.


Create a free website or blog at WordPress.com.