Visual Basic for Application E-Book

Pooja Polara

• Herausgeber: BookRix
• Kategorie: Bildung
• Sprache: Englisch

Veröffentlichungsjahr: 2017

Beschreibung

This book is written to reduce work which is done on excel. you can use this book to create appliation from microsoft office word, excel.

Das E-Book wird angeboten von und wurde mit folgenden Begriffen kategorisiert:

Leseprobe

Vishal Polara, Pooja Polara

Visual Basic for Application

BookRix GmbH & Co. KG81371 Munich

Table of Contents

Chapter 1: Introduction of VBA

Introduction

1.1 Introduction

Visual Basic for application (VBA) is a programming language developed by Microsoft which is a subset or we can say part of visual basic, as we know visual basic is a programming language so VBA support some of the object oriented features of visual basic. This is the era of automation so to make user work easier Microsoft includes VBA in each Microsoft Office application. Using VBA code in excel you can do so many calculation for example if you want to change font of text or you wants to apply color on text you can do easily and accurately than manually.

VBA will not only provide automation of task but it will also reduce human effort to perform various task, it provide functionality to create user interface using msgbox, dialog box and user forms. You can create your own application within office application to make your work easier, as we know it is the product of Microsoft it is possible to access other Office application for example working with word you can access outlook express.

This chapter is about basic knowledge of VBA and how VBA is different from VB. It also covers how to deal with macro recorder.

1.2 Difference between VB and VBA

In VB all kinds of objects available while in VBA application specific objects available.

VB GUI more user Friendly compare to VBA GUI.

VB can compile your program into an executable binary while VB cant’t.

VB IDE is more powerful then IDE of VBA.

VB use to create stand alone application while VBA can’t use for stand alone application.

VB programs give quick response with respect to VBA programs.

1.3 Advantages of VBA

It is easy to use and Versatile Language

It will reduce overall time as well as cost of development

Macro created using macro recorder take more time compare to macro created using VBA

Macro created using macro recorder provides limited functionality while VBA macro provide more functionality for example you can take chart of excel while working in word application.

Macro created using macro recorder doesn’t provide user choice to run different set of action while VBA allows user to choose action and control on each and every step of code.

1.4 Introduction to macro

Macro is a series of command that execute in your application to performed specified action. Macro is basically used to automate task of user. Two ways you can apply macro automatically or manually by clicking on button or using some shortcut key which apply at the time of creation of macro.

There are two way to create a macro

Starts the macro recorder which will going to record all the task that you perform when macro runs

Open visual basic editor write series of command that perform specific action.

In the Next section you will learn how to run macro after recording it.

1.4.1 Working with Macro

Macro is generally used to automate simple task, developer are generally avoid use of macro in complex task instead of use of macro they use VBA code so now so many questions Aries about macro like..In this section you learn to perform various operations on a macro

How to Record Macro?

How to Run Macro?

How to do Editing?

How to Assign Macro to toolbar button?

How to delete macro?

Let’s go to learn everything in detail.

1.4.2 Recording and Running a Macro in Word

It is easy to record macro using macro recorder you just go to the developer tab of office application if it is not visible then go to the word option there you find popular menu in that just checkmark on developer ribbon it becomes visible now click on record macro your macro recorder get start now perform action that gets recorded as VBA code you can see that code using edit functionality of macro recorder.

Here we will learn recording of macro in two office application word, excel.

Let’s go to learn how to record macro stepwise:

Click on Start button > All Programs > Microsoft Office > Microsoft Office Word 2007. This opens a blank word document.

Go to the developer tab and then click record macro button from the ribbon as shown in figure 1:

Figure 1: Developer tab and macro button

Enter Macro name in textbox like Change_Font and check all documents (normal.dotm) is selected in Store macro in as show in figure no 2.

Figure 2: Recording Change_Font macro

If you want to use code for other document then do not assign macro to button.

Enter Description for the macro it is optional.

Click on OK button to start recording as show in figure no 2.

Click on Pause Recording button on the developer tab to pause recording as shown in figure no 3.

Figure 3: Pause Recording of a Macro

Enter the sample text in document.

Now click on resume recorder button from developer tab like figure 4.

Figure 4: Resume recording and writing of text.

Now select Hello world using mouse or shift + right arrow key.

Go to the home menu select times new roman font and font size 12 now stop recording

Insert another text , select that text , click on macro button from developer tab as shown in figure 5 it will shows menu like figure 6 select your macro and click on run that will change font of newly added text.

Figure 5: Apply Macro using Macro button

Figure 6: Select Created Macro by Clicking on Macro button

1.4.3 Recording a Macro in Excel

Here we create macro in excel same way as we created in word. All other step is same like how to see developer ribbon.

This are the step to record macro in excel:

Click Start > All Programs > Microsoft Office > Microsoft Office Excel 2007 open a blank workbook.

Enter some text in Excel Sheet as show in figure 7.

Figure 7: Enter Text in Excel Sheet

Select developer tab click on record macro.

Enter the macro name as shown in figure 8.

Figure 8 Recording of Macro in Excel

Select Personal Macro Workbook from store macro in. write down description for a macro.

Click on Ok button that start recording of macro select text whose color you want to change go to home tab of excel select font color option select color now color of your text get change.

Click on Stop recording button.

Insert new text in that cell of Excel Sheet, Select text, Click on Macro button that will shows you Menu like figure 10 now select your macro and click on run that will change color of your text to run macro as shown in figure 9.

Figure 9: Apply macro on First Cell of Excel sheet

Figure 10: Select Macro Name after clicking on macro button

1.4.4 Editing a Macro

Here in this section we see how to edit a crated macro.

To edit macro click on macro button that open dialogbox now select your macro name and click on edit button of dialogbox.

1.4.5 Assigning a macro to toolbar button

Here we see how to assign button to a macro.

Let’s see the step.

Click on record macro button you will get following dialogbox.

Figure 11: Click on button

Click on Button as shown in figure 11.

Now select macro to which you want to assign button as shown in figure 12

Figure 12: Add macro to toolbar

Now select macro and click on modify button to assign symbol to macro.

Figure 13: Assign button to a macro

Now click on ok button so you can see symbol like figure no 14.

Figure 14: Display Selected Symbol

1.4.6 Deleting a Macro

Deleting of macro is done in same as editing of a macro. Just click on macro recorder button select a macro name that you want to delete.