OLE EXPERTby Keith R. PleasControl Excel 5.0 With OLEAutomationIicrosoft Excel version 5.0 provides de-C7=Loan_amountvelopers with a rich library of program-THE OLEC8=Annual_interest_ratemable objects. In previous versions ofC9=Term_in_yearsExcel, much of this functionality was there butAUTOMATIONC10=Payments_per_yearunexposed to applications outside Excel. WithC14=Calculated_paymentExcel 5.0, Microsoft has implemented an OLEINTERFACE ISAutomation interface that makes it possible toHereÆs a code sample that openscreate and manipulate objects inside Excel fromSTRONGER, FASTER,AMORTIZE.XLS, plugs in values for the vari-other applications. This feature is especially attrac-ables, and pops up the calculated payment in ative to Visual Basic programmers because it allowsAND MORE POWERFULmessage box (it is also straightforward to bringaccess to ExcelÆs library of objects, which is prob-back the whole table, if desired):ably second to none for building spreadsheet appli-THANcations.Dim APPXL As objectPerhaps youÆre wondering whether the OLEDDE EVER WAS.Dim XL As objectAutomation interface is capable, and if youÆll runDim ws As objectinto any of the performance issues that plagueddevelopers who tried in the past to integrate appli-Set APPXL = GetObject(, "Excel.Application")cations using DDE. Well, the interface is the methodSet XL = APPXL.Applicationby which VBA is integrated into its host application and the answer is thatXL.WorkBooks.Open "F:\TEMP\AMORTIZE.XLS"the OLE Automation interface is truly capableùitÆs a stronger, faster,Set ws = XL.ActiveSheetand more powerful interface than DDE ever was.ws.Range("Loan_amount").Value = 100000ws.Range("Annual_interest_rate").Value = .075EXCEL REGISTRATIONws.Range("Term_in_years").Value = 30You may already be familiar with the Windows registration databasews.Range("Payments_per_year").Value = 12where OLE objects are registered. This file, which you can view using theMsgBox Format$(ws.Range("Calculated_payment")._REGEDIT.EXE utility included with Windows, stores a variety ofValue, "currency"), , "Payment"information of interest to programmers wishing to integrate applicationsXL.Workbooks(1).[Close] (False)using OLE and OLE Automation (while the list is useful, it is alsonecessary to understand how objects are contained by and accessedSet ws = Nothingthrough other objectsùsee Figure 1).Set XL = NothingWhile the Windows registration database will be a topic by itself inSet APPXL = Nothinga future column, there are a couple of points that you should be aware ofnow. First, under 16-bit Windows the registration database is limited toThere's one line in this sample I'd like to draw your attention to:64K for keys and 64K for values. Excel 5.0 registers 130 different objectsfor exposure to OLE Automation and Microsoft Graph 5.0 (shipped withXL.Workbooks(1).[Close] (False)Excel 5.0) registers another 38; together they consume 20K of that heap.These entries are needed to do type checking for early-bound methodEven though the workbook was opened via ôWorkBooks.Open,ö itinvocations performed in the VB (and VBA) design environment.canÆt be closed from VB via a corresponding ôWorkBooks(1).Close.öSecond, there are a couple of possible syntaxes for referencing ExcelTrying to do this results in the message ôMethod not applicable for thisin CreateObject and GetObject functions: ôExcel.Application.5ö willobject.ö The problem is that, in VB, the Close method is only applicablealways give you Excel 5; ôExcel.Applicationö will always give you theto a database. To force VB to pass the Close method on to Excel fordefault VBA-aware installation of Excel on the system, which wouldevaluation, enclose it in brackets. In cases that require bracketsùorusually be the most recent version.example, using the Show, AddItem, and RemoveItem methodsùit isThis will only be useful after Excel version 6.0 ships, but should beoften a requirement that the parameters be enclosed in parentheses.kept in mind for forward compatibility.EXCEL DIALOGSUSING AN EXCEL SPREADSHEETYou are probably already familiar with ExcelÆs spreadsheets. Figure 2Keith R. Pleas is an independent system consultant. He is the author ofshows the AMOR-TIZE.XLS worksheet included with the Excel samples.the forthcoming book, Implementing DDE & OLE, from Ziff-DavisThe important variables (and their cell locations and predefined rangePress. He can be reached via CompuServe at 72331,2150, or via thenames) in calculating an amortization table are:Internet at keithp@curlew.wa.com.Visual Basic ProgrammerÆs Journal FEBRUARY/MARCH 1994 85OLE EXPERTIf you can build your user interface within Excel, why work with VB atVB, hereÆs a list of the major restrictions and limitations of using Excelall? Well, the differences between VB and VBA are most apparent in thedialogs for your user interface.user interface. In the first release of VBA, there are no forms or control; IÆm not trying to discourage you from using them, but you mightlikewise, there is no place to plug in the add-on custom controls that areappreciate knowing about the roadblocks before you run into them:so much a part of VB.And if you have no controls, it stands to reason you have no controlòAll dialogs are application modal.events. Instead, VBA (again, this is only in the first release) program-òExcel doesnÆt respond to DDE or OLE messages when a dialog ismatically builds the user interface using the host applicationÆs tools.showing or a menu is dropped down.Instead of VB forms, you have Excel dialogs that are constructed onòYou are limited to one dialog per sheet (though a workbook candialog sheets and can contain a variety of traditional Windows controls.have many).And you can (sort of) write event code and attach it to the ôcontrols,öòMoving a control in code (must be done before the dialog is shown)though the method for doing this is nowhere near as straightforward asactually moves it on the dialog sheet (analogous to VBÆs designit is for VB.environment).Assuming that youÆre already familiar with the forms capabilities ofòDialogs donÆt really go away until the calling subroutine ends(theyÆre just hidden).òOnly two dialogs can be displayed at the same time; anything more(or if the dialogs are complex) runs out of stack space.òBecause of the stack space issue, dialogs must be chained togetherusing OnTime events to kick off subsequent macros.òThe OnTime event involves a tolerance factor. If you miss it forsome reason (perhaps your system is busy with another task),youÆre dead. In addition, you can have only one OnTime eventrunning at any given time. Further, because Windows timersrequire callbacks (which are unavailable in Excel, much as they arein native VB) you canÆt use Windows APIs to get additional timercapabilities.òWhen a dialog is showing, you canÆt move a control on it.òText boxes are limited to 255 characters.òOnly one macro can be assigned to a control. For example, buttonshave only a Click event, and list boxes only a select event.FIGURE 1How Excel Objects Flow. Through Excel, Visual BasicòThe text box Keypress event occurs after the character shows up indevelopers gain access to a peerless library of objects designedthe text box; it doesnÆt tell you what key is pressed.for building business spreadsheet applications. This chart shows howExcelÆs objects are contained by and accessed through other objects.Are these restrictions something to worry about? Excel developershave been turning out useful applications for years with roughly thissame set of limitations. If all you want to do is throw up a couple of listboxes and buttons (something as complex as the Windows File | Opencommon dialog, for example), youÆll probably be OK. But developersused to the VB forms model will be extremely frustrated.HereÆs an example of calling an Excel dialog from VB. First, createan Excel workbook called SHEETS.XLS, open a dialog sheet, and adda list box and a list box. Then run the following code from VB:Dim APPXL As objectDim XL As objectDim wb As objectDim dlg As objectDim dlgList As objectDim objList As objectSet APPXL = GetObject(, "Excel.Application")Set XL = APPXL.ApplicationXL.Workbooks.Open "SHEETS.XLS"Set wb = XL.ActiveWorkbookSet Dlg = wb.DialogSheets("dialog1")Set DlgList = dlg.ListBoxes("sheetsList")Set objList = wb.SheetsdlgList.RemoveAllItemsdlg.DialogFrame.Caption = "List of Sheets"For ix = 1 To objList.countdlgList.[AddItem] (objList(ix).name)NextFIGURE 2The AMORTIZE.XLS worksheet sample included with Excel.dlg.[Show]A simple code string is used to open AMORTIZE.XLS, plugin values for the variables, and pop up the calculated payment in aSet dlg = Nothingmessage box. If desired, the whole table can be easily brought back.Set dlgList = NothingSet objList = NothingSet wb = Nothing86 FEBRUARY/MARCH 1994 Visual Basic ProgrammerÆs JournalOLE EXPERTSet XL = Nothingsmaller main memory footprint) than if each of the add-ins were fullySet APPXL = Nothingloaded, it still takes time for Excel to manage. Also, these menu items thatThe Excel5 INI Fileare read in for each add-in consume resources from the Windows menuheap. The menu heap (added in Windows 3.1) is one of the three systemWhen programming Excel from external applications, itÆs importantheaps (the others being GDI and User) involved in calculating freeto understand at least one aspect of ExcelÆs INI file: the section thatSystem Resources; while the amount of free menu heap has customarilymanages add-ins. This is necessary for at least two reasons: First, as abeen higher than GDI, which is usually the first to get used up, adding adeveloper you naturally want to minimize the resource usage of your appslew of menu items can have a dramatic affect. Of course, the problemand make it as fast and responsive as possible. Second, itÆs sometimeswith 64K heaps goes away in 32-bit Windows.necessary to have precise control over ExcelÆs state. A related issue is thatSo if you want to integrate with Excel 5.0, be aware of ExcelÆs add-if you change the default state of Excel, youÆll almost certainly want toin state. With Excel running and after setting an object variable xl to pointrestore the userÆs previous state when youÆre through.to an Excel application, enter the following code in VBÆs debug (easilyExcel add-ins are an important variable in the startup state of Excel.my favorite window in Windows):Individual add-ins are shown in entries in the ô[Microsoft Excel]ö sectionand begin with the word ôOPEN:öfor x = 1 to xl.addins.count :?xl.addins(x).name, _xl.addins(x).installed:next[Microsoft Excel]This generates a listing similar to the following (your mileage may vary):OPEN=/F C:\WINDOWS\EXCEL\LIBRARY\SOLVER\SOLVER.XLAANALYSIS.XLL 0OPEN1=/F C:\WINDOWS\EXCEL\LIBRARY\CROSSTAB\CROSSFNC.XLAAUTOSAVE.XLA 0OPEN2=/F /R C:\WINDOWS\EXCEL\LIBRARY\ADDINFNS.XLACROSSFNC.XLA -1SCENARIO.XLA 0[Recent File List]ADDINFNS.XLA -1ANALYSIS.XLA 0[Init Commands]ANALYSF.XLA 0PROOF.XLA 0[Converters]REPORTS.XLA 0SOLVER.XLA -1Note the /F switch in each line. This option, new with version 5.0, tellsUPDTLINK.XLA 0Excel to fast load the add-in: This means the add-in is examined for menuVIEWS.XLA 0items and selected text strings, but is not yet loaded into memory. Whilethis approach results in much quicker loads times (and a significantlyThough Excel establishes and maintains this list in EXCEL5.INI andVisual Basic ProgrammerÆs Journal FEBRUARY/MARCH 1994 87OLE EXPERTGo To NextPage.88 FEBRUARY/MARCH 1994 Visual Basic ProgrammerÆs JournalOLE EXPERTGo To NextPage.Visual Basic ProgrammerÆs Journal FEBRUARY/MARCH 1994 89OLE EXPERTGo To NextPage.90 FEBRUARY/MARCH 1994 Visual Basic ProgrammerÆs JournalOLE EXPERTwill renumber the entries so that they are sequential, a programmerAlias "GetPrivateProfileString"shouldnÆt rely on this. The proper technique involves reading in all the(ByVal lpApplicationName As String,keys in that section and searching for anything beginning with ôOPEN.öByVal lpKeyName As Long,The following VB code stuffs the contents of these strings into a defaultByVal lpDefault As String,list box (what you want to do with these string will vary):ByVal lpReturnedString As String,ByVal nSize As Integer,Declare Function GetPrivateProfileString Lib "Kernel"ByVal lpFileName As String) As IntegerAlias "GetPrivateProfileString"Dim sBuff As String * 2048(ByVal lpApplicationName As String,Dim sBuff2 As String * 128ByVal lpKeyName As String,Z$ = Chr$(0)ByVal lpDefault As String,ByVal lpReturnedString As String,iBuff = GetPrivateProfileStringSec("Microsoft Excel", O&, "none", sBuff, Len(sBuff),ByVal nSize As Integer,"EXCEL5.INI")ByVal lpFileName As String) As IntegerIni$ = Z$ & Left$(sBuff, iBuff)Declare Function GetPrivateProfileStringSec _Lib "Kernel"Foo$ = Z$ & "OPEN"y% = 1Dox% = InStr(y%, Ini$, Foo$)If x% = 0 Then Exit Doy% = InStr(x% + 1, Ini$, Z$)Tmp$ = Trim$(Mid$(Ini$, x% + 1, y% - x% - 1))iBuff = GetPrivateProfileString("Microsoft Excel", Tmp$, "none", sBuff2,Len(sBuff2), "EXCEL5.INI")List1.AddItem Left(sBuff2, iBuff)LoopUSING EXCEL ADDINS (SOLVER)After building a spreadsheet model of a problem, you can use Solver tooptimize the value of a target cell in that model by changing the valuesof a related range of cells. Solver allows you to set constraints for any cellin the model. Figures 3 and 4 show the effects of running Solver on theSOLVEREX.XLS sample provided with Excel and using the scenariocovered in Chapter 30 of the Microsoft Excel User Guide. Specifically,a constraint is specified for total advertising expenditures (F10 <=$40,000) and total profit (F14) is optimized by varying the quarterlyadvertising estimate (B10 to E10).Excel defines an object for handling add-ins, with properties andmethods just like other Excel objects:òProperties: Application, Author, Comments, Count, Creator,FullName, Installed, Keywords, Name, Parent, Path, Subject, TitleòMethods: Add, ItemUnfortunately, the add-ins themselves are not necessarily objects andmust be programmed through the Execute- Excel4Macro method of theApplication object. For example, to run the Solver analysis describedabove, the following code is run from VB:Dim APPXL As objectDim XL As objectDim ws As objectSet APPXL = GetObject(, "Excel.Application")Set XL = APPXL.ApplicationXL.Workbooks.Open"C:\WINDOWS\EXCEL\EXAMPLES\SOLVER\SOLVEREX.XLS"FIGURE 3FIGURE 4Using the Solver add-in to analyze a spreadsheet.Set ws = XL.ActiveSheetExcel defines an object for handling add-ins, witholdP$ = ws.Range("$F$14").Valueproperties and methods like other Excel objects. Unfortunately, add-insXL.ExecuteExcel4Macro "[SOLVER.XLA]SOLVER!SOLVER.OK(!R10C6,1,0,)"are not necessarily objects and must be programmed through theXL.ExecuteExcel4MacroApplication object. Figures 3 and 4 show the effects of running Solver on"[SOLVER.XLA]SOLVER!SOLVER.ADD(!R10C6,1,""=40000"")"the SOLVEREX.XLS sample provided with Excel, using a scenarioXL.ExecuteExcel4Macrocovered in Chapter 30 of the Microsoft Excel User Guide."[SOLVER.XLA]SOLVER!SOLVER.OK(!R14C6,1,0,(!R10C2:R10C5))"XL.ExecuteExcel4Macro "[SOLVER.XLA]SOLVER!SOLVER.SOLVE(True)"Visual Basic ProgrammerÆs Journal FEBRUARY/MARCH 1994 91OLE EXPERTGo To NextPage.92 FEBRUARY/MARCH 1994 Visual Basic ProgrammerÆs JournalOLE EXPERTGo To NextPage.Visual Basic ProgrammerÆs Journal FEBRUARY/MARCH 1994 93OLE EXPERTnewP$ = ws.Range("$F$14").Valueprogrammable via conventional syntax. nMsgBox "Old: " & Format(oldP$, "currency") & Chr$(10) & "New: " &Format(newP$, "currency"), , "Profit"XL.Workbooks(1).[Close] (False)Set ws = NothingSet XL = NothingSet APPXL = NothingInterestingly, Excel add-ins can also be programmed (from VBAonly) via:References Command (Tools Menu)This command adds, deletes, or makes available for editing VisualBasic references to libraries or other workbooks that are specified for theactive workbook. You must be in a Visual Basic module to use thiscommand.SOLVEROK(!R10C6,1,0,)SOLVERADD(!R10C6,1,""=40000"")SOLVEROK(!R14C6,1,0,(!R10C2:R10C5))SOLVERSOLVE(True)A couple of notes about this behavior: first of all, ExcelÆs built-in Macrorecorder, when set for VBA format, records (at least in the version IÆm using)generates ôApplication.ExecuteExcel4Macroö code. This isnÆt as bad as itseems since the code is much more portable to VB than it would be otherwise.Second, Excel 5 (at least for this version) is actually translating OLEAutomation calls into traditional XLM macro code anyway, so therewouldnÆt be an appreciable performance improvement if add-ins were94 FEBRUARY/MARCH 1994 Visual Basic ProgrammerÆs Journal
(.txt)