Coding in VBA for Excel
This section is about coding (writing sentences so that Excel understand). The rules are not as severe as in other programming languages and VBA is very forgiving usually telling you that there is something wrong in your code as soon as you click on enter. In these lessons you will learn a lot of tips to make things easier in "General Coding Tips" and then you will discover the VBA vocabulary of Excel.
An ordinary sentence for Excel is more like "Me.Hungry, Me.Thirsty" than "My stomach is telling me quite clearly that it is time for me to start thinking about eating and drinking".
A few tips to make things easier when you start coding.
VBA tells you when the code is wrong but what if the logic is wrong or what if the user gives a wrong answer.
Among the objects that you will be working with is Excel itself. It is called the APPLICATION.
Working with one workbook, with THISWORKBOOK, with many workbooks and even with all the workbooks in a directory.
Moving from one to the other, copy/paste from one to the other, adding and deleting worksheets and even doing something on all of them.
Working with cells (Range)
Moving around from cell to cell
Inserting and deleting
Entering values and formulas in cells
There are important cells and groups of cells
Paste and the useful PasteSpecial
The address can be "A1" but also Sheets("B2").Value or varAddress or nfAddress
Interacting with the user with minimal effort.
Most of your data in the form of a database. Learn how to sort and filter your data.
They are the KILL, the IF_THEN_ELSE_END, the SELECT-CASE, the DO_LOOP, the FOR_NEXT....
Creating new Excel functions, using the Excel functions in VBA and a few VBA functions.
When you don't want to hard code things and when the value of something changes put it in a variable. Discover the VARIANT type variable that allows you to execute 1,000,000 calculations in less than 5 seconds.
When you connect to outside sources of data (large databases, text files, other Excel workbooks, Access, etc.) the computer is using SQL (Structured Query Language) a specialized language to work with data. You can use Excel's functionalities to connect and extract data but you can also use directly the SQL language. It is much faster.
With VBA for Excel you can develop procedures (macros) to work in Excel. Using the API functions you can also program things in WINDOWS from Excel.