Questions similar to this appear on the MicroStation Programming Forum. This problem appeared in the VBA discussion group.

Q  Sometimes you want to write VBA code in another application, such as Microsoft Excel™ or Word™. Equally, you might want to write MicroStation^® VBA code that uses the methods of another application's object model.

• How do I use MicroStation VBA in another application?
• How do I use another application's VBA object model in MicroStation VBA?
• My Excel™/Word™/Access™ VBA code can't find a MicroStation DLL. What should I do?
• How do I use other Windows controls in MicroStation VBA?

A  The Windows operating system delivers technology that let's us share code easily in VBA. The Common Object Model (COM) framework lets us use one application's object model from another application. One of Microsoft's marketing terms for this is ActiveX™.

Put simply, you can write MicroStation VBA code that uses Excel's VBA methods, and you can write Excel VBA code that uses MicroStation's VBA methods. These Object Libraries are extensive. They are provided by product vendors including Bentley Systems and Microsoft Corporation, and there are also plenty of component vendors who provide additional functionality independent of any application.

You can also write your own ActiveX component, using .NET (or another language that is COM-compatible). An ActiveX component is a COM server by another name. When you create a COM DLL it is automatically an ActiveX component, available for referencing by other application code.

Microsoft Visual Studio helps you to write a COM server using your favourite .NET language. It's easiest with C#. We've written elsewhere about COM Server Development using .NET.

An ActiveX component can provide functionality, a user interface, or both. Those ActiveX components that provide only a user interface are called controls and are supplied in Control Libraries. Often, a control library has the file extension .ocx rather than .dll, which is why they are sometimes referred to as OCXs.

Object Libraries

VBA provides common base functionality that is customised by a software vendor. MicroStation VBA has extensions for 3D graphics provided by Bentley Systems. Excel VBA has extensions for spreadsheet manipulation provided by Microsoft. Because of the common foundation, and apart from those custom extensions, MicroStation VBA and Excel VBA are identical.

You can reference another library from the VBA editor using the menu Tools|References…. When you select that menu, VBA pops the References dialog …

In this example, I'm referencing the Microsoft Scripting Runtime library. The Scripting Runtime library is, by the way, an extremely useful set of objects for manipulating the Windows file system, including files and folders. If you want to reference, say, Excel from MicroStation VBA, scroll through the list in the References dialog to find the Microsoft Excel nn.0 Object Library (where nn is the version of Excel installed on your computer).

Calling MDL Functions in VBA

When developing, you have to be aware of your environment. MicroStation VBA provides a huge library of functionality, but the MicroStation Development Library (MDL) provides even more (in fact, much of VBA's functionality is provided by underlying MDL).

On rare occasions, you may need to borrow a function from MDL to use in your VBA code. For example, there is no VBA method that tells you if an element is a grouped hole. However, MDL does provide a function. To use an MDL function in VBA you must first Declare it (Declare is a VBA keyword) …

'  MDL function declarations
Declare PtrSafe Function mdlElmdscr_isGroupedHole Lib "stdmdlbltin.dll" ( _
    ByVal groupEdP As LongPtr) As Long

For the purpose of this article, the signifant part of that declaration is the phrase Lib "stdmdlbltin.dll". That tells VBA that the MDL function is provided by a library in file stdmdlbltin.dll. If you search your \MicroStation folder, you will find that file and a good few other DLLs that are part of MicroStation's installation.

MicroStation VBA is provided by ustation.exe or one of the DLLs that lives in your MicroStation installation. By default that is C:\Program Files\Bentley\Program\MicroStation. When you run a MicroStation VBA it starts in the MicroStation folder. What happens when it finds an MDL function declaration that specifies a DLL? It uses the rules of Windows to search for that DLL:

1. look in the same folder as the .exe
2. look in folders in the %PATH% environment variable

VBA is implemented by ustation.exe and the MDL DLL is in the same folder. VBA locates the DLL at run-time, and your code executes as expected …

' ---------------------------------------------------------------------
'   This VBA function is a wrapper around the MDL function mdlElmdscr_isGroupedHole.
'   mdlElmdscr_isGroupedHole must be declared at the beginning of your VBA module.
' ---------------------------------------------------------------------
Public Function IsGroupedHole(ByVal oElement As Element) As Boolean
    IsGroupedHole = 0 <> mdlElmdscr_isGroupedHole(oElement.MdlElementDescrP)
End Function

The wonders of COM let you reference MicroStation's object library from VBA code written for another application. First, you reference the required object library, then declare an object of the required type in your VBA code …

'  Reference the MicroStation V8 COM library
Dim oMicroStation   As MicroStationDGN.Application

As you probably noted in the Reference Dialog, VBA knows where the library is located. So when you write the VBA statement above, it's shorthand for telling VBA, "I want you to use the COM library you will find in C:\Program Files\Bentley\Program\MicroStation\ustation.exe."

Follow the PATH

Excel is installed somewhere like C:\Program Files\Microsoft Office\OfficeNN\ (where NN is the version of Microsoft Office installed on your computer). Excel VBA is provided by the .exe or DLLs that live in that folder. What happens when your code that uses the MicroStation VBA object uses a MDL function declaration that specifies a DLL? It uses the rules of Windows to search for that DLL, and of course doesn't find it. The Excel folder is not the folder where MicroStation is installated, nor is MicroStation in your %PATH% environment variable.

By now, you should have worked out the solution: add the MicroStation folder to the list of folders in the Windows %PATH% variable. Now, when Excel VBA attempts to find a MicroStation library it knows where to look.

"How do I add a folder to the Windows %PATH% variable?", I hear your anguished plea. Follow these steps …

1. Right-click My Computer on the Windows desktop, or choose the System applet in the Windows Control Panel.

   The System Properties dialog appears

2. Click the Advanced tab
3. Press the Environment Variables button

   The Environment Variables dialog appears

4. In the top list, scroll until the PATH variable is displayed

   The PATH variable may not be defined, and may be absent until you define it

5. Press the Edit button (or the New button if PATH is not defined)
6. Append the path to your MicroStation folder in the Variable value text box

Control Libraries

Control Libraries provide more user interface objects than are delivered with VBA. For example, the ListView control delivered with VBA is simpler but less capable than the Windows ListCtrl control that we are familiar with today. The ListCtrl control is not delivered with VBA, but is almost certainly installed on the version of Windows you are using right now.

Control Libraries are similar to application object libraries. They are COM DLLs that implement a user interface, often with a .ocx extension.

You can reference a Control Library from the VBA editor when two conditions are met:

• You are editing a VBA UserForm
• The controls palette is visible and active

With those conditions met, you can reference a Control Library using the menu Tools|Additional Controls…. Selecting that menu pops the Additional Controls dialog …

In this example, I'm referencing two controls developed by Bentley Systems. The Color Palette and Color Picker controls provide VBA-compatible versions of the palette and picker that you are familiar with as a MicroStation user.

By the way, if you're interested in using those controls in your MicroStation VBA projects, there's more information here.