How to make any file read-only using VBA?

VBA has in-built function SetAttr to change attribute of a file or folder. This Function is classified as File or Directory function. Below is the syntax of SetAttr and it takes two parameters.

        Syntax: SetAttr (FilePath, Attribute)

FilePath is the fully qualified path of the file and Attribute is the parameter of a file which you wish to change. In this example we are going to change attribute of the file ‘Sample.xlsx’ from normal to read-only.

Example:

Sub ReadOnly()

Dim s as String

S = C:\LM10\Sample.xlsx

SetAttr (S, vbReadOnly)

End Sub

Read More

How to add line break and tab space in outlook using VBA?

When working on email automation in Outlook, the content of the mail can be of type Body or HTMLBody. Depending on the type we select we can use html tags or VBA constants to add Line Break or Tab Space in the body of the mail.


Line Break:

Assume MailObj is the Outlook mail object. To add line break between two texts in the body of the mail we can use either VBA constant (vbCrLf) or html tag (<br/>) as shown below.

     MailObj.Body = "This is 1st text" &  vbCrLf  &  "This is 2nd Text"

or

    MailObj.HTMLBody  = "This is 1st text"  &  "<br/>"  &  "This is 2nd Text"


Tab Space:

Tab space is equivalent to 8 blank spaces. Similar to the above example tab space cab be added to each text using a VBA constant (vbTab).


MailObj.Body  =  vbTab  &  "This is 1st text" &  vbCrLf  &  vbTab  &  "This is 2nd Text"

Read More

How to convert mailing list in Excel column into a string using Collections?

Let us assume you have say 20 email Ids in an Excel sheet to which a common mail communication is to be sent. One way of combining all the emails into a string is using CONCATENATE function. However it is difficult if the no. of email IDs to be combined are more. Another way to accomplish this is by using Collections in VBA. Collections are nothing but group of related objects.

First store all Email Ids into a Collection and then combine each email Ids in Collection into a String variable and this variable can be used as Mailing List to send mails. See the below example.

Below code will create a Collection and add mail Ids from Excel sheet to it. 

Dim MailList as Collection

Set MailList = New Collection

For i  = 1 to 20

MailList.Add Cells(i,1).Value

Next i

Now we have created collection, next step is to combine all mail Ids in collection into a single string.

Dim S as String

S = ""

For Each Item in MailList

S= S  & Item  & ";"

Next 

Now we have created single string 'S' of all 20 mail Ids. This variable can be used in outlook mail  application to send mails as shown below.

MailObj.To = S or

MailObj.CC = S

Read More

How to activate other Microsoft application using VBA

If you are working on a project that involves interaction with multiple MS Office application, then VBA has a method to invoke other office applications within Excel. For example,

Below code invokes or activates Word application.

Application.ActivateMicrosoftApp (xlMicrosoftWord)

Similarly, replace xlMicrosoftWord to xlMicrosoftMail to activate Outlook. So one can create keyboard shortcut to frequently used application and activate those applications without any manual effort.

However, to invoke other application not listed or supported by above method use Shell command. For example to start notepad application, use the code: Shell "NotePad.exe"

Read More

Static variable in VBA

A static variable is a local variable which retain its value even after the execution of procedure. Static variable can be declared by placing static  keyword before the declaration.

For example,

static username as string
static counter as integer

In below example static variable "m" retain its value everyime a program is executed

Sub StVar()

Static m As Integer
m = m + 1
MsgBox (m)

End Sub

Note: To reset a static variable to initial value close the workbook or hit the reset button in VBA editor.

Read More

How to create and use Dynamic Arrays in VBA

Arrays are important elements of any programming language. An array can be one, two or multi dimensional. An Array declared without a specific size (re-sizable) is called a Dynamic Array. Dynamic Array can be declared as shown below.

Dim NewArray () as Integer

However, to use the Array in application, We must define its size. To change size of an Array we use ReDim keyword.

ReDim NewArray (4) 


Now, NewArray can store up to 5 values (i.e. 0 to 4). Let us assign 5 values to the Array.

NewArray = Array (5,10,15,20,25)

ReDim can be used multiple times in our application to change the size of an Array dynamically. Suppose we want to change the size of NewArray again, so that it can store 10 values, then use ReDim again as below.

ReDim NewArray (9)

But changing the dimension of Array using ReDim statement clears the previously stored values. If we want to retain the previously stored values, then we use Preserve keyword as shown below. By doing this old values stored in an array remain unchanged.

ReDim Preseve NewArray (9)

Read More

How to repeat the Header row in each page using VBA?

Consider an Excel document which has employee details with 5000 employees. The 1st row of the sheet has details, such as Name, Age, Designation, Date of Birth and Date of Joining of employees. 

Now, If we want to take print of these employee list, and if we want the header row to appear in each page of the print out then, select the sheet which has the data and use the below VBA code to make header row appear on each page.

Read More

How to enter formula with relative and absolute reference using VBA

Relative vs Absolute:

To enter a formula using VBA into a single cell, We use Range().Formula property and assign it to worksheet function.

For Example, VBA code to enter VLOOKUP formula in cell B1 is
                    
                         RANGE("B1").FORMULA = "=VLOOKUP(A1,SHEET2!A:B,2,0)"


Suppose, We want to apply the same formula from Range B1 through B10, then change the VBA
 code to
                         RANGE("B1:B10").FORMULA = "=VLOOKUP(A1,SHEET2!A:B,2,0)"


Now, in the new code Range A1 is fixed or absolute for cells from B1 through B10. To change it to relative reference, replace A1 with A:A on the RHS of the code. Now, the new code look like

                        RANGE("B1:B10").FORMULA = "=VLOOKUP(A:A,SHEET2!A:B,2,0)"

Replacing A1 with A:A changes the lookup value from Absolute to Relative reference (i.e. for cell B1, A:A is  A1; for B2, A:A is A2 and so on..).

NOTE:

Learn Financial Management Courses online

Use Coupon Code 580EDUNF83 to get additional 20% discount

This relative referencing style doesnot work for EOMONTH worksheet function. The syntax for EOMONTH is EOMONTH(START DATE, MONTHS).

                 Let RANGE("B1:B10").FORMULA =  "=EOMONTH(A1,0)"

In the above formula, If we replace A1 with A:A then it will return #VALUE error. An alternative to get the relative reference to such worksheet functions is replacing A1 with the below formula                                                        INDIRECT(ADDRESS(ROW(),1,4)). 

So the correct code would be

      RANGE("B1:B10").FORMULA =  "=EOMONTH(INDIRECT(ADDRESS(ROW(),1,4)),0)"

Read More

How to get only Integer portion of a division using VBA operator

Excel has a built in function QUOTIENT() to return integer portion of a division. The similar result can be obtained using ROUND() function. However, VBA has operator to return only integer portion of a division. The symbol for getting division with integer portion as result is "\".

Below is an an example of two procedures with "division" (/) and "division with integer" (\) for two variables.

The first procedure returns a value of 5.5 and the second one returns 5. However, this division with integer operator does not work when used as formula in cells.

Read More

How to make Excel UserForm Immovable using VBA

By default excel userforms are movable. This will let the user to view the Excel sheet by dragging the user form aside. In case if the the creator of the user form does not want the user to view Sheets, then UserForm position can be fixed using a VBA code as given below.


                                  Private Sub UserForm_Layout()
                                  UserForm1.Left = 0
                                  UserForm1.Top = 0
                                  End Sub

Here is a link to video demonstration of this.

Read More

The Difference between ByVal and ByRef in Excel VBA

The Difference between ByVal and ByRef in Excel VBA:

In VBA, ByVal (By Value) and ByRef (By Reference) are two different ways of passing arguments to the procedure. When a parameter is passed to the procedure as ByRef, the value of the parameter can change at runtime. However, if the parameter is passed as ByVal then its value does not change at runtime.

Learn Financial Management Courses online

Use Coupon Code 580EDUNF83 to get additional 20% discount

Let us understand this by an example. Consider two procedures namely FIRST  and SECOND. Here, the FIRST procedure is called within the SECOND procedure. Hence the FIRST procedure is a CALLED procedure and the second one is CALLING procedure.

The FIRST procedure has integer argument "x" passed onto it as ByRef. This procedure adds 10 to the value of "x" passed onto it.

                                     Sub First(ByRef x As Integer)
                                     x = x + 10

                                     End Sub


The SECOND procedure does not have any arguments. It has a variable "y", the value of which is passed to FIRST procedure as argument and display the value of "y" in MsgBox.

                                            Sub Second()
                                            Dim y As Integer
                                            y = 5
                                            Call First(y)
                                            MsgBox (y)
                                            End Sub



ByRef:

Now, when we run SECOND procedure keeping "x" as ByRef in FIRST procedure the output would be 15.

    output for ByRef

ByVal:

Now, when we run SECOND procedure keeping "x" as ByVal in FIRST procedure the output would be 5. 

      Output for ByVal

Read More

How to copy only visible cells in Excel using VBA

Consider an Excel sheet which has list of customers from two different regions, say A and B. If we have to copy only the customers from region A into sheet 2, Apply filter to region column for criteria A, select the visible data and use the below code to copy only the visible cells:

Selection.SpecialCells(xlCellTypeVisible).Select

Selection.Copy

Sheets("Sheet2").select

ActiveSheet.Paste

This will copy only the visible cells into Sheet 2.

 

Read More

Snake and Ladder Game in Excel

Snake and Ladder is a most popular classic board game. Kids enjoy playing it. I have created Excel version of this classic board game with 2 players. Below is a video demonstration of it.

Learn Financial Management Courses online

Use Coupon Code 580EDUNF83 to get additional 20% discount

Read More

How to convert an Excel sheet into PDF in Office 2007

There are many web  based application available to convert an Excel sheet into PDF document. However, In most of these 

applications only limited features are available for free and are also subjected to daily usage limit. Follow the below steps to convert anExcel sheet into PDF document.

  1) Download and Install Excel to PDF AddIn from Microsoft's official website.

      

  2) Open the Excel Sheet which you have to convert into PDF.

  3) Open Visual Basic Editor from Developers tab and paste the following code into the Module

            

                                       ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                       Filename:="C:\Users\EA\Desktop\KPSC\New.pdf", _
                                                    Quality:=xlQualityStandard, _
                                                    OpenAfterPublish:=False

  4) Make sure that file path provided is correct. 

  5) Now run the code to get the activesheet converted into PDF.

Read More