Testing Macros in the Visual Basic Editor for Excel

Testing the VBA procedure step by step

Testing is the most time-consuming part of any VBA project. During the development of a project you will use 20% of your time analysing and designing, 15% programming and 65% testing.

During the testing phase, you will correct bugs, typos and the logical errors. More importantly you will improve your original project, fine tune it, discover better ways to do things and add code.

You have created your first macro and tested it using the "Run" button. You can also test a macro step by step.

Print this page, open Excel and a open a new workbook. Use ALT/F11 to open the Visual Basic Editor as you learned in topic.

(i) Go to Excel and make sure that cells A1, A2 and A3 of Sheet1 are empty.

(ii) In VBE go to the Code window of Sheet1 and copy/paste the following macro:

Sub PreTest()
Range("A1").Value = 09
Range("A2").Value = 22
Range("A3").Formula = "=A1+A2"
Range("A1").Select
End Sub

(iii) Click anywhere within the macro and then press the F8 key at the top of your keyboard. VBE highlights the first line of code in yellow.

(iv) Right-click on the small yellow arrow and see a menu appear

(v) Press on "F8" a second time. No line has been executed yet and if you go to Excel you will see that cells A1 to A3 are still empty. The next time you press "F8" , VBE will execute the yellow-highlighted line.

(vi) Press "F8" a third time. The yellow-highlighted line is now "Range("A2").Value = 66". VBE has executed the previous line "Range("A1").Value = 34" has been executed so if you go to Excel (ALT/F11) you will see 32 in cell A1.

(vii) Come back to VBE (ALT/F11) and press "F8" again. Go to Excel and see what happened in cell A2.

(viii) Come back to VBE (ALT/F11) and press "F8" again. Go to Excel and see that there is a formula in cell A3.

(ix) Come back to the VBE (ALT/F11) and press "F8" again, cell A1 is now selected in Excel.

(x) Press "F8" again. Nothing happens in Excel but "End Sub" is highlighted in yellow

(xi) Press "F8" again. Nothing happens in Excel no more lines in VBE are highlighted in yellow.

The macro hac been tested, the test is over.

In the code change the addresses A1, A2 and A3 respectively to B1, B2 and B3. Test the macro again. Do it as many times as you want.

You have tested a macro step by step. In the downloadable tutorial you will learn how to test parts of a macro, how to come back a few lines, make changes and re-execute these lines. You will also discover this most important functionality, how to share the screen between Excel and VBE.

The Code Window in the Visual Basic Editor of Excel

The Code Window is where 90% of the VBA work is done; writing VBA sentences, testing your VBA procedures (macros) and modifying them when needed.

To illustrate everything that you can do in the Code window we will start by creating a small macro in an empty workbook.

There are plenty of other operations that you can execute in the code window. For example, you can test a macro line by line (step by step), go back a few lines and make corrections, use breakpoints to test only part of a macro.

The Properties Window in the Visual Basic Editor of Excel

The Properties window shows you the properties of the component that is selected in the Project Window (single click). For example in the new workbook if you single click on "Sheet1" in the Project Window you see the properties of sheet1 in the Properties Window.

As you can see, a worksheet has 12 properties that you can change in this Properties window. Notice that there are 2 "Name" properties. On the first line there is the programmatical name of the sheet (Sheet1). You will discover later the advantages and disadvantages of changing this property. The second "Name" property (9th line) is the name (or caption) that appears on the tab of the sheet in Excel.

Changing the "Name" Property. You will also learn how to name your modules and work with a few other properties of the objects appearing in the Project Window.

Close the VBE and close Excel without saving anything.

The Project Window in the Visual Basic Editor of Excel

As you can see, the Project window shows you all the workbooks that are open ("Book1") in the example below) and their components. You can use the + and - signs to show the details.

A new Excel workbook includes three sheets and another component named "ThisWorkbook" is a component in which you will store the macros ( also called VBA procedures) that should start automatically when the workbook is opened.

We will now complete a brief exercise to learn how easy it is to work within the Project Window.

The Visual Basic Editor in Excel (VBE)

When you want somebody to do some work for you, you open your Email program and you send him a message in a language that he understands (English, Spanish, French, etc). When you want Excel to do some work for you, you open the Visual Basic Editor and you write the instructions in a language that Excel understands VBA (Visual Basic for Application).

You will develop, test and modify VBA procedures (macros) in the Excel Visual Basic Editor (VBE). It is a very user-friendly development environment. VBA procedures developed in the VBE become part of the workbook in which they are developed and when the workbook is saved the VBA components (including macros, modules, userforms and other components) are saved at the same time. So, when you send the workbook to the "Recycling Bin" the VBA procedures (macros) are destroyed.

There are no risks to your computer or to Excel in completing the task below. At any time if you feel uncomfortable, just close Excel without saving the workbook and try again later.

For users of Excel 1997 to 2010: The first thing that you need to do is to make sure that the security level of Excel is set at either "Low" or "Medium" so that you can use the macros (VBA procedures) that you develop. From the menu bar of Excel select "Tools" then "Macro" then "Security" and select "Medium".

For users of Excel 2007 to 2010: From the "Developer" ribbon click on the "Macro Security" button. Check the second level "Disable all Macros with Notification" and you are set.

Setting up the Visual Basic Editor in Excel (VBE)
The Visual Basic Editor is a program within Excel that allows you to communicate with Excel. We will open it and start by setting it up so that working within it becomes easy and efficient.

Print this page, open Excel and open a new workbook (Book1).

On your keyboard press the "ALT" key (left of the space bar) and hold, strike the "F11"key (the F11 key is at the top of your keyboard).You now see the Visual Basic Editor. Again press "ALT/F11" and you are back into Excel. Use the "ALT/F11" key to go from Excel to the VBA and back.

Close the VBE and close Excel without saving anything.

Microsoft Office 2010 - Removed features

The following features are removed from Microsoft Office 2010.

Removed from the entire suite
Microsoft Office Document Imaging application
Microsoft Office Document Scanning application
Office Diagnostics tool
Support for MSXML version 5
Research and Reference pane for Internet Explorer
Features removed from Microsoft Word
Smart Tag auto-recognition
Person Name smart tag
AutoSummary feature
Support for Word Add-in Libraries (WLL)
Features removed from Microsoft Access
Access Calendar ActiveX control
Replication Conflict Viewer
Data access pages
Features removed from Microsoft Outlook
ANSI offline Outlook data files (.ost) for Exchange synchronization
Calendar rebasing tool
DAV connectivity for HTTP account types
Exchange 2000 connectivity
Exchange Message Security feature support
Postmarks
Features removed from Microsoft PowerPoint
Macro recorder
Save as Web Page feature
Features removed from Microsoft Publisher
The ability to create new Web Publications

Microsoft Office 2010 - New features and Improvements

Office 2010 is more "role-based" than previous versions. There are features tailored to employees in "roles such as research and development professionals, sales persons, and human resources." In its Internet implementation, Office 2010 incorporates features of SharePoint Server and borrows from "Web 2.0" ideas.

Microsoft Office 2010 includes updated support for ISO/IEC 29500:2008, the International Standard version of Office Open XML (OOXML) file format. Office 2010 provides read support for ECMA-376, read/write support for ISO/IEC 29500 Transitional, and read support for ISO/IEC 29500 Strict. In its pre-release (beta) form, however, Office 2010 only supported the Transitional variant, and not the Strict. The intent of the ISO/IEC is to allow the removal of the Transitional variant from the ISO/IEC compliant version of the OOXML standard. Microsoft Office 2010 supports OpenDocument Format (ODF) 1.1, which is an OASIS standard.

New features also include a built-in screen capture tool, a background removal tool, a protected document mode, new SmartArt templates and author permissions. The 2007 "Office Button" was replaced with a menu button that leads to a full-window file menu, known as Backstage View, giving easy access to task-centered functions such as printing and sharing. A notable accessibility regression from 2007 is that the menu button scores worse with the Fitts's law accessibility calculation than previous versions. A modified Ribbon interface is present in all Office applications, including Office Outlook, Visio, OneNote, Project, and Publisher. Office applications also have functional jumplists in Windows 7, which would allow easy access to recent items and tasks relevant to the application. Features of Office 2010 include:

Ribbon interface and Backstage View across all applications
Background Removal Tool
Letter Styling
The Word 2007 Equation editor is common to all applications, replacing Microsoft Equation Editor 3.0
New SmartArt templates
New text and image editing effects
Screen Capturing and Clipping tools
Live collaboration functions
Jumplists in Windows 7
New animations in PowerPoint 2010
A new feature in Microsoft Office 2010 is Outlook Social Connector, which allows users connect to and receive updates from their social network inside Microsoft Outlook.

When users view their emails a name, picture, and title is available for the person they are contacting. Upcoming appointments can also be viewed with this new feature and users can request friends. Outlook Social Connector currently supports Facebook, LinkedIn, MySpace and Windows Live Messenger.

Microsoft Office 2010 Introduction

Microsoft Office 2010 (also called Office 2010 and Office 14) is a productivity suite for Microsoft Windows, and the successor to Microsoft Office 2007. Office 2010 includes extended file format support, user interface updates, and a changed user experience. With the introduction of Office 2010, a 64-bit version of Office is available, although not for Windows XP or Windows Server 2003. Office 2010 does not support Windows XP Professional x64 Edition.

On April 15, 2010, Office 2010 was released to manufacturing, with those Volume Licensing customers who have Software Assurance being able to download the software from April 27, 2010. The suite became available for retail as well as online purchase on June 15, 2010.

Office 2010 marks the debut of free online versions of Word, Excel, PowerPoint, and OneNote, which work in popular web browsers (Internet Explorer, Mozilla Firefox, Google Chrome and Safari, but not Opera). A new edition of Office, Office Starter 2010, replaced the low-end home productivity software, Microsoft Works.

Microsoft's update to its mobile productivity suite, Office Mobile 2010, will also be released for Windows Phones running Windows Mobile 6.5 and Windows Phone 7. In Office 2010, every application features the Ribbon, including Outlook, OneNote, Publisher, InfoPath, SharePoint Workspace (previously known as Groove), and the new Office Web Apps.

Procedures and Access Levels

Like a variable access, the access to a procedure can be controlled by an access level. A procedure can be made private or public. To specify the access level of a procedure, precede it with the Private or the Public keyword. Here is an example:-

Private Sub CreateCustomer()
Dim strFullName As String
strFullName = "Chidambaram Palaniappan"
End Sub

The rules that were applied to global variables are the same:

Private: If a procedure is made private, it can be called by other procedures of the same module. Procedures of outside modules cannot access such a procedure.

Also, when a procedure is private, its name does not appear in the Macros dialog box.

Public: A procedure created as public can be called by procedures of the same module and by procedures of other modules.

Also, if a procedure was created as public, when you access the Macros dialog box, its name appears and you can run it from there itself.

Calling a Sub Procedure

Once you have a procedure, whether you created it or it is part of the Visual Basic language, you can use it. Using a procedure is also referred to as calling it.

Before calling a procedure, you should first locate the section of code in which you want to use it. To call a simple procedure, type its name. Here is an example:

Sub CreateCustomer()
Dim strFullName As String
strFullName = "Chidambaram Palaniappan"
End Sub

Sub Exercise()
CreateCustomer
End Sub

Besides using the name of a procedure to call it, you can also precede it with the Call keyword. Here is an example:

Sub CreateCustomer()
Dim strFullName As String
strFullName = "Chidambaram Palaniappan"
End Sub

Sub Exercise()
Call CreateCustomer
End Sub

When calling a procedure, without or without the Call keyword, you can optionally type an opening and a closing parentheses on the right side of its name. Here is an example:

Sub CreateCustomer()
Dim strFullName As String
strFullName = "Chidambaram Palaniappan"
End Sub

Sub Exercise()
CreateCustomer()
End Sub

Introduction to Sub-Procedures

A sub procedure is an assignment that is carried but does not give back a result. To create a sub procedure, start with the Sub keyword followed by a name (like everything else, a procedure must have a name). The name of a procedure is always followed by parentheses. At the end of the sub procedure, you must type End Sub. Therefore, the primary formula to create a sub procedure is:

Sub ProcedureName()

End Sub

The name of a procedure should follow the same rules we learned to name the variables. In addition:

If the procedure performs an action that can be represented with a verb, you can use that verb to name it. Here are examples: show, display To make the name of a procedure stand, you should start it in uppercase. Examples are Show, Play, Dispose, Close. You should use explicit names that identify the purpose of the procedure. If a procedure would be used as a result of another procedure or a control's event, reflect it on the name of the sub procedure. Examples would be: afterupdate, longbefore.
If the name of a procedure is a combination of words, you should start each word in uppercase. An example is AfterUpdate
The section between the Sub and the End Sub lines is referred to as the body of the procedure. Here is an example:

Sub CreateCustomer()

End Sub

In the body of the procedure, you carry the assignment of the procedure. It is also said that you define the procedure or you implement the procedure.

One of the actions you can in the body of a procedure consists of declaring a variable. There is no restriction on the type of variable you can declare in a procedure. Here is an example:

Sub CreateCustomer()
Dim strFullName As String
End Sub

In the same way, you can declare as many variables as you need inside of a procedure. The actions you perform inside of a procedure depend on what you are trying to accomplish. For example, a procedure can simply be used to create a string. The above procedure can be changed as follows:

Sub CreateCustomer()
Dim strFullName As String
strFullName = "Chidambaram palaniappan"
End Sub

Introduction to Procedures

A procedure is a section of code created to carry an assignment, separate from a spreadsheet, whose action can be used to complement a spreasheet. You create the procedure by writing code. One of the advantages of a procedure is that, once it exists, you can access it when necessary and as many times as you want.

There are two categories of procedures you will use in your spreadsheets: those that are already installed with Microsoft Excel and those you will create.

In the Visual Basic language, like most other languages, there are two types of procedures: functions and sub procedures.

SendKeys

If you want to do a screen capture using VBA, (simulating the PrtScr key), you have to use:

WordBasic.SendKeys "__"

In "__" we can enter any key values. Thanks!

FileProperties

If you want to intercept the FileProperties menu command, the only reliable way to do it is to use:

Sub FileProperties()
'your code here
WordBasic.FileProperties
End Sub

In fact, if you let Word create the code for you, using the method described here, the above code will be created.

FileCopy/FileCopyA

The VBA FileCopy statement will not copy files that are open. However, the WordBasic equivalent will (this is what is known as progress!).

The following works even if the file being copied is open:

If Left$(Application.Version, 1) = "8" Then
'Word 97
WordBasic.CopyFile FileName:="c:\OldTempDirectory\Temp1.doc", _
Directory:="C:\NewDirectory\Temp2.doc"
Else
'Word 2000 and above
WordBasic.CopyFileA FileName:="c:\OldTempDirectory\Temp1.doc", _
Directory:="C:\NewDirectory\Temp2.doc"
End If

ToolsBulletsNumbers

WordBasic allows you to remove all manually typed numbering from a selection using the old Word 2 command:

WordBasic.ToolsBulletsNumbers Replace:=0, Type:=1, Remove:=1

This is particularly useful for removing manually typed numbering from Headings in a document you have been emailed, prior to applying List Numbering. If you go into Outline View, set the Heading Level to the number of levels you need to remove the typed numbering from, and run the above line, it will just remove numbering from those Headings and will leave the body text alone.

DisableAutoMacros

If you are running a macro that opens (or creates) several files, the last thing you may want is for an AutoOpen (or AutoNew) macro to fire up each time. WordBasic has a means of preventing this, which VBA never copied.

WordBasic.DisableAutoMacros 1 'Disables auto macros
WordBasic.DisableAutoMacros 0 'Enables auto macros

This command is also very useful when launching an instance of Word from another application, or from VB, when you will generally not want any AutoExec macros to fire

SortArray in VBA

Sub SortTest()
Dim ct(2) As String
Dim i As Long

ct(0) = "orange"
ct(1) = "apple"
ct(2) = "banana"
WordBasic.SortArray ct()

For i = 0 To 2
Debug.Print ct(i)
Next i

End Sub

File/Directory Functions in VBA Functions:-

ChDir
Dir
FileLen
MkDir
ChDrive
FileDateTime
GetAttr
SetAttr

Financial Functions in VBA Functions:-

DDb
Mirr
PPmt
Syd
FV
NPer
PV
IPmt
Npv
Rate
Irr
Pmt
SLn

Data Type Conversion Functions in VBA Functions:-

CBool
CDate
CInt
CStr
CByte
CDbl
CLng
CVar
CCur
CDec
CSng

Lookup / Reference Functions in VBA Functions:-

Choose
Switch

Date & Time Functions in VBA Functions:-

Date
DateValue
Month
Weekday
DateAdd
Day
MonthName
WeekdayName
DateDiff
Format
Dates
Now
Year
DatePart
Hour
TimeSerial
DateSerial
Minute
TimeValue

Information Functions in VBA Functions:-

IsDate
IsError
IsNull
IsNumeric

Numeric / Mathematical Functions in VBA Functions:-

Abs
Fix
Rnd
Tan
Atn
Format
Numbers
Round
Cos
Int
Sgn
Exp
Log
Sin

String Functions in VBA Functions:-

View an Listing in VBA:-
========================

Asc
InStrRev
Replace
Trim
Chr
LCase
Right
UCase
Concatenate with &
Left
RTrim
Val
CurDir
Len
Space
Format
Strings
LTrim
Str
InStr
Mid
StrConv

Assigning a Macro to a Shortcut Key:-

Word allows you to assign macros or commands to specific key combinations. These key combinations are referred to as shortcut keys, and when used they result in the macro or command being executed. When you first create a macro by recording it, Word gives you the opportunity to assign the macro to a specific key combination. If you later want to change the key combination, you can follow these steps:

1. Select Customize from the Tools menu. You will see the Customize dialog box.
2. Click on the Keyboard button. Word displays the Customize Keyboard dialog box. (Click here to see a related figure.)
3. Scroll through the Categories list and select the Macros category. The list at the right side of the dialog box changes to show the currently available macros.
4. In the Macros list, select the macro you want assigned to the shortcut key.
5. With the insertion pointer in the Press New Shortcut Key box, press the shortcut key you want to use. For instance, if you want to use Ctrl+Alt+J, press that.
6. Just below the Press New Shortcut Key box you can see whether the shortcut key is already assigned to a different function.
7. Click on Assign.
8. Repeat steps 4 through 7 for each change you want to make.
9. Click on Close.

Aligning a Paragraph in a Word Macro:-

Word allows a rich set of formatting attributes for text in a document. You can control the alignment of a paragraph by using the following VBA statement:

Selection.Paragraphs.Alignment = position

where position is one of the constants shown in the following table:-

Constant Result
wdAlignParagraphLeft Formats the current paragraph as left justified
wdAlignParagraphCenter Formats the current paragraph as centered
wdAlignParagraphRight Formats the current paragraph as right justified
wdAlignParagraphJustify Formats the current paragraph so it expands to the left and right margins

Adding a Macro to a Toolbar:-

For instance, you can create a macro, and then add it to a toolbar. How you do this depends on which version of Word you are using.

If you are using a version of Word prior to Word 2007, then follow these steps:

1. Choose Customize from the Tools menu. Word displays the Customize dialog box.
2. Make sure the Toolbars tab is selected. (Click here to see a related figure.)
3. In the list of toolbars, make sure there is a check mark beside the toolbar to which you want your macro added. The check mark ensures that the toolbar is displayed on the screen.
4. Click on the Commands tab.
5. In the list of Categories, choose the Macros entry. Your macros should then appear in the Commands list.
6. In the Commands list, select the macro you want assigned to a toolbar.
7. Using the mouse, drag the macro from the Commands list to the location on the toolbar where you want it to appear.
8. When you drop the macro, it appears on the toolbar.
9. To add more macros, repeat steps 6 through 8.
10. Click on Close.

Word 2007 is different; it doesn't technically have toolbars like older versions of Word had. You can't make additions or changes to the ribbon, but you can add macros to the Quick Access toolbar. Follow these steps:

1. Click the Office button and then click Word Options. Word displays the Word Options dialog box.
2. At the left side of the dialog box, click the Customize option.
3. Using the Choose Commands From drop-down list, choose Macros.
4. In the list of available macros, select the one you want assigned to the Quick Access toolbar.
5. Click the Add button. The command now appears at the right side of the dialog box.
6. Click the OK button. The command now appears on the Quick Access toolbar.

Accessing Objects in a Word Project:-

When you create a new application-level project for Word by using Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System (VSTO 2005 SE), Visual Studio automatically creates a ThisAddIn.vb or ThisAddIn.cs code file. You can access the Application object by using Me.Application or this.Application.

When you create a new document-level project for Word by using VSTO 2005, you have the option of creating a new Word Application or Word Template project. VSTO 2005 automatically creates a ThisDocument.vb or ThisDocument.cs code file in your new Word project for both Document and Template projects. You can access the Application and Document objects by using the Me or this object reference.

At first glance, there appears to be a lot of overlap in the Word object model. For example, the Document and Selection objects are both members of the Application object, but the Document object is also a member of the Selection object. Both the Document and Selection objects contain Bookmark and Range objects. The overlap exists because there are multiple ways you can access the same type of object. For example, you apply formatting to a Range object; but you may want to access the range of the current selection, a particular paragraph, section or the entire document.

The Application object contains the Document, Selection, Bookmark, and Range objects.

Word provides hundreds of objects with which you can interact. The following sections briefly describe the top-level objects and how they interact with each other. These include:

1. Application object
2. Document object
3. Selection object
4. Range object
5. Bookmark object

Object model:-

In computing, object model has two related but distinct meanings:

The properties of objects in general, in a specific computer programming language, technology, notation or methodology that uses them. For example, the Java object model, the COM object model, or the object model of OMT. Such object models are usually defined using concepts such as class, message, inheritance, polymorphism, and encapsulation. There is an extensive literature on formalized object models as a subset of the formal semantics of programming languages.
A collection of objects or classes through which a program can examine and manipulate some specific parts of its world. In other words, the object-oriented interface to some service or system. Such an interface is said to be the object model of the represented service or system. For example, the Document Object Model (DOM) [1] is a collection of objects that represent a page in a web browser, used by script programs to examine and dynamically change the page. There is a Microsoft Excel object model [2] for controlling Microsoft Excel from another program, and the ASCOM Telescope Driver [3] is an object model for controlling an astronomical telescope.

Variables and Constants

Variables and Constants:-
During the execution of some VBA macros there will be times when information that is either gathered from the user, returned by a function, or defined by the programmer that must be stored temporarily for use later on in the macro. Sometimes this information will change during the execution of the code and sometimes it will be static. Variables and constants are used to store this type of information. In the following macro CoName is a variable:

Sub Add_Footer()
CoName = InputBox("Name your Company?", "Add Company Name to Footer")
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = CoName
.CenterFooter = ""
.RightFooter = "&N"
End With
End Sub

Like a variable, a constant is a temporary holding place for some information that is used in a procedure. However, as the name implies a constant never changes. Constants must be declared. A declaration statement in a VBA macro is used to define the value of a constant. In the following macro the Company Name is declared in the first statement of the VBA code:

Sub Add_Footer()
Const CoName = “Accounting Software Advisor”
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = CoName
.CenterFooter = ""
.RightFooter = "&N"
End With
End Sub

The company name is enclosed in quotation marks. All literal text (this is what strings are referred to in VBA programming), which is placed in a procedure, must be surrounded by quotation marks.

Function & Procedure

Functions:-
A VBA function is a lot like a workbook function in an Excel spreadsheet. It performs a calculation and then returns the appropriate result. Functions provide information that is useful in building VBA procedures. In the previous example, the Msgbox function was used to display the path information on the screen.

VBA Functions should not be confused with Function Procedures. A Function Procedure is a series of Visual Basic statements enclosed by the Function and End Function statements. A Function procedure is similar to a Sub procedure, but a function can also return a value. A Function procedure can take arguments, such as constants, variables, or expressions that are passed to it by a calling procedure. If a Function procedure has no arguments, its Function statement must include an empty set of parentheses. A function returns a value by assigning a value to its name in one or more statements of the procedure.

Procedures:-
When you create VBA code inside an Excel workbook it will be stored as a “Module” within the workbook. Unlike, Lotus 1-2-3 macros, these modules are not entered in cells but are still a part of the workbook files. They can only be viewed by using the VBA Editor.


Code within a module is organized into procedures. A procedure tells the application how to perform a specific task. Use procedures to divide complex code tasks into more manageable units. There are three types of VBA procedures: Sub, Function, and Property.

The most common type of procedure is the Sub. A Sub procedure is a series of Visual Basic statements enclosed by the Sub and End Sub statements that performs actions but doesn't return a value. A Sub procedure can take arguments, such as constants, variables, or expressions that are passed by a calling procedure. If a Sub procedure has no arguments, the Sub statement must include an empty set of parentheses. The following is an example of a simple Sub:

Sub Center_Across_Selection()

' Center_Across_Selection Macro
' Macro recorded 4/19/2003 by Carlton Collins
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Style = "Comma"
Selection.Font.Underline = xlUnderlineStyleSingleAccounting
End Sub

Properties

Properties
Properties are used to describe an object. Some properties are read-only, while others are read/write. These properties describe Excel objects. For example, an Excel workbook has a particular path on your hard drive or network where it is saved. That path is a property of the workbook. That path is read-only as it cannot be changed without saving the file to a different location. Properties are separated from objects by periods just as methods are. The following sentence will display the current path of the Loan Calculator.xls file in an onscreen message box:

Msgbox Workbooks(“Loan Calculator.xls”).Path

Note: Msgbox is a function. Functions will be discussed later in this section.

If you want to set a read/write property equal to something, it changes the current value of that particular object. If you don’t set a read/write property equal to something, Excel will tell you the object’s current value. For example, the following sentence will set the sheet name of the first sheet in the workbook to “Cover”

Sheets(“Sheet1”).Name = “Cover”

Collections & Methods

Collections:-
Some objects are collections of other objects. For example, a workbook is a collection of all the objects it contains (ex. sheets, cells, ranges, charts, VBA modules, etc.). A VBA statement that makes reference to the workbook names “Loan Calculator.xls” would appear like this:

Workbooks(“Loan Calculator.xls”)

Methods:-
A method is an action that can be performed on an object. Excel VBA objects are separated from their methods by a period. For example, if you wanted to save a particular file as part of a VBA program you could include the following sentence in the code:

Workbooks(“Loan Calculator.xls”).Save

Object Oriented Programming & Objects

Object Oriented Programming:-
VBA is a structured programming language where sentences (called statements) are constructed of building blocks such as objects, methods, and properties. These VBA statements are grouped in larger blocks called procedures. A procedure is a set of VBA statements that performs a specific task or calculates a specific result. The first step to learning how to create procedures is to learn about the building blocks.

Objects:-
VBA is an object-oriented programming language, which means the statements you create in VBA act on specific objects rather than begin general commands. Excel is made of objects that you can manipulate through the VBA statements. The entire workbook file is an object, an individual sheet is an object, a range within a sheet can be an object, and an individual cell is an object. There are many more types of objects, and as you see objects can be containers for (called collections) other objects.

VBA Programming Basics

The VBA programming language provides the tools needed to create solutions when the macro recorder can’t get the job done. VBA is such a powerful programming language that you can (if you have the programming ability) create a solution to virtually any problem you encounter in Excel. In fact you can completely customize Excel through VBA and you can even change the entire Excel interface.

(i) Object Oriented Programming.
(ii) Objects.
(iii) Collections.
(iv) Methods.
(v) Properties.
(vi) Functions.
(vii) Procedures.
(viii) Variables and Constants.

Activate window by name in MS-Word

Sub GenerateGlossary()
Dim strSource As String
Dim strDestination As String
Dim strGlossaryName As String

strSource = ActiveWindow.Caption
strGlossaryName = "word"

Documents.Add
ActiveDocument.SaveAs FileName:=strGlossaryName, FileFormat:=wdFormatDocument
strDestination = ActiveWindow.Caption
Windows(strSource).Activate
End Sub

Active document paragraph in word.

Sub loopDemo()
Dim i As Integer
For i = 1 To ActiveDocument.Paragraphs.Count
Application.StatusBar = "formatting" & i & " out of " & ActiveDocument.Paragraphs.Count & "..."
Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdMove
Next i
End Sub

Turning Off Track Changes

Sub trac()
Dim blnTrackChangesOn As Boolean
blnTrackChangesOn = ActiveDocument.TrackRevisions
ActiveDocument.TrackRevisions = False
ActiveDocument.TrackRevisions = blnTrackChangesOn
End Sub

To delete all the files in a given directory:-

'Loop through all the files in the directory by using Dir$ function
Dim MyFile As String
MyFile = Dir$("c:\temp\*.*")
Do While MyFile <> ""
KillProperly "c:\temp\" & MyFile
'need to specify full path again because a file was deleted 1
MyFile = Dir$("c:\temp\*.*")
Loop

Kill statement can't delete readonly files:-

Dim KillFile As String
KillFile = "c:\temp\temp.doc"
'Check that file exists
If Len(Dir$(KillFile)) > 0 Then
'First remove readonly attribute, if set
SetAttr KillFile, vbNormal
'Then delete the file
Kill KillFile
End If

Font and Background Color in Excel VBA :-

Font and Background Color in Excel VBA:-

The format to set the font color is

cells(i,j).Font.Color=RGB(x,y,x), where x ,y , z can be any number between 1 and 255

For example

cells(1,1).Font.Color=RGB(255,255,0) will change the font color to yellow

The format to set the cell's background color is

cells(i,j).Interior.Color=RGB(x,y,x), where x ,y , z can be any number between 1 and 255

Here is the VBA code:-

Private Sub CommandButton1_Click()
Randomize Timer
Dim i, j, k As Integer
i = Int(255 * Rnd) + 1
j = Int(255 * Rnd) + 1
k = Int(255 * Rnd) + 1
Cells(1, 1).Font.Color = RGB(i, j, k)
Cells(2, 1).Interior.Color = RGB(j, k, i)
End Sub

Calculating BMI :-

The formula for calculating BMI is

BMI=wieght(/(height2)

Here is the VBA code:-

Private Sub Calc_BMI()
Dim weight, height, bmi, x As Single

weight = Cells(2, 2)
height = Cells(3, 2)
bmi = (weight) / height ^ 2
Cells(4, 2) = Round(bmi, 1)
If bmi <= 15 Then Cells(5, 2) = "Under weight" ElseIf bmi > 15 And bmi <= 25 Then
Cells(5, 2) = "Optimum weight"
Else
Cells(5, 2) = "Over weight"
End If
End Sub

Calculate the interest rate :-

To solve this puzzle, we need to calculate the initial investment based on the interest rate and the length of a period, usually in years. The formula is

WorksheetFunction.PV(rate, N, periodic payment, amount, due)

where rate is the interest rate, N is the length of the period and amount is the amount borrowed.

Here is the VBA code:-

Private Sub Calc_InterestRate()
Dim F_Money, Int_Rate, Investment As Double
Dim numYear As Single
F_Money = Cells(2, 2)
Int_Rate = (Cells(3, 2) / 100)
numYear = Cells(4, 2)
Investment = PV(Int_Rate, numYear, 0, F_Money, 1)
Cells(5, 2) = Format(-Investment, "$##,###,##0.00")
End Sub

Prime Number or Not :-

Private Sub Check_PrimeorNot()

Dim N, D As Single
Dim tag As String
N = Cells(2, 2)

Select Case N
Case Is < 2 MsgBox "It is not a prime number" Case Is = 2 MsgBox "It is a prime number" Case Is > 2

D = 2
Do
If N / D = Int(N / D) Then
MsgBox "It is not a prime number"
tag = "Not Prime"
Exit Do
End If
D = D + 1
Loop While D <= N - 1 If tag <> "Not Prime" Then
MsgBox "It is a prime number"
End If
End Select
End Sub

Calculated the Sum Passed & Failed :-

This program is to calculated the Sum Passed & Failed:-

Private Sub Calc_Sumpassed_SumFailed()
Dim rng As Range, i As Integer
Dim mark, sumFail, sumPass As Single
sumFail = 0
sumPass = 0
Set rng = Range("A1:A10")
For i = 1 To 10
mark = rng.Cells(i).Value
Select Case mark
Case Is < 50 sumFail = sumFail + mark Case Is >= 50
sumPass = sumPass + mark
End Select
Next i
MsgBox "The sum of Failed marks is" & Str(sumFail) & vbCrLf & "The sum of Passed marks is" & Str(sumPass)
End Sub

If (condition) Then Code statement :-

There are several ways to implement this code structure. The most basic uses just one line of code.

Private Sub GuessAge()

Dim userGuess As Integer
Dim age As Integer

age = 25

userGuess = Val(InputBox(“Guess a number between 20 and 30.”, “Guess Age”))

If (userGuess > age) Then

MsgBox (“Too high!”)

MsgBox (“The answer is “ & age)

End If

If (userGuess < age) Then

MsgBox (“Too low!”)

MsgBox (“The answer is “ & age)

End If

If (userGuess = age) Then MsgBox (“You got it!”)

End Sub

Select/Case Statement :-

Here is example demonstrates the "select case" statement.

Private Sub FindDay()

d =Weekday(Date)

Select Case d

Case 1

MsgBox "Sleepy Sunday"

Case 2

MsgBox "Monday again!"

Case 3

MsgBox "Just Tuesday!"

Case 4

MsgBox "Wednesday!"

Case 5

MsgBox "Thursday..."

Case 6

MsgBox "Finally Friday!"

Case else

MsgBox "Super Saturday!!!!"

End Select

End Sub

Looping Statement :-

Most of the time when you write code, you want to allow the same block of code to run a number of times. You can use looping statements in your code to do this.

Sub SumUp()
Dim n As Integer
Dim t As Integer

For n = 1 To 10
t = t + n
Range(“A1”).Value = t
Next n
MsgBox " The total is " & t

End Sub

Another example:-


Sub DoWhileNotEmpty()

Do While ActiveCell.Value <> Empty

ActiveCell.Value = ActiveCell.Value * 2

ActiveCell.Offset(1, 0).Select

Loop

End Sub

Scrolling about a chosen worksheet.

Here's a very simple line of code that will stop users from scrolling about a chosen worksheet. To place the code in, right click on the sheet name tab, select "View Code" and paste in this code.

Private Sub Worksheet_Activate()

Me.ScrollArea = "A1:L20"

'To set back to normal use:

Me.ScrollArea = ""

End Sub

Making sure the Combobox selection is part of the list:

Private Sub ComboBox1_Change()

If ComboBox1.ListIndex >= 0 Then

'Your code here

End If

End Sub

Stop a user from closing a UserForm via the X:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = 0 Then

Cancel = True

MsgBox "Please use the Cancel button", vbCritical

End If

End Sub

When coding with a UserForm use the keyword Me.

Private Sub UserForm_Activate()

MsgBox Me.Name

End Sub

When coding with the Worksheet events use the keyword Me.

Private Sub Worksheet_Activate()

MsgBox Me.CodeName

End Sub

When coding with the Workbook events use the keyword Me.

Private Sub Workbook_Open()

MsgBox Me.FullName

End Sub

Prevent endless loops within events:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ResetEvents
Application.EnableEvents = False
'Your code here.
Application.EnableEvents = True

Exit Sub
ResetEvents:
Application.EnableEvents = True
End Sub

Validate Date in VBA

Sub Feed_Check_Date_Function()

‘ Date is within the Valid Range
Check_Date “2011-09-01?, “2011-09-10?, “2011-09-01?

‘ Date is NOT within the Valid Range
Check_Date “2011-09-01?, “2011-09-10?, “2011-09-21?

End Sub

Function Check_Date(ByVal StartDate As Date, ByVal EndDate As Date, ByVal DateTobeChecked As Date)

If DateDiff(“d”, StartDate, DateTobeChecked) <> 0 Then
MsgBox “Enter a correct date!!!!!”
End If

End Function

Check Column Exist in Excel

'Check the column exist in Excel File. In Fnd string pass the Column heading text.

Function CheckColumnExist(Fnd As String) As Boolean
'Check the column exist in Excel File.
CheckColumnExist = False
Set r1 = Cells.Find(What:=Fnd, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not r1 Is Nothing Then
CheckColumnExist = True
End If
End Function

To get windows temp directory path

'This private method is used to get windows temp directory path

Private Function GetTmpPath()

Dim sFolder As String ' Name of the folder
Dim lRet As Long ' Return Value

sFolder = String(MAX_PATH, 0)
lRet = GetTempPath(MAX_PATH, sFolder)

If lRet <> 0 Then
GetTmpPath = Left(sFolder, InStr(sFolder, _
Chr(0)) - 1)
Else
GetTmpPath = vbNullString
End If

End Function

To Find the Last Used Row in Excel

Function FindLastRow() As String
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
FindLastRow = LastRow
End If
End Function

To Find the Last Used Column in Excel

Function FindLastColumn() As String
Dim LastColumn As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
FindLastColumn = LastColumn
End If
End Function

Inserting and retrieving text from a Bookmark.

There are several methods of inserting text at/into a bookmark. The method you use depends on whether you need to retrieve the text from the bookmark at a later time.

Lets look at the more obvious ways of inserting text at a bookmark.

ActiveDocument.Bookmarks("myBookmark").Range.Text = "Inserted Text"

If the bookmark is a placeholder bookmark, the inserted text will look like this:

I Inserted Text

If the bookmark is an enclosing bookmark, it will be deleted, and the inserted text will appear in it's place.

ActiveDocument.Bookmarks("myBookmark").Range.InsertBefore _
"Inserted Text"

ActiveDocument.Bookmarks("myBookmark").Range.InsertAfter _
"Inserted Text"

With both these methods, if the bookmark is a placeholder bookmark, the text will be inserted after the bookmark:

I Inserted Text

With enclosing bookmarks (even if the bookmark only encloses a space), the following occurs:

InsertAfter – [ Original Text ] Inserted Text
InsertBefore – [ Inserted Text Original Text ]

In order to retrieve the text in a bookmark, the bookmark needs to be
an enclosing bookmark. Then you can use the following to retrieve the text from the bookmark:

strBookmark = ActiveDocument.Bookmarks("myBookmark").Range.Text

You have already seen how to add text to an enclosing bookmark using the InsertBefore method above. But what if you want to insert text into a placeholder bookmark (making it an enclosing bookmark) so that you can retrieve the text from it at a later time ? And what if the bookmark is already an enclosing bookmark but you want to replace the text inside it ? There is no single command in VBA to achieve this. What you need to do is replace the bookmark with the inserted text (the bookmark is deleted), then re-create the bookmark around the inserted text. The following code is an example of how this is done:

Dim bmRange As Range

Set bmRange = ActiveDocument.Bookmarks("myBookmark").Range

bmRange.Text = "Inserted Text"

ActiveDocument.Bookmarks.Add _
Name:="myBookmark", _
Range:=bmRange.

Thanks!

Working with Bookmarks in VBA

Types of Bookmarks
The most important thing you need to know when working with bookmarks in Word is that there are two “types” of bookmarks – “placeholder” bookmarks and “enclosing” bookmarks.

Before we proceed, and whenever you work with bookmarks, you should turn on display of bookmarks by going to Tools | Options | View and selecting “Bookmarks”. This makes it easier to see what's actually happening.

(1) Placeholder Bookmarks
If you click somewhere in the document and insert a bookmark it will look like a beam I – this is a “placeholder” bookmark.

(2) Enclosing Bookmarks
Now, if you select some text and insert a bookmark it will look like the selected text is enclosed in square brackets ie: [selected text] – this is an “enclosing” bookmark.

What is VBA used for?

A common use of VBA is to add increased 'functionality' or some 'automation' to the various MS OFFICE programs.

For example, you could access a commonly used menu item that you commonly use, such as the 'Statistics' tab from the document 'Properties' item from the 'File' menu, which is not easily accessable, to be instantly accessed by a hot key.
Or, you could automate a series of commonly performed procedures or actions. For example, you might commonly enter data into a table then do some calculations and/or formmating of that data. VBA can be used to automatically do the calculations and/or formatting of this data.
To get some idea of what can be done using VBA, do a web search for 'VBA Addins'.

What is the 'ESSENCE' of VBA?

To understand the 'Essence' of VBA, you must understand that every part of a MS Office program is made up of 'Objects', and these Objects have 'Properties' that can be 'set' or 'altered' in response to a user initiated 'Event' such as a 'mouse click' or a 'keypress'.

In WORD for example, the 'Menu Bar' is an Object. The 'Header' and 'Footer' are objects. The whole document, individual pages, a paragraph, a sentence, a word, an individual character are also objects.

All of these Objects have 'Properties'.
Some of these properties are either 'true' of 'false'. For example, a selection of text could have its 'bold' property set to 'true'.
Some of these properties could have a 'numerical' value. For example, a selection of text could have its 'size' property set to '36'.
Some of these properties could have a 'text' value. For example, a selection of text could have its 'color' property set to 'wdRed'.

All of these Objects can react to 'Events'.
Using a mouse or a keyboard, the user initiates an 'Event'. For example, clicking on the 'Font Size' menu item in the main toolbar, will initiate the Event of the dropping down of the Font Size selection box.

VBA 'controls' this interection between 'Objects' and their 'Properties' in response to a user initiated 'Event'

What is VBA?

Visual Basic for Applications (VBA) is an implementation of Microsoft's Visual Basic, an event driven programming language and associated development environment which is built into most Microsoft Office applications (including Apple Mac OS versions), some other Microsoft applications such as Microsoft MapPoint and Microsoft Visio - a former independent application which was acquired by Microsoft; as well as being at least partially implemented in some other applications such as AutoCAD, WordPerfect and ESRI ArcGIS. It supersedes and expands on the capabilities of earlier application-specific macro programming languages such as Word's WordBasic, and can be used to control almost all aspects of the host application, including manipulating user interface features such as menus and toolbars and working with custom user forms or dialog boxes.

Sub setCookies(header As String)
Dim cookie As String
Dim Name As String
Dim Value As String

cookie = Mid(header, InStr(header, ":") + 1, Len(header) - InStr(header, ":") + 1)

'They will be delimited by ";"
If ((InStr(cookie, ";") <> 0) < Len(cookie)) Then 'we found a list of cookies Do Until (InStr(cookie, ";") = 0) If (InStr(cookie, "=") > 0) Then 'name=value
Name$ = Trim(Mid(cookie, 1, InStr(cookie, "=") - 1))
Else
Name$ = cookie
End If

Value$ = Trim(Mid(cookie, 1, (InStr(cookie, ";") - 1)))

On Error Resume Next
If Not (Value$ = "FORMCRED=") Then
colCookies.Add Value$, Name$
'Lets update cookies we already have...
If Err.Number = 457 Then
colCookies.Remove Name$
colCookies.Add Value$, Name$
Err.Clear
End If
End If
cookie = Mid(cookie, InStr(cookie, ";") + 1, Len(cookie) - InStr(cookie, ";") + 1)
Loop
End If

'Process only cookie in list or last cookie from list
If (InStr(cookie, "=") > 0) Then 'name=value
Name$ = Trim(Mid(cookie, 1, InStr(cookie, "=") - 1))
Else
Name$ = cookie
End If
Value$ = Trim(Mid(cookie, 1, Len(cookie)))

On Error Resume Next
colCookies.Add Value$, Name$
If Err.Number = 457 Then
colCookies.Remove Name$
colCookies.Add Value$, Name$
Err.Clear
End If

End Sub

Get Cookies

Function getCookies()

For Each Item In colCookies
If getCookies = "" Then
getCookies = Item
Else
getCookies = getCookies & ";" & Item
End If
Next Item

End Function

Reset Cookies in VBA

Function resetCookies()

While colCookies.count > 0
colCookies.Remove 1
Wend

End Function

Reset Cookies in VBA

Function resetCookies()

While colCookies.count > 0
colCookies.Remove 1
Wend

End Function

URL Encode in VBA

Function UrlEncode(strString) 'As String
Dim strUrlEncode
Dim lngPos

For lngPos = 1 To Len(strString)
strUrlEncode = strUrlEncode & "%" & Right("0" & Hex(Asc(Mid(strString, lngPos, 1))), 2)
Next
UrlEncode = strUrlEncode

End Function