Latest Blog Roll

xlmacros.com is  primarily a  tutorial website and aims to provide Excel Users (having little knowledge of Excel VBA), with the  power to write efficient macros to supplement their efficiency while working on Excel. At the same time this websites aims at helping Excel user community develop necessary Excel skills by providing numerous tips, tutorials and forum platform.

 

At xlmacros.com we understand the frustration you have to go through, while trying to make something work in Excel and it just won’t work. We hereby try to make an attempt to compile various Excel tutorials, Excel VBA tutorials, Videos, a huge list of Excel Templates and Excel Addins for you to download and a Forum for you to chat and discuss about various issues you are facing in Excel.

Although, most of Excel users today are aware of something called VBA, there are not many who have taken advantage of this language to program Excel objects. Learning VBA, does not at all mean that you will become a programmer or you need to be one. VBA just helps you to save a lot of time by automating the repetitive tasks that you perform in Excel.

Please do remember, EVERYTHING...Yes, everything that you do in Excel manually can be programmed, and it does not need you to be a programmer to do so.

Excel has built in features which help you record actions and automatically generates the code for you.

All you need to do is to copy and paste these lines here and there and you get a program which you can use at the click of a button.

You can check out the step by step lessons with numerous examples and concepts in this Learn Excel VBA course here which will help you learn VBA quickly and easily.

Excel Training Video course is a series of videos developed by our team in case you want to learn Excel 2007 (but not the VBA)

Learn Excel Macros the Easy Way

This page explains Excel VBA with a new view (with Analogies). Excel VBA solves various complex Business problems and saves a lot of time by automating repeated tasks.


Basics of VBA

Concepts and Analogies:
The reason some of these concepts are very important to understand is that once you get on to write the Macros yourself you would have a better/generalized understanding of the way Macros and written. And also when Excel throws some exceptions like “Script out of Range” or “Object required” etc. they do not sound alien to you. And you can make out what Excel VBA expects you to do to make your Excel VBA Application program run.

 

Our World Vs Excel’s World

For a moment just think if Excel is the World (like our real World) Excel has a lot of people like you and me. Like they say everyone is unique in our World there would be many people in Excel (the new World) Now the People in our World are the Objects in the Excel World. Like there are many people here there are various objects in Excel. Like we all have our names these objects also have unique names all the Excel World’s people (objects) also have their names. The names of Excel World’s people are Application, Workbook, worksheet, range, PivotTable, Charts .

Now the People in our World are the Objects in the Excel World. Like there are many people here there are various objects in Excel. Like we all have our names these objects also have unique names all the Excel World’s people (objects) also have their names.
The names of Excel World’s people are Application, Workbook, worksheet, range, PivotTable, Charts .
Like sometimes one person can have two or more names some Excel Objects also can be referred by one or more names. i.e. if you want to refer to First cell in any worksheet you can either refer to it by Range(“A1”) or you can refer to it by Cells(1,1) , now both of these statements refer to an Object (Person) in the Excel World which is A1 cell in a sheet.

Best Ways to Learn Excel VBA

The best way to learn Excel VBA is to record as many macros as possible (Record Macros) and see what code Excel generates. By seeing the code Excel generates again and again you will get the feel of it and slowly you will become familiar with the way the code is written. In fact, this is one of the best part of Excel VBA you do not need to know and learn anything when you make programs in Excel. Excel itself generates the programs for you. You just need to modify and manipulate the statements program To get desired results.

Recording and Running Macros

Excel has built in Macro recorder which you can use to record your actions while working on Excel. To perform the same action again and again you can run the macros. Go to View I toolbars I Visual Basic to open the VB console. Once you have the VB Console, record the macro

You can give name to your macro, assign a shortcut key to it and also tell Excel where to store the Macro (Personal Macro Book or the Workbook you are working on).

To run the macro you have recorded simply click the play button, select your macro from a list and click run. Clicking the Run Button would run the Macro and will do all the actions you did while you were recording the macro. Just think of a Macro recorder as a Voice Tape recorder. You record some sounds once you have recorded them you can listen to the same sound anytime by playing it. Just like a Voice Tape Recorder will let you hear the same sound that you recorded a Macro as well perform the same actions which you recorded.

Where do you keep your code?

The code that you write or record can lay in either the worksheets or in the Modules (Think of Modules as simple containers of the code, this is the place where you start writing your code). Module is a place where you keep your recorded/created Macros (Procedure). An excel Macro is a procedure which is a set of statements that tell your application (Excel) what needs to be done with its objects.

When you record the Macros they also get stored in the Modules, so when you need to run a Macro it can be called. You can insert any number of Modules in a workbook and you can call the macro by simply writing the name of the Module it is in and a ‘dot’ and the name of the Macro. For example if you wish to Call a Macro named “ABC” which is in Module1,
to call this one you would need to write Module1.ABC.

Apart from the module you can also put your code in the worksheets and also in the workbook. To insert a code in a worksheet (which is primarily sheet specific i.e. the Macro should run when you press a button in the sheet) just double click on the Worksheet, or workbook to see the window wherein you may put your code. We will talk more about events in the later sections which talks about if you wish to run a code every time a sheet is activated or every time the Workbook is saved etc.

Referring to an Objects (People in Excel World)

Now when we know that using Excel VBA and Macros we manipulate the Excel Objects, we also need to know the appropriate way to refer to the Excel Objects. Tell me, how would you refer to a person in the Real World? If u simply uses the name of the person it might create confusion as there could be more than one person of the same name. To be more specific you would probably say I that “I am talking about “John” who live in “Taxes” state in “USA” country. That would be good. right? The chances of somebody who wants to meet “John” would be more than the case where you would have said “I am talking about “John”.

Now the Excel Application also has its own Hierarchy of objects. And the Hierarchy is Application >> Workbook/s >> Worksheet/s >> Range/s. For the time being just consider this as the Excel Object Hierarchy model. So Lets say if you want to refer to cell A1 in a sheet called “Sheet1”. The best way to do this would be to refer to sheet1 as Application.workbooks(“NameOfBook”).sheets(“NameOfSheet”).Range(“A1”). Once you refer to the appropriate object you can very well manipulate its property or may be do some action on it i.e. ClearContents, change Background Color etc.
So, if you wish to change the background color of a Range, you would need to say Application.Workbooks(“NameOfBook”).Sheets(“NameOfSheet”).Range(“A1”).ClearContents now, this statement when written in a Procedure deletes the contents in the referred Range.

Absolute and Relative Reference

There are two ways to refer to an object the absolute and the relative reference. I will give you and example, if you wish to refer to cell A1, you can refer to it by Range(“A1”) or you may also refer to it by saying that this cell is one cell above Cell A2 i.e. Range(“A2”).Offset(-1,0). Offset takes the row and the column argument, by -1 as row index we mean one row above and by 0 as column index we mean same column.
Relative referencing to a cell is important when you are dealing with dynamic ranges.

Try putting in a small snippet of the code below and run it in a Module. This will give you an idea of how the index number work and how Excel looks at all the cells.

Sub test ()
For I = 1 to 512
Cells(i).value=i
Next i
End sub

Object Properties and Methods

Like all of us in the real World, Excel Objects also have their unique set of properties and Methods. One person may be Black and other may be White, somebody has Brown eyes somebody has Black. In the similar way each and every Excel Object also has a unique set of properties and methods attached to him/ her /it.

To see what are the various properties attached to an object, go to the VBE window (By Pressing ALT + F11) , in the window double click on the Sheet (Any sheet) and type Range(“A1”). , now did you notice a drop down showing up with a list of some English like Words?

This list that appears has all the Properties and Methods associated with the Range Object. And to distinguish between Properties and Methods from this list - Any item that starts with a small green box is a Method and the rest are properties.

Now let’s understand the difference between a method and a property. A property is an attribute of an Object. Like John has Black Hairs in the real world could be Range (“A1”) has Red Background colour. The Black hairs and the Red Background are the properties of the objects (John in the Real World and Range Object in the Excel World). Now if somebody asks you , What is the colour of John’s hair you would say its Black or if somebody asks you what is the Background colour of Range(“A1”) you would say its Red. So, in Excel you can get the properties of various objects and also you can set/ change the properties of the objects. Although, It might be a bit difficult to change the name of real person in real World. So, using the Excel VBA code statements you can modify and get the properties of Excel Objects.

By using the Excel Methods you can perform actions on the Excel Objects. Like activating a Range, Delete the contents, copy, paste and many more are the methods of the range object.

All the objects in Excel are modified and manipulated using properties and methods. In fact what we do when we run a macro. Using a macro we manipulate Excel Objects by using their properties and methods.

More content to come to this article…

Also Check www.ExcelDepot.com for All Excel Links, Templates, Functions, Excel Books, Excel Consultants

Following are few addins and templates from http://www.exceldepot.com

Lease/Loan Organizer for Excel (Includes Loan amortization schedule)

College comparison worksheet

Ratio analysis

Competitive market benchmark analysis for financial services

Job Decision MatrixRequires genuine Microsoft Office

TuitionEstimateRequires genuine Microsoft Office

Competitive analysis using SWOT

Operating expense analysis

Project earned value analysis

Customer profitability analysis

Competitive market benchmark analysis

Lease/Loan Organizer for Excel

Job applicant data and comparison table

Product profitability analysis

Market analysis

Treasury analysis worksheet

Bidder comparison worksheet and process

Debt ratios analysis Template

Procurement analysis worksheet

Due diligence assessment model

Partner marketing funds analysis

Accounts receivable aging workbook

Loan analysis worksheet

Cost of sales tool

Risk assessment and financial impact model

Prospect ranking tool

Training needs assessment

Competitive analysis

CD switch analysis

Operating lease vs. own analysis

Channel selection matrix

Supplier analysis worksheet

Return on investment

Intellectual property analysis

Customer ranking tool

Law firm financial analysis worksheet

My Personal AccountRequires genuine Microsoft Office

Sales opportunity review worksheet

Competitive market benchmark analysis for manufacturing

ROMI hurdle-rate calculation

ARM vs. fixed mortgage comparison

Product sales sample

Portfolio analysis

Fitness chart for women

Calculation Practice

College Degree Course Credits Tracker

Loan calculator with extra payments

Loan calculator

Mortgage qualification worksheet

Excel Time Sheet

Net worth calculator

Vehicle loan payment calculator

Consumer debt manager

Remodel cost calculator

Year-end tax planning calculator

TuitionEstimate

Payroll calculator

Stock investment performance

Currency rate calculator

Electoral College calculator

Biweekly mortgage payment amortization

Large number math checker

Buy vs. rent home calculator

Closing costs calculator

Multi-family garage sale calculator

Real estate commission calculator

Annuity investment calculator

Buy vs. lease car calculator

Sales commission calculator

Kitchen remodel cost calculator

Food fat percentage calculator

Bulk purchase depreciation calculator

College costs calculator

Depreciation methods comparison

Mortgage refinance calculator

Net present value calculator

Value analysis calculator for product or service

Tip calculator

Life insurance needs calculator

Home office tax benefits comparison

Total cost of ownership calculator

Sales costing calculator

Balloon loan calculator

Activity-based asset depreciation

Step costs graph

Price elasticity of demand calculator

Capital gains and losses calculator

Gain on home sale calculator

Automated Resume and Application Processing (ARAP) savings calculator

Personal net worth calculator

Intangible asset depreciation

Weighted Factors Decision Matrix (Decision Assistant Model)

Lifetime savings calculator

A cutting diet

2007 Tax Calculator

21st century pie char

Fitness chart for men

Fitness progress chart for women (metric)

Simple flowchart

Baby growth chart

Excel Gantt Chart

Production errors scatter chart

Family tree

Control chart

Revenue trend charting

21st century donut chart

Cost analysis with Pareto chart

Run chart

Manufacturing output histogram

12 month profit and loss projection

Small business cash flow projection

Detailed sales forecast

Four-year profit projection

Proyeccion de perdidas y ganancias de 12 meses

Pipeline management solution sales worksheet

Hoja de balance proyectado

tactical sales worksheet

12 month sales forecast

Streamlined sales forecast

product sales worksheet

Pipeline management strategic sales worksheet

Proyeccion de ventas de 12 meses

Sales account adjustment forecast

streamlined worksheet

Detailed sales pipeline management

Five year projection worksheet

New product sales and profit forecasting model

Channel sales forecast

Channel partner pipeline forecast

Waterfall forecasting model

Balance sheet

Two-year balance sheet with instructions

Balance sheet with financial ratios

INSTANT PROJECTED BALANCE SHEETS FOR EXCEL

Two-year balance sheet

Instant Balance Sheet

Balance sheet with ratios and working capital

Checkbook register

General ledger (multiple tabs)

Sample payroll register

General ledger

Bond amortization

Investment ledger

Checkbook register

2006 World Cup tracker

Grade tracker

Blood pressure tracker

Medical bill tracker

Wedding guest tracker (tulip design)

Employee absence tracker

Wedding gift tracker

Charitable gifts and donations tracker

Subscription and membership tracker

Weight loss tracker

Employee attendance tracker

Detailed leads tracking

Warranty tracker

Product or service cost tracker

Law firm project tracker

Project time performance tracking

Education credits tracker

Business debt tracker

Commission tracker

Event management project tracker

Quarterly leads tracking

Marketing campaign tracker

IRS audit information tracker

Activity-based cost tracker

OP MANUAL TIMELINE

Product pricing calculator

Annuity payout calculator

Blood sugar chart

Breakeven analysis

Daylight Calculator

Internal Rate of Return (IRR) calculator

Problem analysis with Pareto chart

Sales compensation model

Access Links

Excel Viewer 2003

Excess Formatting Cleaner

Remove Hidden Data

Save as PDF

Save as PDF or XPS

Save As XPS

Template Wizard with Tracking

Loan Assistant

Multi-Cell Goal Seeker

Risk Analyzer

Report Runner

Sensitivity Analyzer

Chart Tools

Chart Labeler

Histogram Creator

Quick Chart Creator

Waterfall Chart Creator

XY Chart Labeller

Address Assistant

Consolidation Assistant

CSV File Creator

Data Assistant

Data Cleaner

Data Extraction Assistant

Data Loader

Duplicate Finder

Lookup Assistant

Merge Assistant

Name Splitter

Conditional Format Assistant

Directory Lister

Information Lister

Link Finder

Macro Button Assistant

Macro Remover

Password Assistant

Print Assistant

Random Number Generator

Random Sampler

Significant Digit Assistant

ASAP Utilities

Duplication Manager

Formula Reference Sheet Index Creator

Text Manager

Number Manager

Named Range Manager

Time, Pay And Wage Book

Excel Time Sheet Advanced

ComboBox Wizard

Advanced Excel Find

Advanced Excel Select

Forecaster Excel

Calendar

Chart Add-ins and Software

Chart Picture Digitizer

Charting Tools

Classifier

Code VBA

COMxL RS232C

COM Excel

Color Palette

Compare Excel Workbooks

Compare Suite

Construction Works Complete Package

Construction Estimator

Converters

Convert Excel to PDF COM

Convert Excel to PDF

Convert Excel to HTML

Convert Excel to csv

Convert

Convert MySSQL to Excel

Convert Excel to MySQL

Excel Chart Color Tool

Excel Data Cleaning

Excel Exchange Rates

Excel Everywhere HTML

Excel Explorer

Excel Import Contacts

Excel Print Data

Excel OCX

Excel Resource Manager

Excel Sho

Excel Sma

Excel Shortcuts

Excel Templates

Excel Trading

Excel Workbooks Compare

Excel-to-MySQL

Excel Stats

Find A

Financial Advisor for Excel

Forecasti

Formu

General Cost Estimator For Microsoft Excel

Home Cost Estimator For Microsoft Excel

JEM Plus (Excel Add-in)

Monte Carlo Add-In for Excel

MySQL-to-Excel

Neural Networks Signal For Microsoft Excel

NeuroXL Classifier For Excel

NeuroXL Predictor For Excel

O2OLAP for Excel

Online Functions Exchange Rates

Paint Cost Estimator For Microsoft Excel

Privacy Add-in

Rapid Data

Range Name Manager

Real Time Highlighter for Microsoft Excel

Real Time Quotes Excel

Real Estate helper

Remodel Cost Estimator For Microsoft Excel

Repair Cost Estimator For Microsoft Excel

Report Maker

Roof Cost Estimator For Microsoft Excel

Shift Scheduler Continuous Excel

Similar Data Finder For Excel

Short Cuts Microsoft Excel Add-In

Smart VB6 Code

Smart VBA Code

SparkMaker

Spreadsheet Auditor

Spreadshe

Statistical Quality Control

Spreadsheet QC

Spreadsheet Scheduler

SQC for Excel

Tools for Excel Tables

Templates

Transform Tables In Excel

Universal Pricing

VBA Code

VBA Code Print

Workcell Planner

Worksheet Booster

Wage Calculator

Comment Control

Complete Excel Names

DiffEngineX

Easy Filter

ExcelCalcs

Excel Explosion

Excel Utilities

FaceID Values

Find Link

Flex Find

Google Search

Password Remover

QDE

Rays Tools

SendMail

XL Random Generator

Password Prompt Fixer

Backup Assistant

Cell color assistant

Colored cells assistant

Column navigator

File assistant

Filt asst

List assistant

Multi column sorter

Pivot Table Assistant

PivotPlay

PivotPower

Pivot Table Format Helper

Code Documenter

VBA Code Cleaner

Excel Time Sheet Advanced