(C) 2012 - 2017 by Mourad Louha · All rights reserved.

VBA

by: Craig Hatmaker – 2014-01-04 

What is meant by “scripting”?

The term “scripting” often refers to writing simple code called “scripts” to automate tasks with a computer operating system. System and Network Administrators use scripts to automate their work. For example, administrators must keep inventories of various hardware and software under their care. When done manually this can be a slow, tedious, and error prone process. With scripts, System and Network Administrators can automate polling hardware on the network to discover hardware settings, catalog installed software, and record who is currently logged on.

How does VBA and Excel help scripting?

Excel’s “Macro” language is VBA and VBA is almost identical to one of the popular scripting languages, VBScript. So if you know VBScript, you basically know VBA. And if you know VBA, you basically know VBScript. And just about anything that can be done with VBScript, can be done with VBA. But VBA comes with some extras. Excel includes VBA’s “Integrated Development Environment” known as the Visual Basic Editor (VBE). So if you used Notepad to write VBScript, you will find the VBE much easier and more helpful. And if you ever exported a script’s output to a CSV to import into Excel, or if you needed to use a list of objects to feed a script, you will find VBA’s integration with Excel makes that much easier too.

What are the differences between VBA and VBScript?

Here a simple VBScript from Technet’s Sesame Script tutorial that reads a registry entry: Here is the same script in VBA: The differences include:
  • VBA routines must be Subroutines or Functions.
  • VBA doesn’t use “WScript”.
  • VBA uses MsgBox instead of Echo
Some other differences:
  • VBA routines can be run without being saved. VBScript must be saved with a .vbs extension before being run.
  • VBA has more than one variable type. As written, the VBA routine treats all variables as Variants, just like VBScript. But VBA supports better coding standards and allows us to declare variable types.

Downloads

Click here to download an Excel workbook that performs hundreds of common scripting queries.

Excel App WMI Query

Related Links

Introductions and Primers

References

Script Examples

 
Top