Excel Intruduction

Introduction

VBA in Excel was first used to automate repetitive tasks, VBA served to order the computer to do tasks that the users usually did. Because VBA did that more rapidly and more efficiently than the best user, it was possible to develop applications to complete tasks that even a team of well trained users could not perform within the deadlines imposed by the decision making process. These orders were MACROS. Since 1997, VBA is a full fledge programming language that allows a developer to create applications (programs within Excel), the MACROS are now PROCEDURES.

If you don't master thoroughly the functions and functionalities of Excel, you will never become a good VBA programmer, you will be re-inventing the wheel while cars will be flying.

If you don't listen to the user who has the knowledge of the data, you will never be a good VBA programmer and if you don't listen to the client and don't try to understand her/his needs, you will never be a good VBA programer.

Programing will never constitute more than 25% of the work, the other 75% consisting of understanding the needs of the client and designing the workbook within which the programming will be developed.

So to begin with, I offer you some vocabulary of the trade and then, the tools I use in my daily work as a Consultant in Excel-VBA-Access.

First, a section of General Rules for VBA and the Visual Basic Editor (VBE) to simplify the work of the developer and of the person who will have to maintain the applications or modify them in the absence of the original designer.

Secondly, the most important section: Moving Around in Excel. This section shos you how to move between cell, between sheet, between workbooks. It shows you how to select cells and ranges.

Then a section on VB itself VB-Statements and a section on functions of both VB and VBA VB-VBA-Functions. A section covers the controls and forms VBA-Controls and Forms and another one adresses the procedure's triggers, VBA-Events. Finally three pages on VBA-Objects, VBA-Properties and, VBA-Methods.

Finally, join the major leagues, discover the VBA array variables. Start working in the virtual world.

VBA Q&A's

What is VBA?

VBA is "Visual Basic for application". It is a programming language that allows users to program complex tasks within an application. There is VBA for Excel, VBA for Word, VBA for Project, VBA for Access, etc.

Where does VBA come from?

In the ྂ a new computer language appeared, it was called Basic. I used it on advanced calculators and on my COCO64 from Radio-Shack. At the time, RAM (memory) and CPU's (the brain of the computer) being very very small, you were limited in what you could do with the language. With today's computer, came Visual Basic. It is still Basic, but a lot of elements are pre-programmed making the user's task much simpler. Microsoft adopted the language and introduced it as a component of all its applications thus the VBA that stands for Visual Basic for Application. So now you can program in Visual Basic (VB) to create applications or you can program within an application (Excel, Access, Word, Project, etc.) with Visual Basic for Application (VBA). This section of my Website is about VBA for Excel. I also maintain a Web site on VBA for Access.

Should I learn VBA or VB?

Let's say that VB is for programmers and VBA is for users (or developers). Personally, I took hundreds of hours of training in VB but I do all my work in VBA (Excel and Access). You don't need to learn VB to be good at VBA. I adopted VBA because most of the functions I need to organize and analyze data are pre-programmed in Excel and Access. It costs a lot less to develop financial and management applications in Excel and Access than to re-invent the wheel and do everything in VB.

What is the difference between VB, VBA for Excel, VBA for Access, VBA for Word, VBA for Project, etc?

To answer this question, I will ask you to imagine a meeting of five English speaking persons, a lawyer, an accountant, a physician, a chemist and a psychologist. They all speak English but when it is time to talk about their work, none of them totally understand the other. It is the same with each VBA, the objects, properties, methods and events vary from a language to the other.



MACROS or Procedures

You will notice that within my HelpSite on VBA-Excel, I use the term "procedure" rather than "Macro". I consider VBA-Excel as a Development Environment hence, the "procedure". I would hope that my colleagues Developers start using this word so that VBA-Excel earns a status among Developers and Users in the field of Database Interface, Datamarting, Datamining and Financial and Strategic Information Analysis Systems.





The tips offered below are:

Indispensable
Very useful
Interesting



All through this web site, you will encounter the following icon:


Click on it and a demo will appear to illustrate the topic. The cursor moves, explanations are supplied in balloons, it happens in front of your very eyes, on line.

And you can get all the viewlets for free, click here.



General Rules and VBE

Note: Your best VBA coach is the Macro Recorder (Tools/Macro/New Macro). Start it, do what you want your code to do and then go to the Visual Basic Editor (VBE) and cut and paste these segments of code. Personally, I use the Register a lot to avoid writing and spelling errors. I adapt the code that is offered by VBE.

Note: Quotes within quotes must be doubled. Hence,
Selection.Formula= "=Range ("A1").Value & "normal"" must be written
Selection.Formula = "=Range(""A1"").Value & ""normal"""

When I work with the Visual Basic Editor (VBE) I use three windows, the Project window, the properties window and the code window.

You can test a procedure line by line by clicking within the procedure in the Code Window of the VBE and using the F8 key. After a line of code containing a variable has been executed, you can check the value of this variable by moving the cursor on top of the name of the variable.

You can see your VBA code at work by opening both Excel and the Visual Basic Editor and then by right clicking on the task bar and splitting the screen.

Declare all your variables (Dim) at the beginning of the procedure, it will simplify the testing of your code.

To test part of a VBA procedure, start the execution with the F8 key. Then, click on the yellow arrow in the margin of the code window and drag it down to the line you want to test. Use the F8 key from there.

To test part of a procedure, you can also press F8 then right click in the margin of the code window in front of the line that you want to test and select "Set next statement". From then on, you use F8.


To create code within an event related to the sheet, right click on the sheet's tab and select "View Code". Then select the event from the event window and write your code.

You can carry the value of a variable from one procedure to the other by stocking this value in any cell in the workbook. ex:
in the first procedure:
Range("A3456").Value=Variable1
in the second procedure:
Variable2=Range("A3456").Value

Name ALL the cells and ranges that you use in your code so that if their address changes (adding or deleting columns or rows, changing the name of sheets, etc..), your code is not invalidated.
Selection.Name = "nfaBalance"

Note: If you want to name the fields that you use in array formulas, Index/Match formulas and VBA procedures in Excel, you select the pertinent cell or field and then you go to the Name Zone and type a name. Then, CLICK ENTER. Use prefixes (nf, general named fields; prf, fields used in procedures; aff, array formula fields; etc..)

Write your code in lower case letters. If the spelling is right, VBE will capitalize the necessary letters. If it doesn't, check your spelling.

Always activate the "Option Explicit". You are forced to declare variables but there are many advantages. If you have the wrong spelling for your variable, VBE will tell you. You are always sure that your variables are considered by VBE. You can use Shift/Space to call your variables in a contextual menu and double click them rather then key them in.

http://codeprogrammer.blogspot.com