There are three things we need to do in order to start working with LDLS in our Excel Project: a) make excel
aware of the location of the LDLS code, b) define a variable name which we can use to quickly and easily refer
to LDLS, c) open a line of communication between Excel and LDLS.
1. Start Microsoft Excel
2. From the file menu choose Save As and save your document into the location you want with the name LDLSdatabase
or whatever you want to call it.
3. From the tools menu choose Macros and then Visual Basic Editor (VBE).
4. The VBE should now be visible.
5. In the top left hand side of the editor should be a window with the tile Project-VBAProject. This is the project
explorer window.
This window will list all the projects associated with currently open excel workbooks (remember workbooks can be
hidden from view so you may have a project in the list for a file called personal.xls which is normally hidden
from view). The project we are interested in working with will be listed as:
VBAProject(LDLSdatabase.xls) or whatever file name you used.
(note: you can click on the + to expand the project and see the associated items)
6. Left click with your mouse on this project to select it and make it the currently active project.
(note: you can tell the currently active project by checking the file name shown in the VBE title bar)
a) Make Excel aware of the location of the LDLS code
7. From the VBE menu choose TOOLS->REFERENCES.
8. The References dialog should open on you screen. References
Dialog Screen Shot
This dialog list the various code libraries available on your system that you can work with in VBA. This actual
contents of the list will be dependent upon what applications you have installed on your system. In the screen
shot notice the items with a check mark. These are the code libraries we want to access. (note depending on what
version of excel you are using you may see 8.0 or 10.0 instead of 9.0). Scroll through the list of code libraries
and place a check next to those shown and use the priority buttons(similar to setting keeling priority order) to
move these selected code libraries into the same order as you see in the screen shot.
9. Click on O.K. to save the settings and close the Project References Dialog. (Keep the screen shot open for now)
10. Hit the save button on the VBE toolbar to save our project at this point. (
b) Define a variable name which we can use to quickly and easily refer to LDLS
In the References Dialog Screen shot you will notice the in the project explorer window our project is expanded
and you can see a folder called: Microsoft Excel Objects and under this is list a reference to each sheet in your
workbook and to the workbook itself. If you double click on of these it will open the code window for that object.
But we don't want to assign our code to the worksheet or one of its individual sheets instead we are going to create
a module to store our code, two modules in fact.
11. From the VBE menu choose INSERT->MODULE. This will add a new folder to our project and the folder should
contain a module called Module1.
12. Repeat step 11 to insert a second module which will be displayed as Module2.
13. At this point we are going to rename this two modules to something that will remind us of
what code each module contains.
In the project explorer left-click on module1. Have a look at the properties window below on the bottom left hand
side below the project explorer. It will have the heading Module1 Module. Below this you will see two tabs, alphabetic
and categorized. At this point in time it does matter which tab you have selected as the module has only on property
we can set - the name. In the field name you will see the current module name: Module1. Double Left click on this
to select the text Module1 and type over it: Main. Now go ahead and select module2 from the project explorer and
rename it: functions. Modules
Screen Shot
14. This is the module in which we are going to control the logical flow of our code and hence it is our main module.
As the title for this section of the tutorial suggests we first need to define a variable name which we can use
to refer to the LDLS code library throughout our own code.
In the project explorer. Double left click on the module main. The cursor should now be in the code window for
the main module. Type the following statement and it enter:
We are using the DIM statement to create a variable named objLDLS which will refer to LDLS application object.
Using 'obj' as the start of our variable name helps us to remember that whenever we see this name in our code it
is referring to an object. Later on we will define a variable that will simply hold some text information about
whether a resource is locked or unlocked. The statement we will use will be: Dim strLicenseState As String. In
this case we use str to remind us the variable holds string information. (Optional: For more technical information
on declaring variables, DIM statement, and data types open help in the VBE and search for the topics: Declaring
Variables, Dim Statement, Data Type Summary, For more information on lbxApplication: 
)
16. In the project explorer double left click on the functions module to take you to the functions module code
window.
17. With the cursor in the functions code window from the VBE menu select, INSERT->PROCEDURE. This will open
the Add Procedure Dialog. In the Name input box type: openLDLS and leave the type set to sub and scope set to public.
Click on OK. Code Window
Screen Shot
(Optional: For more technical information on procedures and their scope open help in the VBE and search for
the topics: Declaring Variables, Dim Statement, Data Type Summary.)
18. We are now ready to assign the LDLS object to our variable objLDLS. We are going to use the SET statement
and the GetObject Function.
Set objLDLS = GetObject(, "LibronixDLS.LbxApplication")
(Tip: We are using Getobject() instead of create object as it allows us to determine if an LDLS is already running,
while CreateObject() will simply open another copy of LDLS regardless of whether one is already open.)
19. Once you type in this statement Your code should now be:
Public Sub openLDLS()
Set objLDLS = GetObject(, "LibronixDLS.LbxApplication")
End Sub
20. Make sure LDLS is not running if it is close before performing this next step. We need
it closed because we want to test our code to see if it works.
21. If it is already not place you cursor anywhere on the Set Statement in our code. From
the VBE menu choose: RUN->RUN SUB/USER FORM.
22. Hey wait a minute you say I get an error message something about run time error 429.
Hit the button DEBUG on the error dialog. It will take you to the SET statement and highlight it in yellow telling
you that there the error occurred at this point in code execution. This is helpful when trying to trace where errors
are occurring when we have lots of lines of code.
23. O.K. We done something wrong ? What could it be ? In the code window
select the word GetObject and right-click. A pop-up menu should open. Click on the term Definition. Debug
Step 1 Screen Shot
24. The object browser window should now be open. It looks busy but don't be concerned about it. You should
see the term GetObject highlighted. Right-click again to bring up another popup menu and choose help. This will
open up the help file to the article on how to use the GetObject function. Debug
Step 2 Screen Shot
From the help file we can see the GetObject Function takes two arguments: GetObject([pathname] [, class]). Both
of these arguments are optional meaning we don't have to include them. Obviously though you would need to include
at least for the function to know what to retrieve. The following statement from the help file provides an insight
into why we got the error: "If the pathname argument is omitted, GetObject returns a currently active object
of the specified type. If no object of the specified type exists, an error occurs." We didn't include anything
for the optional pathname and because LDLS was not already running the procedure spat the dummy because if couldn't
find a running copy of LDLS to initiate a conversation. The preceding statements says "If pathname is a zero-length
string (""), GetObject returns a new object instance of the specified type." Using this approach
our set statement would change to:
Set objLDLS = GetObject("", "LibronixDLS.LbxApplication")
25. Change your code to reflect this new statement. At this point code execution has been paused because we
are still in debug mode. Simply hit F5 to continue code execution.
26. You should now see the CBV, Dictionary Lookup and Keyboard Selector icons in your system tray. (The main
application window is not visible because we didn't include a statement in our code to make it visible. We will
get to that soon.) But what happens if you hit F5 again to execute the procedure a second time ? Try it. You get
a second instance of LDLS. This is not what we want either. It now seems to be behaving like the CreateObject Statement.
We need to come up with an approach that handles both situations. (At this point you may want to CTRL-ALT-DEL to
bring up the task manager and kill off the the two instances of LDLS and LIBSYS that are currently running.)
27. First we want to know if LDLS is currently running. Our first statement handled this well. If LDLS is running
it simply strikes up a conversation with this instance of LDLS. But LDLS is not running it gets a little lost and
doesn't know what to do. This is where we can use a error trap in our code. An error trap kicks in when a error
occurs and resolves the problem so that code execution can continue without the user having to intervene. We need
to add an ON ERROR statement as the first statement in our procedure. It acts as our error trap. When an error
interrupts the flow of code it redirects the flow of code to some alternate location with a different set of instructions
to be executed. In this particular instance if our statement first statement doesn't work, i.e. there is no instance
of LDLS running) we want our procedure to execute the second statement which will then create a new instance of
LDLS. Translating this into code our procedure will now be:
Public Sub openLDLS()
On Error GoTo ErrorTrap
Set objLDLS = GetObject(, "LibronixDLS.LbxApplication")
Exit Sub
ErrorTrap:
Set objLDLS = GetObject("", "LibronixDLS.LbxApplication")
End Sub
Note the Exit Sub statement. If our first statement executes without error (i.e. there is currently a copy LDLS
running) then we want to exit the procedure at this point as we don't want the second GetObject statement to execute.
In order to complete this function procedure we need to add in statements that will make the LDLS application
visible. To do this we simply set the visible property of the LDLS application to True. Thus our completed procedure
is:
Public Sub openLDLS()
On Error GoTo ErrorTrap
Set objLDLS = GetObject(, "LibronixDLS.LbxApplication")
objLDLS.Visible = True
Exit Function
ErrorTrap:
Set objLDLS = GetObject("", "LibronixDLS.LbxApplication")
objLDLS.Visible = True
End Sub
28. At this point make sure you have no instances of LDLS running. If you do then please close
them. What I want to do is set a break-point in our code. When the break-point in our code is reached code execution
pauses and it allows us to step through code execution one line at at time so that we can see the flow of our code.
This is another helpful technique in identify the source of problems in our code as well as being useful learning
tool.
Down the left hand side of the code window you will see a gray bar. Click on this gray bar next to statement
On Error GoTo ErrorTrap. This will highlight this line on code also place a circle on
the gray bar to indicate a break-point is set in our code. Setting
a Breakpoint Screen Shot
29. Hit F5 to begin code execution. Notice the program flow pauses at our break-point. (If LDLS is open please
close it now.) To step through our code one line at time Hit F8. The error trap statement is processed and control
moves to the first Set Statement.
30. Hit F8 again to process the this Set statement. Notice that control has jumped to the second set statement
because error 429 that we saw previously has occurred because there is no instance of LDLS open and the first set
statement doesn't know who to talk to but we have no given it an alternative statement to execute if this situation
arises.
31. Hit F8 again to try and process the second set statement. Notice the LDLS splash screen displays and LDLS
begins to load. You should see the icons display and the system tray and control should jump back to the VBE window
and it is awaiting our next move.
32. Hit F8 again. This will execute our statement: objLDLS.Visible = True. You should now be able to see the
LDLS main window. Hit F5 to complete.
33. At this point I will leave it to you as an exercise to step through the code again and determine the flow
of code now that there is an instance of LDLS running. Once you have done this from the VBE menu choose DEBUG->CLEAR
ALL BREAK-POINTS to remove the break-point as we no longer need it.
34. We now switch focus back to the main module. In the project explorer double left click on the main module
to bring its code window into focus. We are going to create a new procedure here that will be the main procedure
that controls the flow of our program. From the VBE menu bar select INSERT->PROCEDURE. In the ADD PROCEDURE
dialog give the procedure the name generateDatabase and leave the other settings at their default values. Click
Ok.
35. At this point we only need to add one line of code to our procedure: openLDLS. When we run this procedure,
generateDatabase, it will come to this line and transfer code execution to our procedure openLDLS we have just
created.
36. Another useful thing to do is to add comments to your code so that later on when you or someone else looks
at your code it tells the story of what you code is supposed to being doing. To add a comment just simply start
the comment with a single quote character. I have added some comments
to the code which you can see in the Final
Screen Shot. You don't need to add comments to the depth I have but I will do so in these
learning exercises a reminder to you of what we are doing.
Conclusion
In this lesson we have looked at the three things we need to do in order to control another application from
VBA: a) make excel of the location of the LDLS code, b) define a variable name which we can use to quickly and
easily refer to LDLS, c) open a line of communication between Excel and LDLS. In doing this we have also looked
at creating procedures, debugging code, using the right-click menu to access the help files, setting break-points
and stepping through code to follow its flow.
| |
Return
|
|
|
This site is maintained and updated by Andrew. Page Created: 18/7/2002.
Page Updated: 13/8/2003 Current Date:
|
