Investigating macros and objects

  • Thread starter Thread starter Bryan Kelly
  • Start date Start date
B

Bryan Kelly

I need to write some macros and to do so I must understand some of the Excel
functions. For example, when I record a macro to find something, I edit the
macro and find this:

Cells.Find(What:="actualaxis1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

For example: What are all the options for "After:=" If I suspect that
"ActiveCell" does not fit my needs, what else can I put in here? In this
case, I don't want to look AFTER any particular cell, I want the entire
worksheet searched.

Question 1: I need a list of Excel functions. For each function, I need to
know the parameters it can accept and the options and ranges for each
parameter. Where can I find this information?

Question 2: After running the macro, I opened the "Locals Window" and began
stepping through the macro to see what it does. I quickly received runtime
error 91: To make this short, that just means that my search object was not
found. To throw a run time error when a search fails is just plain absurd.
A not found condition should be an expected result, not a run time error.
What should I do to detect a not found condition and eliminate a run time
error?

(btw, when I try help, it tells me it cannot find VEENLR3.HLP. Its not on
my Exel disk so I don't know how to resolve this but I supose that is fodder
for another question.)
 
Your general macro questions would probably be answered more quickly if
you post this message in microsoft.public.exce.programming

For information on Excel's functions, you can download Peter Noneley's
Function Dictionary:

http://homepage.ntlworld.com/noneley/

To get help for Visual Basic for Applications, make sure you've
installed the VBA help from your MS Office CDs.
 
Bryan -

Sounds more like a question for the programming newsgroup.

To get the VBA help files from the CD, insert the CD, rerun the setup
program, then just modify your installation to include all the help
files. VBA help isn't installed by default.

Also, a list of all the functions isn't going to help much, because it's
a long list. A better tool is the Object Browser, which you open in the
VB Editor by pressing the F2 function key. It has a list of all the
objects in Excel, along with the properties, methods, and events for
each. It also enables you to search for a keyword.

Another trick is to turn on the macro recorder to see what Excel is
doing while you perform a sequence of actions. This code can be made
more efficient, but it's usually a good start.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top