Exploring Excel Macros

Create Automatic Index sheet in Excel

2012-01-25T09:13:00.000+05:30

The purpose of this macro is to create an index sheet for your excel file. The index sheet will contain a list of all the worksheets available in the excel file.

Steps involved:

Open the excel file where you want to add index sheet
Insert a new sheet at the beginning of the file and rename it as “Index”
Press Alt+F11
A new visual basic window will open, in the left pane (project explorer), select the Index sheet and double click
In the right pane, where you can enter the macro commands, there will be 2 drop down boxes.
Select “Worksheet” in the first drop down box and select “Activate” in the second drop down box. Thus you are telling the system to run the macro whenever this worksheet is activated.

Paste the below code as it is:

Private Sub Worksheet_Activate()

Dim wSheet As Worksheet

Dim M As Long

M = 1

With Me

.Columns(2).ClearContents

.Cells(1, 2) = "INDEX"

End With

For Each wSheet In Worksheets

If wSheet.Name <> Me.Name Then

M = M + 1

Me.Hyperlinks.Add Anchor:=Me.Cells(M, 2), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name

End If

Next wSheet

End Sub

Save the file and go to the Index sheet. You will notice that the index has been listed in column B.

This macro will run everytime you come to this sheet, which effectively means that index will be updated whenever you come to the sheet and you always see the latest list of sheets in that order.
You can click on any of the sheet names to go directly to that sheet.

Line by Line analysis of the macro:

Private Sub Worksheet_Activate() - even which triggers the macro.
Dim wSheet As Worksheet – define the variable which will be used later to identify the individual sheets
Dim M As Long – this will be used as a counter and to update the rows in the list one by one.
M = 1 – initiate the value of M at 1.
With Me – start working with the current worksheet where Me refers to the current worksheet, ie. Index sheet.
.Columns(2).ClearContents – for the current worksheet, delete the data in column B. This will delete any previously existing index
.Cells(1, 2) = "INDEX" – Enter the value in cell B2 as Index – heading of the list
End With – end working with the current worksheet.
For Each wSheet In Worksheets – “For” loop is used to start working with one sheet at a time and continue till all the sheets are done.
If wSheet.Name <> Me.Name Then – make sure that the system has not come back to the starting point, (i.e. index worksheet) and to avoid making a reference to index in the index sheet itself
M = M + 1 – increase M by 1. this will allow to update the next row in the index instead of overwriting the current row
Me.Hyperlinks.Add Anchor:=Me.Cells(M, 2), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name – create a hyperlink to the respective worksheet. As we can see, we are using the cell address (M,2) to update the list, this way, the system keeps moving one row down everytime it finds a new worksheet.
End If – end the if condition
Next wSheet – At this point, one index entry has been created. Next wsheet will ask the system to go to the next worksheet and follow steps i. to m. again.
End Sub – after all the sheets have been indexed. End the macro.

This macro is easily available on the internet if you along with a number of other variations. I have only taken one and simplified it for easy understanding.

Enjoy!!!

Excel macro to identify cells containing formulae

2011-12-22T08:34:00.001+05:30

Quite often, we come across large excel sheets with huge volumes of data and a whole lot of formulae. It becomes very tricky to check the validity of each formula.

Even greater is the risk that someone might have deleted a formula and entered a figure manually.

Here is a setup in excel which can help you to identify all the cells having formulae and potentially identify any manual interventions.

The logic / reasoning follows at the end of the solution.

Open the excel file you want to use

Select all the cells which you want to cover under this control.

Select Insert à Name à Define à a dialog box will pop up

In the Names in Workbook field, enter this value: CellHasFormula

In the refers to field enter this formula: =GET.CELL(48,INDIRECT(“RC”,FALSE))

Press Ok to close the dialog box.

Select Format à Conditional Formatting à a dialog box will pop up

In the dialog box, select “Formula is” in the first field.

Type this value in the second field: =CellHasFormula

Click on the format button and select the formatting you want.

Then press ok to exit the dialog boxes.

As you can see below, the fields which contain formula have a different formatting.

If you change any of the fields manually or add a formula anywhere, you will see the formatting changes automatically

Step by Step analysis:

The 2 key steps in this solution are:

Naming the range with a formula è =GET.CELL(48,INDIRECT(“RC”,FALSE))

I’m not sure of the roots of this formula, but apparently it is one of the old excel macro commands “Get.Cell” from the time before VB macros were included in excel.

Get.Cell is used to retrieve the metadata (or properties) of a cell and use them in the macro.

“48” is probably the command used to check whether it is a formula or not. I’m still trying to find what other commands are possible with Get.Cell.

“Indirect(“RC”,…” is the normal excel formula which is used to refer to the cell itself.

“CellHasFormula” is just a name given to this range. It can be anything you want.

Adding Conditional formatting è In the second stage, we just make a reference to “CellHasFormula” and decide what formatting we want if the cell has a formula.

You will notice that we have not written any macros here, but still when you open the file next time, excel will give you a macro warning. That is because we are using “Get.Cell”. You will need to enable macros to be able to use this solution.

Excel Macro to automatically send emails to a list of people

2011-12-12T11:36:00.000+05:30

For your own safety, lets be clear on the below understanding before you start reading or possibly using the below excel solution:

You can assume I’m an absolute idiot on MSExcel.

I have absolutely no formal training of any kind on excel, macros or on programming for that matter.

I work like a scavenger searching for bits and pieces of macro codes on the net and putting them together to suit my needs.

There may be bits which I may not understand myself, but there will never be a harmful code posted here as I understand and respect the privacy and integrity of your system.

If you have improvements or suggestions on the excel solutions posted here, please feel free to post a comment or mail me at gulthemacroguy@gmail.com

You can also send in your excel related questions / doubts / requirements on the above email and we can explore the answers together.

Quite often we come across the need to send mails to a number of people automatically. Eg: send personalised greetings, send updates to a list of people, salary details to employees, marks to students and so on.

There are a number of softwares available on the net which do this, but they are typically paid versions.

There are other ways to directly using outlook, but those methods need the user to click “OK” every time a message is sent out. This is again for your own security to ensure that virus or spams are not sent without your knowledge. This means you can’t just click a button and forget it.

I could see the below macro as the most practical way to achieve the desired results.

Before using the macro, you need to do the following actions:

Create a list with names in Column A and email addresses in column B in an excel sheet. (this macro is for 3 names, but you can add as many as you want)
Create a button in excel.
Double click on the button and add the below macro code in the window which opens up.

Private Declare Function ShellExecute Lib "shell32.dll" _

Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _

ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _

ByVal nShowCmd As Long) As Long

Private Sub CommandButton1_Click()

Dim UserName As String

UserName = “Macroguy”

Dim Email As String, Subj As String

Dim Msg As String, URL As String

Dim r As Integer, x As Double

For r = 3 To 5

Email = Cells(r, 2)

Subj = "Automated Mail Alert"

Msg = ""

Msg = Msg & "Hi " & Cells(r, 1) & "," & vbCrLf & vbCrLf

Msg = Msg & "This is an automated mail alert generated using MS excel macros"

Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf

Msg = Msg & UserName

Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")

Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

Application.Wait (Now + TimeValue("0:00:04"))

Application.SendKeys "%s"

Next r

End Sub

Line by Line analysis of the Macro:

Lines 1-4 are required to define a shell command. Shell commands stand for those system commands which need not be part of excel, but are triggered using the macro.

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Line 5 This is the start of our macro. This macro needs to be attached to a command button. The same macro can be attached to any event such as file open, file close, file save etc.

Private Sub CommandButton1_Click()

Line 6-7 Just defined the username, this username will be used as a signature in the email. (there are ways to use shell commands and retrieve the user name from the windows user login, but I’ll cover that some other day)

Dim UserName As String
UserName = “Gulshan”

Line 8 Defined the email, subject, message body and mail link as strings

Dim Email As String, Subj As String
Dim Msg As String, URL As String

Line 10 Now comes the best part of this macro. You can send mails to more than one recipients using this macro and the message will go as an individual mail to each of them. We do this be maintaining a list in cells A3 – B5, column A contains the names and B contains the email addresses.

Dim r As Integer, x As Double

Line 11 3^rd to 5^th rows in excel contain the email addresses. You can replace 5 with the last row of your list of mails.

For r = 3 To 5

Line 12 (r,2) tells the system to look at 2^nd column of each row and take the email address from there.

Email = Cells(r, 2)

Line 13 Define the subject. (can also be set to pick up from a cell reference)

Subj = "Automated Mail Alert"

Line 14 – 18 Create the message body: (vbCrLf sends “Enter” command or line break to the message)

Msg = ""
Msg = Msg & "Hi " & Cells(r, 1) & "," & vbCrLf & vbCrLf
Msg = Msg & "This is an automated mail alert generated using MS excel macros"

Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf
Msg = Msg & UserName

Line 19 – 20 Replace all the spaces in the message with the system code for space. This is done because the system will create a hyperlink and hyperlinks are not supposed to contain spaces.

Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

Line 21 Replace all the line breaks in the message with the system code for line breaks. This is done because the system will create a hyperlink and hyperlinks are not supposed to contain line breaks.

Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

Line 22 Create the URL link for the message by concatenating the above data.

URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

Line 23 Execute the hyperlink. This is as good as clicking on the link. This will open a new outlook message window with the first recipient, subject and the message body.

ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

Line 24 – 25 Now the system will wait for 4 seconds and then send the send command (keyboard strokes Alt+S) to send the mail. The 4 seconds wait has been created to accommodate for the system speed. You can change it depending on how fast or slow your system is.

Application.Wait (Now + TimeValue("0:00:04"))
Application.SendKeys "%s"

Line 26 Now move on to the next record.

Next r

Line 27 End the macro after all mails have been sent.

End Sub