VBA Tools and Techniques – VBA Tips

Developing an Application requires the technique. Unlike the other language, VBA is a language that is special for Microsoft Office Application, which is built by Microsoft. The technique in VBA is the quite the same as programming by Microsoft likes VB.net, C#. For Visual Basic Application (VBA), it requires four steps to start with. These techniques include designing an interface, setting property to each control, implementing or coding, and testing.

Designing an interface:

Designing interface is important. It helps you to know what you will deal with. In order to design an interface you should know some controls that exist in the controls group.

Here is some basic control

Text Box control, sometime called an edit field or edit control, displays the information entered at design time, entered by user, or assign to the control at run time.

Label is a graphical controlthat you can use to display text that a user cannot change directly.

Command Button control is used to begin, interrupt, or end a process, when chosen; A Command Button appears pushed in and so is sometime called a push button.

Combo Box control is the same as textbox except it provide user to select data from the list for inputting without typing

List Box is use to display data in a list.

Option Group: use for grouping data or function.

Option Button uses grouping data that user select only one from that values. Option Button is true when it is selected otherwise is false.

Check Box is similar to option Button but user can select more than one.

Image Box is used to display image

Setting Property to Each Control

Setting Property is the step where you name or object within your interface. Setting Property helps you to know what your object is and the name of your object is easy to remember. In order to setting property you need the conversion of object name. The following is the most common conversion in VBA.

Object	Prefix	Example
Form	frm	frmSum
CommandButton	cmd	cmdSum
Label	lbl	lblInputX
Text Box	txt	txtResult
Check Box	chk	chkOption
Menu	mnu	mnuEdit

Implementing (Coding)

Coding is to add event or procedure to you controls. This event can be Button click event, mouse drag and drop, text change… etc For example:

.Private Sub cmdSum_Click()

   txtSum = CInt(txtInputX.Text) + CInt(txtInpuY.Text)

End Sub

Testing and Debug code

Testing is the step that you must do to find all bugs in your code. In the Testing Step, you can see errors and how your code work through intermediate window and Watch window in the VBA Editor Explorer.

Example Design interface and Setting Properties

1. Start with new form object

2. Start Drawing Some Control

3. Setting Properties to each control

Form1

Name	frmSum
Caption	Sum Two Number

Label1

Name	lblInputX
Caption	InputX

Label2

Name	lblInputY
Caption	InputY

Label3

Name	lblResult
Caption	Result

TextBox1

Name

txtInputX

TextBox2

Name

txtInputY

TextBox3

Name

txtResult

CommandButton1

Name	cmdSum
Caption	Sum

CommandButton2

Name	cmdCancel
Caption	Cancel

After that, our form would look like the following form

In summary, VBA require you to know how to deigning interface, setting property to control, and implementing or coding so that you can easily work with VBA. 

Read More...

Using Code Window VBA

The VBA Code Window is where you write, edit, and view the code. The Code Window is like a word processor or text editor. For example, you can type text, use the backspace or delete text. And just like in Word, you can press Tab to indent the text, select text by dragging the mouse point to it, and copy and paste text.
As mentioned, the code window is all really a small word processor or text editor. But word processors tend to be oriented around paragraphs of text, whereas the Code Window is built for typing individual lines of code. Unlike word processors – where you do not press Enter until you get to the end of paragraph – in the Code window, you press Enter at the end of each line you type.

When you type a line of VBA code and press Enter, The Visual Basic Editor compiles that line of code. For now, you can think of compiling as testing the line of code to see whether it works. If you type the same line at random in the Code Window – pr even if you try to type a legitimate line of code but make a mistake – you will see the compile error message, as shown in below Figure.

Earlier, we talk about ways of dealing with compile errors. In the next article, we will get to telling on how to write code. For now, just realize that if you are type anything other than a valid line if VBA code into the Code Window, you can see a compile error message as soon as you press Enter. So don’t waste your time trying to type text at random into the Code Window.

Read More...

How to use Property Window VBA

In this article, I would like to show how to use Property Window in VBA. The Property window in the VBA Editor can be quite perplexing because it displays the property of whatever object is selected in Word, Excel, and of course Access, the property window might show nothing. That is often the case when you are working with standard modules because the standard module does not tied any particular object or event.

To illustrate how thing tie together, the following Figure shows a portion of a form, In Design View, in Access. A Form is selected. In the VBA Editor which also appears in this figure, The Properties for that selected sub-form appear in the VBA Editor Properties Window.

In this figure, you also see an example of how Project Explorer might look in a database that already contains some modules. The modules whose name begin with the world Form_ are all the class modules that are attached to forms in the Database.

The most important thing about the Property window is that it is optional, and you really don’t need it taking space in your VBA editor when you are not using them. Most of time, you probably won’t use it, so feel free to close it.

Read More...