VBA Versus VB Programming
The following description assumes some familiarity with Visual Basic, which I treat as the "standard" of comparison. I compared Excel 97 VBA and Visual Basic 5.0, since these both use MSVBVM50.DLL. There are probably some VBA enhancements in Office 2000, which I currently do not use.
General
 | VBA projects do not have separate files for UserForms, code modules and class modules |
| If you export VBA UserForms as text, most control references are NOT in the *.frm files |
Application Development
| VB supports numerous project types, such as ActiveX EXE, ActveX Control, AddIn |
| VB Application Wizard builds various "standard" application frameworks for you |
| VBA differences: Can not created compiled EXE, only has one project type Menu/Debug/Compile VBA project is similar to VB Run with full compile (Control-F5) You can not declare instances of your own (*.CLS) objects using WithEvents Tools / Options is missing some pages Debug object has no Assert method |
| VBA code may fail if resident on an Excel worksheet (click event of a command button). The identical code will work properly if placed in a Code module, and called from the worksheet. |
VBA versus VB Controls
| VBA does not provide various standard controls: Picture Box, Timer, DriveList, DirectoryList, FileList, DrawingObjects, Line, Data, OLE container |
| VBA differences: Provides standard MultiPage control, similar to the SSTab control Does not support control arrays. Unlike VB5, it supports the Add method of the controls collection. Controls have an Accelerator property (instead of using ampersands in Caption property) Default control names are different ( e.g. CommandButton1 vs Command1, ListBox1 vs List1) Image control has no Stretch property CheckBox control value property has different values based on TripleState property TextBox MultiLine and ScrollBars property work correctly, unlike VB ListBox has no Sorted or NewIndex properties References to ImageList control can’t be set via Property pages, but must be set via VBA code |
VBA UserForms versus VB Forms
| Numerous similar, yet different properties and methods |
| UserForm differences: Always modal, have no minimize and maximize buttons No menu bar, and there is no menu editor Have Resize event, but it never fires Do not have graphic methods like Pset and Line No MDI form type, only supports single document interface |