Questions similar to this appear on the Bentley Discussion Groups. These problems appeared in the VBA discussion group.

• How do I do xxx in Excel with MVBA?
• How do I copy data to/from Excel with MVBA?
• How do I display an Excel worksheet in MicroStation using MVBA?
• Are there any text books that deal with Excel VBA programming?

Bitness: 64-bit or 32-bit?

MicroStation CONNECT is a 64-bit application. It runs only on 64-bit versions of Windows. If you want to communicate with other applications — Office, for example — using VBA, those applications must also be 64-bit.

You can't communicate, using the methods described here, with an Office 32-bit application using MicroStation.

Q How do I do xxx in Excel with MVBA?

A Like MicroStation, Microsoft Excel™ has its own implementation of Visual Basic for Applications. Excel has included VBA for many years over several versions. Even today, with Microsoft tempting us with .NET, Excel (and each of the other Microsoft Office applications) continues to include VBA.

With Excel installed on your computer, you can create a reference to its object library (the same applies to any other application that implements VBA).

With MicroStation's VBA Interactive Design Environment (IDE) open, take the following steps …

1. Use the Tools|References menu to pop the References dialog
2. Browse the list to find your installed version of Microsoft Excel
3. Click the check box to instruct VBA to reference this library
4. Click OK

Note:  In the screenshot above, I'm creating a reference to the Excel 11 COM server. It's likely that you're using a later version of Excel, and your COM server may be Excel 12 or Excel 14 or later.

Once you've referenced the Excel.Application, you have access to all its properties and methods. With the IDE open, press the F2 key (or Edit|View Object Browser) to pop VBA's Object Browser dialog. In the Object Browser, you can focus on the properties or methods of any referenced object …

Q How do I display an Excel worksheet in MicroStation using MVBA?

A You could write some code around something like a DataGrid component, and make it look like an Excel worksheet. You still have the problem of importing and exporting data from Excel.

Q How do I copy data to/from Excel with MVBA?

A Excel has a hierarchy of objects, starting with the Workbook, which corresponds to an Excel file, through the Worksheet, to the Range. It is the Range object that is the gateway to working with Excel data. A Range can refer to a single Excel cell or a range of cells. The range can be a row, column or block of cells, up to an entire worksheet.

I'm not providing an Excel VBA tutorial here: there are plenty of developers' web sites that can help you.

Q Are there any books about Excel VBA programming?

A There are some text books for Excel VBA programmers mentioned here.

Q How do I do xxx in Excel with MVBA?

A Here's a fragment of code that show how to create an Excel.Application object reference and use it to open a workbook …

Option Explicit

' ---------------------------------------------------------------------
'   Example MicroStation VBA code to create and use an
'	Excel application object
' ---------------------------------------------------------------------
'
Sub ExampleExcelReference()
   Dim oExcel As New Excel.Application

   '   Get an Excel workbook
   Dim oWorkbook As Excel.Workbook
   Set oWorkbook = oExcel.Workbooks.Open("your-workbook.xls")
   Debug.Assert Not oWorkbook Is Nothing
   Debug.Print "Using Excel workbook " & oWorkbook.Name

   '   Get the first worksheet in the Worksheets collection
   Dim oWorksheet As Excel.Worksheet
   Set oWorksheet = oWorkbook.Worksheets.Item (1)
   Debug.Assert Not oWorksheet Is Nothing
   Debug.Print "Using worksheet " & oWorksheet.Name

   '   We finished: set the Excel reference to nothing
   Set oExcel = Nothing
End Sub

Debug Statements

In the code above, I've added some Debug statements. Debug statements help you to ensure that you're coding is working as expected. Your users don't know that they are there.

1. Debug.Print displays your text in the IDE's Immediate window. Only you, the VBA developer, can see that window
2. Debug.Assert tests the following statement for truthfulness at run-time. If the statement is True, then execution stops with an appropriate message. In the example above, I'm testing the oWorkbook object: it should be a valid reference, and the Debug.Assert verifies my assumption. Programmer's are good at making fallacious assumptions, and Debug.Assert keeps us in check

Questions

Post questions about VBA to the MicroStation Programming Forum.