Excel Vacabulary

Thousands of line of VBA code in my Resource Workbooks

Here is some vocabulary you might want to adopt so that others can understand you and so you can understand the programmers and discover that they are just ordinary people doing a different job.

Procedure: A set of instruction that you want the computer to execute. In the old days when the computers were small and slow and the languages were not well developed, they were called macros

Objects: They are the building blocks of your procedures (workbooks, worksheets, charts, drawings, controls (buttons, text boxes, list boxes, etc.)), VBA modules, procedures, etc.

Properties: Think of the verb to be. Objects have properties that you can change.

Methods: Think of the verb to do. You might want an object to close, to open, etc.

Events: For a procedure to be executed, an event must happen. One event that everybody knows is the click of the button. Other events are the opening a workbook, the activating a sheet. So, you have to tell the computer when to execute a procedure.

Example:

Sub Command125_Click ()
Range("A2").Value= 2
Application.Close
End Sub

The "Sub" is the procedure; "Command125_Click" is the event; "Range ("A2")" and "Application" are objects; "Value" is a property" and "Close" is a method.

This procedure says that when the user clicks on the command button 125, the cell A2 takes a value of 2 and Excel is closed.

As you will discover in this Web site, you don't have to write much code, the Macro Recorder does the work for you.

http://codeprogrammer.blogspot.com

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

Excel As Database

Excel As Database ?
On this page, I present the limits of Excel as a database and it's functionalities in that field. There are sections on general tips, on filters, and on formulas.

I also illustrate an Data Management System that includes Excel and that can be a excellent solution if you have less than 60,0000 employees, less than 60,000 sales per year, less than 60,000 employees, less than 60,000 items for sale and less than 60,000 accounts in your accounting system. From this database, you can design an accounting system, an inventory system, a billing system, etc. You will be able to maintain such a system internally and adapt it to the changes in your organization.

Excel is not a database application but it is the best reporting tool on the market. You can import data from any database or datamart and create reports and analysis to satisfy your needs for a small fraction of the cost that you would incur if you would use the reporting applications of the database. You will save a lot of time and be able to train many users becoming less dependant on the limited resources of your IS department.
If you want to develop a database or a datamart in Access,
visit my page on the subject.
Even if many users can open the same workbook at the same time (in read only mode), the limit of Excel as a database resides in the fact that only one user can enter data in a workbook at one time. If two to 15 users must enter data simultaneously, you may want to move to Access as a database. If more than 20 users must enter data simultaneously, you will choose one of the big databases (SQL Server, Sybase, Oracle, dB, etc).



An Excel workbook may comprise 256 sheets with 256 columns and 65,000 rows each which amount to 4,294,967,296 cells. Each cell can hold 32,767 characters each or a formula 1,024 characters long. With a 500 mhz CPU and 128 Mg of RAM, Excel can be used as a database. The great power of Excel is its conviviality to design reports, analysis and charts.


Fundamental DATABASE Notion

A DATABASE is a set of data
which reading is convivial
for a MACHINE.

A REPORT (or form) is a set of data
which reading is convivial
for a HUMAN.

Very rarely will you be able to organize data so that reading them is convivial to both machines and humans.

To become an expert in database a good background in computer science is essential. To become an expert in reporting you need a good background in accounting and management.

Excel Database

Note: Select a different format for the title cells as oppose to the other cells of the table so that Excel understands that it is working with a database and not just a table with no title cells. Excel will consider your table as a Database if in the first cell of each column the font is bold and the inferior border is also bold. Once these conditions are met, all items of the "Data" sub-menu become accessible (Filters, Sort, Form, Sub-Total, etc...).

You know how to use the AUTOFILTER, you place the cursor within the database and go to the menu "Data/Filter/AutoFilter" and little arrows appear in the title cell of every field. If you want these dropdown menus to appear only for certain fields, select the desired column or columns before you go to the menu bar to use "Data/Filter/AutoFilter". (Thanks Darrel Steen)


Advanced Filters

Advanced filters are one of the most important tool to be used with databases. Here are a few tips on these filters. Let's say we have a database with the following fields:

First Name
Name
Age
City
Sex
Weight


The criteria range to filter members by the name of Thomas would be:
Name

Thomas


The criteria range to filter the women of Montreal would be:
Sex
City

W
Montreal

The criteria range to filter the women of Montreal and of Toronto would be:
Sex
City

W
Montreal

W
Toronto

The criteria range to filter men between ages of 50 and 59 would be:
Sex
Age
Age

M
>=50
<60>=50
<60

W

Other Formulas Useful with Excel as a Database

SUBTOTAL
The function SUBTOTAL allows (among other operations) to count, to sum or to calculate the average of non-filtered elements of a database. The function requires two arguments, the first is a number between "1" and "11" that specifies the operation to be performed (for ex. "1" is for average, "2" is for count and "9" is for sum) and, the second is the range covered by the function.
=SUBTOTAL(9,B2:B45)
This formula will sum the values in range (B2:B45) for records that are not filtered when a filter is applied on the database.

A Data Management System with Excel and Access

I have designed this system for a division of the Ministry of Supplies and Services (Government of Canada).

In this system, I don't use Microsoft Query and the data are LIVE. Many users can view the different reports at the same time (invoice, contract, statistics, report1) but only one user can enter data at one time.

The workbooks with a single sheet contain the data. They are the tables of this Data Management System. They can host as much as 60,000 records each (lines of data).

The different reports (unlimited numbers) are linked to the "tables" with array formulas and Index/Match formulas. When a report is opened, a dialog window allows the user to refresh the data. On of the great advantage of Excel Reports is that they can be printed and, unlike other reports designed within database, they can be viewed on line. These reports are invoices, contracts, analysis, summary and detailed tables and charts.

There is no convivial or ergonomic form to enter data in "Tables" in Excel. Even the automatic "Form" in Excel is rather limited. So I have created an Access application so that I could design convivial and ergonomic forms to enter the data in the "tables". The Excel workbooks with a single sheet are connected to the Access application through the "Link Tables" function.

This Data Management System is easy and inexpensive to build, easy to maintain and very very friendly. Training on Access is minimal and Excel is a development environment within which your users are probably already familiar. You might want to hire a Data Analyst to make sure that the Excel workbooks with a single worksheet (your tables) are well designed as far as what they contain and how the information is organized.

Excel As Database

Create Your Own Hit Counter

<%
Application.Lock
Application("Count") = Application("Count") + 1
Application.Unlock
x=application("Count")
%>

<%=x%>

Search Engine Script

<html>

<title>CodeAve.com(JavaScript: Search Many Sites)</title>

<body bgcolor="#FFFFFF">



<script language="JavaScript">

<!--

function search_site()

{

// Build the destination url by placing the selection option first

var url =
document.search_form.site.options[document.search_form.site.selectedIndex].value


// Append the text box input to the search url for the selected site

url = url + document.search_form.u_search.value;

// Change the the browser location to the new url

window.location.href = url;

}

-->

</script>



<center>



<form name="search_form">

<input tpye="text" name="u_search">

<select name="site" size="1">

<option value="http://search.yahoo.com/bin/search?p=">Yahoo</option>

<option value="http://www.go.com/Titles?qt=">Go.com</option>

<option value="http://www.goto.com/d/search/?Keywords=">GoTo.com</option>

<option value="http://search.excite.com/search.gw?search=">Excite</option>

<option value="http://www.lycos.com/srch/?query=">Lycos</option>

<option value="http://hotbot.lycos.com/?MT=">HotBot</option>

<option value="http://www.altavista.com/cgi-bin/query?&q=">Alta Vista</option>

<option value="http://www.webcrawler.com/cgi-bin/WebQuery?searchText=">WebCrawler</option>

<option value="http://www.mamma.com/Mamma?&query=">Mamma.com</option>

<option value="http://search.aol.com/dirsearch.adp?query=">AOL.com</option>

<option value="http://search.msn.com/results.asp?q=">MSN.com</option>

</select>

<input type="button" value="Search" onClick="JavaScript:search_site()">

</form>



</center>

</body>

</html>

 

http://codeprogrammer.blogspot.com

Formatting Numbers : Currency

The function you need to format currency is FormatCurrency. It is very easy to use and has been outlined and explained below:

FormatCurrency(NumToBeFormatted[, NumOfDigitsAfterDecimal, IncludeLeadingDigit, UseParensForNegativeNumbers])
- returns a formatted currency from the number specified with the appropriate number of digits specified after the decimal
- the first parameter is the only one that is required, although I recommend that you use at least include the second parameter.
- the last two parentheses are tristate values which means you need to specify a -1 for true, 0 for false, or -2 to use the computers regional settings.
- the IncludeLeadingDigit parameter is used to specify whether or not you want a leading zero to be displayed when the number is a fractional value.
- the UseParensForNegativeNumbers parameter is pretty self explanatory, if set to true it will put parentheses around negative numbers.
- this function also adds a '$' sign at the beginning of the output for you.

Example:

var1 = 5443.354
var2 = -877743.2345
Response.Write("var1 formatted = " & FormatCurrency(var1, 2, -1, -1) & "
")
Response.Write("var2 formatted = " & FormatCurrency(var2, 1, -1, -1))

Result:

var1 formatted = $5,443.35
var2 formatted = ($877,743.2)

http://yudhisuhendro.blogspot.com

Date and Time Functions and Formulas

Note: When your are working on formulas in the control's property dialog window or in query pertinent fields, right click and choose "Zoom", a special enlarged window will open to facilitate your work.

Note: Click on the Wizard's stick to open the Expression Generator. In the left bottom window of the dialog box, you can click on "Functions" to select any of the functions offered by Access.

==========
Here are the functions that I use and some examples:

Date, Between, And, Or, Day, Month, Year, Now, WeekDay, SerialDate, Left, Right, &, +, -, /, *, Len, IIF, Nz, Sum

Nz
The Nz function allows you to make calculations even if some of the controls are empty, or have a "Null" value". If you don't use this functions, you cannot sum a series of text boxes if even one as a Null value.
=NZ(text1,0)+NZ(text2,0)....

Using a format like "Fixed 2 decimals" in a text box, doesn't round up the result of a calculation to 2 decimals, it just shows the result with 2 decimals but calculates with all the decimals. Use the "Round" function even with "+" and "-", it rounds up your result to the number of decimals defined in the format. For some unknown reasons, I ended up with 0.999952 as the result of "1.3-0.3"
=round([textBox1]/textBox2

===================
Date and Time Functions and Formulas

Today
Date()

Yesterday
Date()-1

Do not use Now() because it includes date and time

Last 7 days (do not confuse with last week)
Between Date()-1 and Date()-7

Current week
Between Date()-(WeekDay(Date())-1) AND Date()+(7-WeekDay(Date()))


Last week
Between Date()-6-WeekDay(Date()) AND Date()-WeekDay(Date())

Week before last week
Between Date()-13-WeekDay(Date()) AND Date()-WeekDay(Date())-7

Current month:
Month([yourDateField])=Month(Now())

Current year:
Year([yourDateField])=Year(Now())

Last year (do not confuse with year ending today):
Year([yourDateField])=Year(Now())-1

Year ending today:
Between Date() And Date()-365
or more precisely:
Between Date() and SerialDate(Year(Now())-1;Month(Now());Day(Now()))+1

http://codeprogramer.blogspot.com

Table Tips

Do not use lists in the fields of the tables, you will be stuck with them along the way. Create List Boxes or Combo Boxes in forms and reports when you need them.

A double Primary Key means that you can have two records "Leclerc" but not two records "Pierre"/"Leclerc". Remember though that to be able to enter data through a query linked to such a table, both fields will have to be included in the query.

A numerical field is a field comprising numbers on which you will want to do mathematical operations. So, a telephone number, a postal code or an invoice number are text fields.

You can put numbers in both numerical and text fields of an Access table but the sorting results will be different:
In numerical fields: 1 ,2 ,3 ,4 ,5 ,6.......10.....20....30
In text fields: 1, 10, 11, 12....,2, 20, 21....3, 30, 31

Linking tables can present some limits. If you have a query within another query and both need to look at a linked table at the same time, ODBC cannot handle it. Sometimes, imported table are the solution especially in a datamart that is not used to enter data in the main database but to generate reports.

Query Tips

The query is where users and developers meet, it is the most vital part of the database.

Once the data have been queried, they can be organized and analyzed by the user on forms and reports to achieve the ultimate goal of a database: to inform.



Notes

If your query or your table feeds a form, there is no need to filter and sort whithin the query. You can manage these aspects in the "Filter/Sort" query of the form that you can access opening the form and going to the menu bar "Record/Filter/Advanced Filter/Sort". So, a query or a table can feed many forms.

There is no "If" in Access, there is "Iif". So, in the criteria zone or in expressions, use "Iif" instead of "If". This rule doesn't apply in VBA coding except when you are setting an object formula through coding.

For each report or form that you develop, always create a query. You never know when you will need a field from another table in your report or your from.

When your Information Management System includes reports in Excel, create a query in Access and use Microsoft Query only to get the results of the query created in Access. Access is superior to Microsoft Query to create queries.

Right click in the criteria or the field box and select "Zoom" when you are working with long criteria or formulas in calculated fields.

To replace a word in a long formula, double-click on it and enter the new value. It is much simpler than clicking at the beginning of the word and dragging the pointer over the word.