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