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...

Why use VBA in Word and What is VBA's benifits in Word

VBA – Visual Basic for Application that is built especially for Microsoft office including Word, Excel, PowerPoint, Outlooks, and of course Access. What VBA can do in Microsoft word, maybe is what that you want to know. VBA in Microsoft Word is known as Macro. Macro is like a word command. There are a lot of word command in Microsoft word such as Copy, Paste, Align Left, Align Right, Align Center and mores. A Macro contains VBA code that performs the specific task in Microsoft Word.

Macro is very special. All Word Command can do, it also can do. So what is the special of Word VBA and why do users need to use Macro. It is that a Macro can tie or bind many procedures of VBA code to a single Macro or a single button. This means that Word command can only perform a task one times while Macro performs many tasks one times. For instance a Word Command can copy the text but a Macro can copy and Paste the text. So the Macro's benefits are to improve the work of user with Microsoft Word, performs automatically task, and perform many tasks at the same time in Microsoft Word.

Some Example of VBA command

VBA Code: Copy and Paste all text in Word

Sub CopyAndPaste()

    Selection.HomeKey      'go to first page of the document

    Selection.WholeStory     'put the selection to all text

    Selection.Select           'Select all the text'

    Selection.Copy       'copy the text

    Selection.PasteAndFormat     'paste the text to Word Document

End Sub

VBA Code: Binding procedures to a single Macro

Sub Main()

    dim a as integer

    a= Multiply(2,3)

    PrintResult(a)

End Sub

Function Multiply(a as integer, b as integer)

    Multiply=a*b

End Function

SubPrintResult(result as String)

    debug.print result

End Sub

In this example, the first function called Main, it bind the other two function Multiplyand PrintResult. When we run the Macro Main the two procedures Multiplyand PrintResult are called to perform its tasks.

Read More...

How to use Intermediate Window in VBA

The Intermediate window in the Visual basic Editor allows you to run the code at any time, right on the spot. This window is sometimes referred to as the debug window because it is mainly used for testing and debugging code (Remove the error from code). If the Intermediate window is not open in the Visual basic editor, you can bring it out of hiding at any time by choosing View Intermediate Window from the Visual Basic Editor menu’s bar.

The Intermediate Window allows you to test expression, run VBA procedure that you created and more. You will see the practical example throughout this article. To get you feet wet, test this example expression in the intermediate window. Just bear in mind that a VBA expression is any formula. For instance the simplest expression in the world is probably 1+1, which (as just about everyone knows) result is 2.

Testing Expression in Intermediate Window

To test an expression in the Intermediate Window, do the following

1. Click inside the Intermediate Window:

You need your cursor in the Intermediate Window

2. Type a question mark (?) follow by a space and the expression that you want to test and press Enter

For Example, Click in the Intermediate Window and then type ? 1+1. The Intermediate Window immediately show the result – 2 – as shown in the below figure

 Figure 1: Intermediate Window

You might think of the? Character at the start of the line as asking the Intermediate Window “What is?” For Example, if you think of ? 1+1 as meaning “What is 1 plus 1?”, it stands to reason that the Intermediate Window would return the result 2. After all, 1+1 is 2.

When you start actually writing VBA code, you will use the Intermediate Window to test and debug your code. For now, just know that the Intermediate Window is another optional pane in the Visual Basic Editor that you can show or hide as you needed.

Read More...

What is VBA Procedures

VBA procedure is a series of instruction written in VBA code that tell an application (like Access) exactly to perform a specific task. In VBA code, each step in the procedure is a single line of code (called statement). For instance, In Microsoft Access when the Access executes a procedure, it does so step-by-step, from the top down. Access does whatever the first statement tells it to do. Then it does the second statement tells it to do, and so forth, until it get to the end of the procedure.

Exactly when Access executes procedure is entirely up to you. You can tie the procedure to some event that happens on-screen. For example, you might want a procedure to perform its task as soon as some – one click a button. Or perhaps, you want your procedure to do its task whenever someone types an email address into a form. For now, just realize that you can tie any procedure that you have created to any event you like.

When the event to which you have tied your procedure occurs, Access calls the procedure. What that means is that Access does exactly what the VBA code tells it to do.

What is a procedure?

1. An event, such as clicking a button is called a procedure.

2. Access executes the first line in the called procedure then is executes the second line in the procedure; and so on

3. When Access encounters the end of procedure (End of Sub or End Function), it just stop executing the code and return to the normal state.

Some Sample of procedure

Example 1: Sum of Two Integer Value

Function SumTwoValue(a as integer, b as integer)

    SumTwoValue=a+b

End Function

Example 2: Print the Value 0 to 10

Sub PrintValue()

    Dim i as integer

    For i=0 to 10 step 1

        Debug.print i

    Next i

End Sub

That is all for all VBA procedure. We are welcome to any comments.

Read More...

Where we can write VBA

Likes other programming languages, they need the editor to write the Code. For VBA programming, it has its own editor that is built-in the Microsoft Office Application. In order to go the VBA Editor in Word, Excel, Power Point, Outlooks or Access, we can go to Developer Tap and Click on the Visual Basic or you can use Alt+F11 to go to VBA Editor. With the VBA editor we can write the VBA Code in Standard Module or Class Module.

Standard

A page that contains VBA code that is accessible to all objects in the Microsoft application. A standard Module always exists in the Modules Group in the Navigation Pane.

Class

Class is a page of VBA code that is attached to every form and reports you create. You can also create a class module that appears in the Navigation Pane.

Difference between Standard Module and Class Module

The main difference between a standard module and a class module is that you can create an instance of your class module in code. A standard module contains procedures you can run from anywhere in your Word, Excel, Power Point, Outlooks, and Access. A class module contains code that is either attached to an existing form or worksheet etc… in its own entity in the Navigation Pane.

We just talk about the where we can write the VBA code. For the following figure, we will show where is the VBA is stored within Access Database.

Figure 1

Summary

We can write the VBA code in the VBA editor. In VBA editor contains

Standard: A page that contains VBA code that is accessible to all objects in the Microsoft application.

Class: a page of VBA code that is attached to every form and reports you create.

Read More...

What is VBA

Understanding VBA

Visual Basic is a programming language – a language for writing instructions that a computer can read and process. VBA is a programming language that is specially designed to work with the application program in Microsoft Office including Word, Excel, Power Point, Outlooks and of course, Access.

When you write text in a programming language as opposed to write in plain English, you are writing Code.  Programmers use the term Code to refer to anything that is written in a computer programming language. The whole trick to mastering VBA is finding out what all the various words in the language so that you can write the Code that tell Word, Excel, Power Point, Outlooks, and Access to perform a task.

VBA Sample Code

Public Function PCase(anyText)

    'Custom VBA function to fix all uppercase Letters

   

    PCase = StrConv(anyText, vbProperCase)

   

    IfLeft(PCase, 4) = "P.o." Then

        PCase = "P.O." & Mid(PCase, 5)

    End If

   

    IfLeft(PCase, 2) = "Mc" Then

        PCase = "Mc" & UCase(Mid(PCase, 3, 1)) & Mid(PCase, 4)

    End If

       

    IfLeft(PCase, 3) = "Mac" Then

        PCase = "Mac" & UCase(Mid(PCase, 4, 1)) & Mid(PCase, 5)

    End If

   

End Function

This is only the example to show the sample of VBA code. In the next article we will go to the depth of what VBA is in the Microsoft office.

That is all for the introduction to VBA.

Read More...