I am running a program that uses 5 macros and lots of formulas. Some of the macros I have asked for your help on here. After putting the program together there is alot of lag. I mean if we delete a line we have to wait 1 to 2 minutes for it do the that process. Any ideas on what I should be looking at? I know this sounds somewhat vague but I am at a loss of where to starting looking to resolve the lag. Do I look at computer, server, or program?
From Visual Basic Concepts (part of Help)
You can make your Visual Basic applications run faster by optimizing the way Visual Basic resolves object references. The speed with which Visual Basic handles object references can be affected by:
Whether or not the ActiveX component has been implemented as an in-process server or an out-of-process server.
Whether an object reference is early-bound or late-bound. In general, if a component has been implemented as part of an executable file (.exe file), it is an out-of-process server and runs in its own process. If it has been implemented as a dynamic-link library, it is an in-process server and runs in the same process as the client application.
Applications that use in-process servers usually run faster than those that use out-of-process servers because the application doesn't have to cross process boundaries to use an object's properties, methods, and events. For more information about in-process and out-of-process servers, see "In-Process and Out-of-Process Servers."
Object references are early-bound if they use object variables declared as variables of a specific class. Object references are late-bound if they use object variables declared as variables of the generic Object class. Object references that use early-bound variables usually run faster than those that use late-bound variables.
See this link from a Microsoft person. This is excel specific rather than VBA. Autocalc and other calc options/screenupdating etc.
http://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/
Minimise Dots
So if you are interested in performance minimise dots (each dot is a lookup), especially in loops.
There are two ways. One is to set objects to the lowest object if you are going to access more than once.
eg (slower)
set xlapp = CreateObject("Excel.Application")
msgbox xlapp.worksheets(0).name
(faster because you omitt a dot every time you use the object)
set xlapp = CreateObject("Excel.Application")
set wsheet = xlapp.worksheets(0)
msgbox wsheet.name
The second way is with statement. You can only have one with active at a time.
This skips 100 lookups.
with wsheet
For x = 1 to 100
msgbox .name
Next
end with
String Concatination
And don't join strings one character at a time. See this from a VBScript programmer. It requires 50,000 bytes and many allocation and deallocation to make a 100 character string.
http://blogs.msdn.com/b/ericlippert/archive/2003/10/20/53248.aspx