Microsoft Office VBA Glossary
This is where all you macro code is written, This will be explained in detail elsewhere on the site.
This is the proper (Microsoft) name for an Excel file, it is refered to in the Excel object model. An Excel workbook can have an unspecified number of Worksheets/tabs (limited only by system resources).
The vba object name for this is workbook which is part of the workbooks collection.
dim wbk as workbook ' Notice we access the workbooks (plural) collection to get a reference to the ' workbook (file) c:\data\workbookname.xls. set wbk=workbooks.open("c:\data\workbookname.xls") wbk.worksheets(1).range("A1").value="BusinessProgrammer.com" msgbox "workbook opened"
This is the page or TAB in the workbook and is most commanly called a spreadsheet it has approx 65000 rows and 256 columns up to Excel 2003 and 1,048,576 rows and 16,384 in Excel 2010 and above. The vba object name for this is worksheet or sheet.
In object oriented programming this is the blue print or architectural plan of the program that your are using. It is incredibly useful because when programs expose an object model for programmers to implement such as with Word, Excel or Access, you the business programmer can navigate it in code and get things done in a readable, easy, maintainable and concise manner.
The application that you are coding (Excel, Access, Word) has a visual representation (spreadsheet grid, word area for typing, Access tables and queries) and a programatic representation. The VBA project is the programatic representation of your application and you can issue commands from here (on the fly) via the immediate window. The visual representation of the the vba project is the vba project window which is a child window of the VBA IDE, it displays all the objects which you can control or interact with from your vba project. This view displays all user visible objects such as worksheets and charts and the user invisible objects such as class modules, code behind sheets and forms.
For example in Excel a Project represents the current workbook (file) that you are writing code in. If you add a form, module or class module to the project you can view them here and access them by double clicking on them. When you add a new form, module or class module to your project you get a meaningless name such as module1, form1 etc. Double click the new object in the project window and then change it’s name to something meaningful such as mQueryDatabase or frmSystemLogin
This can be loosely defined as a program that starts at the beginning, runs and then ends sort of like executing instructions in a list, as opposed to event driven programming and or object oriented programming. It can also be used however to define a basic set of rules you need to follow on the journey to writing good code. The name says it all PROCEDURAL programming i.e
put you code in procedures (plural) as opposed to using one procedure that becomes 1000 lines long. It covers rules such as …
- One function one task
- Data Hiding
- Declare All Variables (that you can) Locally
- Use a naming convention – Hungarian for example
- Pass variables between functions using arguments.
This represents a rectangular selection on a worksheet (Note, it does not have to be actually selected) such as A5 to k50 (A5:k50).
Ranges can be assigned to a formula, range name, or most usefully a VBA range data type.
Most data manipulation in Excel does something with ranges. This is a core concept.
This is the “type of” variable, String (text), Double, Integer, Long, Single (Number), worksheet, range ,form
Object Oriented Programming
Have a look at this first post on Object Orientation.
Excel Object Model
This is the vba blueprint to manipulate Excel in code. Using the Object Model enables you to cleanly and clearly instruct Excel or any Microsoft Office Application to do Stuff.
An example of a simple “Object Model” in Excel is contained in this post under the heading Excel Specific Objects.
This is explained in detail on the data hiding post.
Subroutines or functions need to be able to communicate with one another.
The wrong way to do it is by using global variables.
The correct way is to pass an argument, this works with data hiding as an argument states to the reader of the code what the interface to the outside world is.
Therefore you the programmer know that you can change any variables or code outside the routine and you will not effect the contents of the routine, provided you have not changed the argument values.
Example in VBA…
dim bIsBMW as boolean dim szRegistrationNumber as string szRegistrationNumber = "Some Car Reg" ' Her we pass in an argument of Car Reg type as szRegistrationNumber bIsBMW = isBMW(szRegistrationNumber) ' This function is given a Car Reg variable, it then check against a database to see car type ' and return true or false to calling routine. function isBMW(byval szReg as string) as boolean dim bCarIsBMW as boolean ' Code to check for bmw isBMW = bCarIsBMW end function
This provides a similar function in a program to the memory key in a calculator, only difference is that you can have an unlimited amount of variables in a program. It represents a piece of computer memory that you have reserved to store a number or text or some form of object.
Examples of variables, declared in various languages are below
dim strLastName as string ' LastName as Text in VBA string strLastName; // The same in a "C" style language strLastName = "Johnson" // Assigning a value to the variable msgBox strLastName ' Displaying the contents of the variable