Form (4) – VB code analysis

First line (A) – “Private Sub cmdAdd_Click ()”. “Private” means that the code belongs to that object, which is the cmdAdd object. This is what we have called our button. “Sub” means that it must execute a subroutine. How many times must it execute the subroutine? Until it reaches the last line of code, that is “End sub” (B).

Second line – Declares the variable “Dim iRow as Long”

Third line – Declares the variable “Dim ws as Worksheet”

Fourth line – Set ws = Worksheet (“customers”), indicates that it must write the data to the worksheet “customers”.

Fifth line is in green color, so this is a comment, no code.

Sixth and seventh lines (C) – finds the last completed, used cell and then goes to the next row, which must be an empty cell. “. End (xlUp)”: finds the last used cell before an empty cell”. Offset (1, 0): one cell down. Should you have typed Offset (0, 1), then it would have moved a cell to the right.

In the following blocks of code (D), it checks if there is anything entered in the box, first for box “txtNaam”, next for box “txtAdres”, and so on.
If it finds a box which is empty after clicking the “cmdAdd button”, it will pop up a window stating that you must fill in the data.
The code “Me.” in VBA is a keyword that a form uses to refer to itself.
The code “Trim” removes spaces before and behind the text.

The following five lines of code (E) places the data in the database, or rather in the table in the worksheet “customers”.

The last five lines (F) of code remove the contents of the cells when we have clicked the “Add” button.

And finally we will add the code (G) to close the form for the “cmdClose button”.

When you typed something incorrectly in the code, for example, the name of the worksheet where you want to write the data, then VBA will inform you by opening an alert window (A) when you execute the code. The shortcut for this is the F5 key on your keyboard, remember. When clicking the “Debug” button, VBA will show you where this error is in the code (B).

Help with all misconceptions!

VBA is not exactly the same than VB. They are quite similar, but VB is a code that you write an application in, while VBA is the code that you use in conjunction with applications such as Excel, Word or Access.

Add button to open the form

And finally, I almost forgot, we still need to add a button to open the form.
For this, select the worksheet “invoice”, select the “Developer” tab, click the “Insert” button and choose a button. Whether you do this with a form control or an ActiveX control doesn’t matter as it is the VBA code that we need to adjust.

As an example I choose a form control and draw the button in the worksheet.

In the macro window that appears, I give the macro a name and click the “Record” button. In the next window I do not have a shortcut to give, because I have the button. So I click OK.

Stop immediately after recording the macro by pressing the “Stop Recording” button. Open the Visual Basic window and double click the module “Module2”.

Remove the code that has been written for the macro and enter the code below into the code window:

Sub CustomerAdd ()
frmCustomerData.Show
End Sub

Close the Visual Basic window

And test the button

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats