VBA in Excel

Location: http://www.mvps.org/dmcritchie/excel/vba.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

HELP File, existence of (#help)

I have XL95, but I think you can tell if VBA help is installed on your XL97 with
Desktop START --> Find Files --> vba*.hlp
should have Vba_xl.hlp
Also of interest in Excel
 HELP --> Index ---> Text property
within Text property at the bottom is a hypertext for "Using Microsoft Excel worksheet functions in Visual Basic" then from there use See Also or use first hypertext to get to "List of Worksheet Functions Available to Visual Basic".

List of Functions (#functions)

VBA Functions (#helpVBA)
You can see a list of functions in your VBA Object Browser by using F2 once your a in the VBE (Visual Basic Editor -- Alt+F11)
And for the ones you would most be interested in choose VBA in the first dropdown instead of
Then click on the function (note the icon in front) for short description below. Or press F1 for the regular Help information for the function.
If you go through the VBA Help, Index (tab) to look at a function, you can then choose the Contents (tab) to see where it fits in within VBA Help which is written more or less like a manual with a table of contents, chapters, subtopics, and an index. You can go the contents tab of help and look for Functions which is broken down by the names A-C, D-G, H-L, M-P, Q-Z. You can expand each and then scroll up and down on the contents. The next time you enter help and look at contents the entries will be collapsed.
Excel Help for Functions (#helpExcel)

For Excel Help, you can see Excel functions within the topic “Worksheet Function Reference” which you can get near by looking at a worksheet function through the Excel help index. You can see one line definitions for each category (look at also from a help entry). The categories are the same as you see from from the Excel worksheet looking at .
Also see HELP in and for Excel on this site.

Options Explicit (#explicit)

Options Explicit should be placed at the top of your modules to help ensure that you are using your variables correctly and provide better diagnostics. You should go a bit further to make sure you declare all of your variables by using the “Require variable declaration” is checked under Tools, Options, General in your VBE. (This was mentioned on my Slow Response page, also see TypeName below to determine data type if unknown)
Don't put all your macros and functions in one module. You should group things that will be used at the same time. Having lots of modules open is going to cost you more memory usage.
In your VBE HELP (F1) look up topic "Data Type Summary" and do look it up because you will need to know this for helping you with Options Explicit.
Integer, 2 bytes, value -32,768 to 32,767 Long (long integer), 4 bytes value -2,147,483,648 to 2,147,483,647

TypeName used to find DataType (#typename)

You can use the TypeName VBA function, to determine how a variable is actually being used such as if something was not declared or was declared as variant. Look up TypeName in your VBE Help.
' test for when there was no  Option Explicit
  Dim i As Variant
  Dim cell As Variant
  i = 14
  msgbox "cell before use is " & TypeName(cell) 
  For Each cell In Intersect(Selection, ActiveCell)
     MsgBox "cell is " & TypeName(cell)
  Next cell
  MsgBox "variable ""i""  is " & TypeName(i) & vbCr _
         & "selection is " & TypeName(Selection) & vbCr _
         & "cell  is " & TypeName(cell)
End Sub
Results
cell before use is Range
cell is Range
variable "i" is Long
selection is Range
cell is Empty

Names Collection (#names)

Names Collection posting by Leo Heuser (2002-02-02) describes local and global variables, name box, and the insert menu.

Related Information on Other Sites

Also see VBE which is/was/will be for using the VBE.

Microsoft Knowledge Data Base (MS KB)

Programming Microsoft® Excel for Windows®
Microsoft Excel -- What's New
VBA coding conventions and VBA Constant and Variable Naming Conventions
Programming To The VBE, Chip Pearson
Debugging VBA, Chip Pearson
Code Modules And Code Names, Chip Pearson, the four main types of modules: Standard Code Modules, Workbook And Sheet Code Modules; and worksheets and chart sheets; User Forms; and Class Modules.
HELP in and for Excel on this site.
199809 - INFO: How VB Interprets Numbers, Constants and Numeric Types
failure to dimension may result in overflow errors from simple multiplication.
Add Method
looks interesting, has lots of VBA code and snippets, alphabetical list, Add is within the A's.
MS Excel workbook Tips from Excel Tip .com / Free Excel Tutorial / Excel Spreadsheet Help
[commercial site, 496KB text + 18KB images, slow loading]
Reading flat text files
read the 9th line of each file and place into a spreadsheet.

This page was introduced on July 12, 1998.

[My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]

Please send your comments concerning this web page to: David McRitchie send email comments