Learn How to Write Macro To Automate For Free in Step by Step Fast!!

Want to automate and file mouse clicks and keystrokes? Want a system that automatically does your manual job on Home windows OS? This post will guide you on how to write macro to automate.

There are quite a few simple resources for recording macros for Home windows platform offered in the marketplace, on the lines of the Excel macro recorder.

Again in the days of Windows 3.1, the OS had a utility named the Recorder that recorded mouse clicks and keystrokes for repetitive duties that made everyday living a ton much easier. But in the era of Windows 95 and earlier mentioned, this useful and very simple software was finished absent with now the only put you come across a macro recorder is in Microsoft’s Excel spreadsheet and Word. The disadvantage, it only documents repetitive duties in excel and term independently and has limited scope and no scalability.

The handiness and usefulness of this resource were sorely missed and has designed a way for a lot of impartial software developers to build a Windows macro recorder.

A person can do lots of matters with very simple home windows macro resource like changing the monitor resolution, modify the themes on the desktop and switching in between distinctive folder alternatives whilst viewing Home windows Explorer to name a couple of.

The next generation of Home windows Macro recorders is not only an instrument to automate uncomplicated and schedule tasks, but also sophisticated tasks. The Windows macro recorders can automate anything that commonly needs multiple ways and cannot be carried out by an uncomplicated just one-line command.

These courses can automate IT tasks such as copying info into a database, importing and exporting information from a spreadsheet, automating report technology to tests applications and automating small business procedures. Some recorders even have advanced net recorders to automate internet-primarily based duties these types of as website information extraction and monitor scrapping.

The user-friendliness and simplicity of use of all the macro recorders available in the industry differ and so do the capabilities. Some windows macro recorders accessible in the industry can do quite a few sophisticated responsibilities while others just do the planning process administrative jobs. Which macro recorder you obtain depends on the use and necessities.

See also  7 Best Strategies and Benefits For Fixing Bugs

Basic Components of macro writting

VBA: Visual Basic for Application is portable language that can manipulate Microsoft office Application like-Word, Access, Power Point, and outlook.

If you do a job that does not need much human decisive brain but repetitive in nature, A VBA macro automation can help you to reduce time and human effort. Increase your productivity too. When I said a VBA Macro …definitely the question come as what is a macro??

Macro:A macro is a piece of code which has a capability to automate MS office suite. Initially a programming language which was having capabilities to execute a sequence of command in MS office Suite is called Macro language.

How it is developed?

Please do not confuse with VB(Visual Basic ) with VBA(Visual Basic Application ) to start with. Way back in 1960 when BASIC((Beginner’s All-purpose Symbolic Instruction Code) was born. It became hugely popular among the software industry.

After a great success, people wanted a user friendly  version of it. The first customized version of the BASIC was quick BASIC released by Microsoft in 1985. After understanding the need of the customer and support Microsoft office package in 1995 Microsoft released the first version of VBA.

What kind of task can be automated through VBA??

  • Recurring Tasks
  • Repetitive Tasks
  • Automatic update or refresh
  • If you need your own function that will support your activities
  • Greater look and feel
  •  Control lot more applications through one point

Common Problems of VBA:

  • Most of the times Macros are very week
  • Macro created on one version may not work in other version of MS packages
  • Sometime it is windows dependent. I mean Macro created in operating system may not work in other operating
  • It can be concern as security threat.

How to access Macro?

  • Open any MS office application(word,Excel etc)
  • Go to view tab
  • Click on Macro drop down
  • Click on view macro to see if any macro is already there
  • Click on record macro to create a new one
Macro editor- Write macro to automate
Macro editor- Write macro to automate

You don’t need to have anything else other than a MS office package. It is inbuilt inside of a MS office.

See also  How To Select The Best Test Case Management Software in 2021

Introduction to VBE-Visual Basic Editor

Visual Basic Editor:

Now it’s time to see a less visible environment commonly working in the background called VBE(Visual Basic Editor). This is attached to a workbook even if no code is there. Both the platform work together. It is something like -Workbook is for the new or non-programming user and VBE is for the advanced user.

How to open VBE:

A fast and easy way to get into the Visual Basic Editor is to press Alt+F11 on your keyboard. Alternatively, you can go to view–>Macro->Edit Macro to get the editor

Inside of a VBE:
The editor will look like-

VBE- write macro to automate
VBE- write macro to automate

If you go to View then you can hide or unhide all the property panes.

Few things to remember-

  • Double-clicking on the project explorer and module will show you the code.
  • Click on insert tab and click on ass module will create a new module and code pane
  • By default, the modules will be named as Module1, Module2…ModuleN but you can change the name by selecting one module and the option will be available on Property Window
  • For deletion of a module ..just right click on a module–>remove module Name

How to Write macro to automate your jobs?

Deceleration of variable in VBA

What is Variable?

A Variable is a name given by users to assign a piece of data that is stored inside computer’s memory.Variables hold values of different data types that are specified when the variable is declared.

Deceleration of variable includes four keywords in a particular order:

  • The Dim statement (VBA’s abbreviation for “Dimension”)
  •  The name of your variable, which you create, such as vals_name
  •  The word As.
  •  The type of data being stored.[datatypes]


Dim vals_name As String
Dim vals_name1 as Integer, vals_name2 as Integer, vals_name3 as Integer
Dim vals_name1, vals_name2, vals_name3 as Integer

The assignment is very simple–

vals_name= Application.ActiveWorkbook.Worksheets(“Sheet1”).Range(“A1”).Value

or else

vals_name= Worksheets(“Sheet1”).Range(“A1”).Value  [it automatically assume current application and current Workbook]

Get Column Count in Excel

Function getColoumnNumber()
Set ExcelFile = CreateObject("Excel.Application")
'You can set it to false as well
ExcelFile.Visible = true
'Open the Excel file
ExcelFile.Workbooks.Open "C:Demo.xls"
'Get the 1st sheet (can be change to some other index number)
Set ExcelSheet = ExcelFile.Sheets.Item(1)
'Get the number of columns
Cols = ExcelSheet.UsedRange.Columns.Count
msgbox "No of Columns: " & Cols
Set ExcelSheet = nothing
Set ExcelFile = nothing
End Function

one more good piece of code is as below. Here is a simple piece of code that will give you the column count. Even you can get the column name as well.

Public Function GetColCount (sSheetName)
On Error Resume Next
i = i + 1
sColName = DataTable.GetSheet(sSheetName).GetParameter(i).Name
Loop While 0 = Err.Number
'GetParameter throws an error when using an index that is out of bounds ...
'We can use this functionality to ASSUME that it's an unused column.
'We've come to the end of our USED columns
GetColCount = i - 1
On Error GoTo 0
End Function

Code to get Colomn count and Name of a Excell File

Public Function GetColCount (sSheetName)
On Error Resume Next
i = i + 1
sColName = DataTable.GetSheet(sSheetName).GetParameter(i).Name
Loop While 0 = Err.Number
'GetParameter throws an error when using an index that is out of bounds ...
'We can use this functionality to ASSUME that it's an unused column.
'We've come to the end of our USED columns
GetColCount = i - 1
On Error GoTo 0
End Function

How to Color a Tab in Excel-20** in VBA

Color a tab
Color a tab

Objective- To colour a tab of an Excel. It is often seen that most of us like to colour the tab of the excel .to distinguish between different sheets. The colour code may say a different meaning.

See also  What is Pareto Analysis In Information Technology Specially in Testing?

It may be for a different purpose.
I have found out a solution and the way to do that.
Excel way–

  • Select the tab
  • Click on format
  • Go to Tab Color
  • Expand it and select the colour.

color a tab in Excel
color a tab in Excel

Coding Way

Well..this code is a simple VBA code sample to do this tab colour dynamically through coding.
Let’s see how we can do that..

Sub Macro1()
MsgBox ThisWorkbook.Sheets.Count
For i = 1 To ThisWorkbook.Sheets.Count
Worksheets(i).Tab.ColorIndex = 10 *i
End Sub

Here I have taken the sheet count first and to give colour I have picked randomly as 10*i
The important code to note here is…

Worksheets(i).Tab.ColorIndex="Some value"

Alternatively, you can also get the ColorIndex programmatically


Want to do more complex programming??? like-get those sheets name whose colour is red
Yes by this code you can do that as well…

Sub Macro1()
For i = 1 To ThisWorkbook.Sheets.Count
If Worksheets(i).Tab.ColorIndex = 10 Then
MsgBox Worksheets(i).name
End If
End Sub

Share and Enjoy !

Leave a Reply

Your email address will not be published. Required fields are marked *