VBA is an abbreviation for Visual Basic for Applications. If there is something that you want to do in your workbook that Excel is incapable of doing then try doing it with VBA. The macro recorder does a great job of creating VBA code automatically for you, but there are some things that you can’t record and in those cases you will need to manually create the VBA code.
What is Computer Programming?
Before we get started on our journey into learning VBA for Excel, let’s take a brief look at what computer programming is all about.
The Excel VBA language is a good starting language for those who want to become computer programmers because Excel has so much functionality already built-in that you can use in your code plus you don’t have to buy it, it comes free with Excel. The VBA language can also be used in Access and Word but it has the most capability in Excel.
Computer programming can be made simpler if we think of it as doing electronically what we are already doing manually.
When writing a program take each individual mental step you go through to solve a problem and write a correlating Visual Basic statement. For example let’s say you wanted to find the average of 3 numbers (3, 6, and 12). The first mental step you would take would be to add the 3 numbers. You would store the total of that addition in your brain (memory) so you could use it later for the division.
The first VBA statement would be:
TOTAL = 3 + 6 + 12
Just as we save the total of the addition in a part of our memory, the computer will store the total of the addition in a part of its memory. In the Visual Basic statement we assigned the name TOTAL to this part of the computer’s memory. Just as we can retrieve the stored value (21) from our memory for later use, so can the computer.
The next mental step would be to take the total of our addition and divide it by 3 and store the result in our memory.
The second Visual Basic statement would be:
SOLUTION = TOTAL / 3
The result of the division is stored in a memory area we named SOLUTION for later retrieval. The last step we would do would be to display the result (7). There are many ways we can display the result in VBA. One method would be to place the result in a cell. The following statement displays the value that is stored in SOLUTION in cell A1.
Range(“A1”).Value = SOLUTION
What is involved in a programming project
When we think about creating a project we start by thinking about what it is we want to accomplish. We usually start with an overall goal. A goal may be to create an Inventory Tracking Project that will increase our efficiency and lead to less missing inventory. We then list our objectives. We list everything that we want the project to accomplish. After we have stated our overall goal and listed out objectives then we can start thinking about what procedures will be needed to accomplish these objectives. We will need to write a coded procedure to handle each one of the procedures.
We write programs in procedures because:- It makes the program easier to read.
- We only have to write the procedure once and then we can call it as many times as we need to.
- If there are multiple programmers working on the project they can be assigned different procedures to work on.
- It makes it easier to track errors. Let's say that when I run the program the amount of taxes is coming out wrong. I don't have to search the entire program to find this error I just go to the procedure where taxes are computed.
There is a lot more to programming than just coding. You will have to research as to where the data will be coming from. Coming up with the proper test data and doing the actual testing can take longer than creating the program. Coming up with testing material that test all possibilities is essential. The program you create may use files that were created and updated in many other programs. The program you create might create and update files that will be used in other programs. All of these programs will have to be run to make sure the results are what is expected.
Careful planning is the key to a successful project. Getting all the details and planning a program can be far more difficult than the actual creation of the program. Good communication skills are necessary to get all the program requirements from those requesting the program. Those requesting a program often have a hard time communicating exactly what it is they want. Sometimes they are not even sure what they want. There will be a lot of back and forth with them, showing displays & outputs then verifying that this is what they really want. Unless it is absolutely necessary do not start writing any code until you have final approval for the project.
Programming environments keep improving and you can do more with less code than ever before. While that may make programming easier, programming languages are capable of doing far more than they have in the past which means that there is more for the programmer to learn and remember.
If you had a room full of programmers writing the same program, each programmer would code his program differently. Does this mean there is no right or wrong way of creating a program? No. The best program is the program that runs the fastest, is the most user friendly, and has the best error protection. The program should also be well documented and easy as possible to follow. You or another programmer may have to make changes to the program years from now and you will not remember what the program does or what data was used in the program, etc. unless you have everything well documented.
Programmer Skills: A programmer must be able to spend long periods of time in deep concentration. A programmer needs a variety of skills:
* Patience
* Good Communicator
* Problem Solver
* Good Study Skills
* Reading Skills
* Most importantly – Persererance
If after learning a new skill, you find yourself saying what if I tried this and what if I tried that, then you are on your way to becoming a good programmer.