Introduction to Excel VBA

In this topic we will understand the basics in Excel VBA. What can we do with Excel VBA? How it helps us with MS Excel.

.

Introduction to Excel VBA

EXCEL VBA is the acronym for Visual Basicfor Applications. It is an integration of the Microsoft’s event-driven programming language Visual Basic with Microsoft Office Applications such as Microsoft Excel. Excel VBA is a programming language for MS Excel Application. It allows you to automate the task which you are doing manually and repeatedly. Most of the times, we do the same kind of tasks daily in our daily work, you can automate it using VBA.

We can use Excel VBA for:

§  automating the repeated tasks

§  automating hourly, daily,weekly,monthly or yearly generated reports

§  preparing rich and interactive Excel Dashboards

§  connecting to various database to get the data and prepare the reports

§  building  the automated tools

§  interacting with other applications, including MS Office Tools using API

§  doing many more things using VBA based on your data and requirement

FOR EXAMPLE

» We fetch some data from different sources like database by using sql language and paste it in a worksheet and doing some manipulations and then we will format it and generate Automated Report

» We may extract some data from other file system like csv etc… to an Excel sheet, and then we will clean the data and create some reports and send through email..

» Many of us do the similar kind of work daily; there may be some change in the data but not the structure of the data, using vba to process steps which execute to deliver the output.

VBA HELPS…

Read More...

Indentation Paragraph, Set Line Spacing, Set Tabs

When you type information into Microsoft Word, each time you press the Enter key Word creates a new paragraph. As you know margin settings determine the blank space that appears on each side of a paragraph. For example, you can indent the first line of a paragraph, you can set the amount of space that separates paragraphs, and you can align a paragraph left, right, center, or flush with both margins. Styles are a set of formats you can quickly apply to a paragraph. For example, by applying a style, you can set the font, set the font size, and align a paragraph all at once. In this lesson, you will learn about the various formats you can apply to a paragraph and about styles.

Left Indentation

Left indentation means to move the left edge of the paragraph inward towards the center of the paragraph.

Read More...

Copying Moving and Pasting Data in Excel

When we are working with the data most of the time we may required to copy/move the data from one location (range) to another location. This tutorials will explains you Copying Moving and Pasting Data in Excel.

Moving a Range in Excel

We can Move the data from one range to another range by using either short cuts or in-built commands.
If you want to Move the data from one range another location:

First we need to Select the existing Range => Cut it => Choose a New Range => Paste it

Select Range to Move:

Cut it: Press Ctrl+ X or right click and select Cut command.

Choose a New Loaction:
 

Read More...

Formatting Data and Formatting Cells in Excel

Microsoft Excel provides us the tools to make rich formats, we can format the numbers, fonts, font color, background color, alignments and many more things we can do in Excel. This tutorial section will help you to learn Formatting Data and Formatting Cells in Excel.

We have many tools in Excel to work with Formatting

Home Tab

If you observe the Home Tab, you can see many commands to work with formats.

Font

We can use these commands to format the Fonts Styles like Bold, Underline, Italic, Font size, Background Color, Font Color etc. You can click on the icon at bottom-right corner to see the more Font Settings.

Read More...

Entering Data and Moving in Excel Worksheet

Entering Data and Moving in Excel Worksheet is very easy and you find that it is the time saver for data entry. You can enter any numbers, text, dates in Excel and you can format as per your requirement.

Entering Data

In order in enter data into your worksheet, you need to activate the cell by locate your mouse point to any cell When your cell is activated, it become Active Cell. Active Cell is the place where we enter the data. When we open a new Excel Workbook, by default it activates A1 (top-left corner of a Worksheet) position of a Worksheet and we can start entering the data.

We can see the cell A1 is activate and you can see the Address of the active Cell in the Address bar (rounded with red circle), you can also observe the sample text in A1.

We can also activate a Cell and enter the data in Formula bar.

Read More...

Getting Started with Microsoft Excel

In this section I will introduce the basic features of Microsoft Excel, one of the tools in the MS Office Package suite.

What is Excel?

Microsoft Excel is a very popular spreadsheet application. It has effectively become the standard and these days the word 'Excel' is synonymous with 'spreadsheet'. Excel is a spreadsheet application used to enter the data (text, numbers, etc…) to perform calculations, analysis and store it in a excel file. There are many built-in functions and formulas allow you to perform calculations and you can create attractive, readable presentations and rich visualized dashboards including charts, table,etc…

Why Should I Learn MS Excel?

MS Excel is used with many purpose because Excel:

§  has ability to Store Data and is easy to use and flexible to do the  any changes

§  can makes calculations of complex formulas

§  can works great as a basic reporting tool, you can Dashboards and Rich Visualized Templates

§  can interact with Other Applications and Databases

§  is widely using as Data Mining Tool to Process the data, Reporting and Presentations

§  has programming Capability, you can code and build the tools to automate your tasks known as “Macro”.

Starting Excel

You can start Excel by
1. Double Click on the Excel Icon on your Desktop
Or
2. Go To Run and type excel to Start Excel Application
Or
3. Click on Start Button => Select All Programs => Select Microsoft Office => Click On Excel to Start Excel Application

Exploring the Excel Interface

When you Start Excel Application, it will Open an Excel Workbook, It is the main file having collection of Worksheets. Each Worksheet look like table with Rows and Columns, and the smaller entity in the Worksheet is called a Cell where we can enter the data.

At the top of the Worksheet you can find Menu Bar also called Ribbon, collection of built-in functions and utilities are available to execute our tasks.

Entering and Editing Data

We can enter text, numbers,dates in a Cell and we can modify and update the existing data.

Formulas and Functions

There are number of formulas and functions available in Excel to deal with your data to get the required results.

Excel Built-in Tools

There are number of tools available in Excel, which help you to play with your data to achieve required results.

Read More...

Introduction to Microsoft Excel and Creating First Excel File

Microsoft Excel is one of the tools in the Microsoft Office Package suite; it is used to create spreadsheets. It has many in-built functions and tools to work with data and create different type of reports and dashboards. It also provided the feature to the users to work behind the existing tools and can enhance its capabilities. To implement functionality beyond a regular spreadsheet, Microsoft Visual Basic programming environment is provided with Microsoft Office Excel. This programming language is called as Visual Basic for Application (VBA).

In This Section:

§  What is Excel?

§  Why is it used?

§  Creating your first excel workbook.

What is Excel?

As mentioned above, Microsoft Excel is one of the tools in the Microsoft Office Package. It is a spreadsheet application, it have powerful tools to deal with the data and create different kinds of reports, dashboards, and tools.

Why is it used?

Almost every organization uses Excel for their day to day activities from requirement gathering to deliver and present the output using excel.

§  Preparing the different kinds of templates, Quality Checking Template, Process Flow Templates, Project ManagementTemplates, Cost Estimation Templates, etc…

Read More...

Text Decoration, Change Text Color, Text Alignment, Change Text Case

This section will show you how to decorate your text in Microsoft Word Document. We will explore on making bold, italic, underline text.

Making text bold

A bold text appears with heavy weight and dark ink and we use bold text to give more emphasis on the sentence.

How to make Bold

Step 1 Select the text that you want to change to a bold font.

Step 2 Click Font Bold [ B ] button in the Home tab Font group, or simply use Ctrl + B keys to make selected text bold.

Making text Italic

 An italic text appears with a small inclination and we use italicized text to differentiate to differentiate it from other text

Read More...

Understanding and Undo Changes with Microsoft Word Document

Microsoft word provides two important features called Undo, which is used to undo the previous action and second one is Repeat or Redo, which is used to repeat the previous action.

Undo

You can quickly reverse most commands you execute by using Undo. If you then change your mind again, and want to reapply a command, you can use Redo.

For example, If you mistakenly delete text, you can use the Undo feature to recover it, similar way if delete a character and you want to delete more characters then you can use Redo operation.

How to use Undo & Redo operations:

You can access the Undo and Repeat buttons from the Quick Access toolbar. You should make a note that Repeat button also called Redo button and both operations have same meaning.

1. Type some text in a blank document as I have typed above. Now click Repeat (Redo) button and you will see that word would repeat the same operation for you.

Read More...

Move, Copy, Cut & Paste, Find and Replace Text In Word Document

In this section, we will explore Microsoft Word Features on moving, copying, cutting and paste text in the word document file.

Moving Text

Sometime it is required to move a text from one location to another location in your document. You can move text from one location in a document to another by using drag-and-drop technique with the help of mouse. This tutorial will teach you how to use drag and drop technique to move a text.

How to Move Text in Document:

1. Select any part of the text using any of the text selection methods.

2. Now take your mouse pointer over the selected text and hold mouse left button and keep holding it while moving around the document.

Read More...

Entering, Select, Delete Text in Word Document

In this section, we will explore Microsoft Word Features on entering, select, and delete text in the word document file.

In Microsoft Word, you can easily entering text or add text by putting your cursor on your document area and type the text that your want to add with your keyboard.

Entering Text

Document area is the area where you type your text, select your text and delete text. So just keep your mouse cursor at the text insertion point and start typing whatever text you would like to type.

For Example: let type the word "Welcome to Microsoft Word"

-- To Start a new line with the typing Press Enter Key

-- When you want to add space: Press spacebar key

Select Text

Selecting a text is one of the most important feature required while editing a word document. You can perform various operations on a selected text like you can delete it, copy it, move it, apply formatting to it, change its capitalization etc.

How to select text

1. Selecting text between two points

Click at the start of the block of text, hold down Shift, and click at the end of the block.

2. Selecting a single word

Simply double click anywhere on the word you want to select.

3. Selecting a paragraph

Simply triple click anywhere on the paragraph you want to select.

4. Selecting a sentence

Hold down Ctrl key and click anywhere in the sentence you want to select.

5 Selecting a column of text

Hold down Alt, click and hold the mouse button, and drag over the column you want to select.

For instance: When you select text

Delete Text

It is very common to delete text and retype the content in your word document. You might type something you did not want to type or there is something extra which is not required in the document. Word offers you various ways of deleting the text of your document.

Using Backspace & Delete Keys:

The most basic deletion technique is to delete characters one at a time by pressing either backspace or delete keys.

How to delete the text

1. Backspace Key

Keep the insertion point just after the character you want to delete and press backspace key. Word deletes the character immediately to the left of the insertion point.

2. Ctrl + Backspace

Keep the insertion point just after the word you want to delete and press Ctrl + Backspace key. Word deletes the whole word immediately to the left of the insertion point.

3. Delete Key

Keep the insertion point just before the character you want to delete and press delete key. Word deletes the character immediately to the right of the insertion point.

4. Ctrl + Delete

Keep the insertion point just before the word you want to delete and press Ctrl + Delete key. Word deletes the word immediately to the right of the insertion point.

Using Selection Method:

We have you how to select various parts of a word document.The following will show youon how to using select feature to delete text or characters within Word Document.

1. Deleting text between two points

Click at the start of the block of text, hold down Shift, and click at the end of the block to select the text and finally press either backspace or delete key.

2. Deleting a single word

Simply double click anywhere on the word you want to delete and finally press either backspace or delete key.

3. Deleting a paragraph

Simply triple click anywhere on the paragraph you want to delete and finally press either backspace or delete key.

4. Deleting a sentence

Hold down Ctrl key and click anywhere in the sentence you want to delete and finally press either backspace or delete key.

5. Deleting a column of text

Hold down Alt, click and hold the mouse button, and drag over the column you want to delete and finally press either backspace or delete key.

6. Deleting a line

Bring your mouse in selection bar area and click in front of the line you want to delete and finally press either backspace or delete key.

7. Deleting entire document content

Press Ctrl + A keys to delete the entire document and finally press either backspace or delete key.

That is all for this section. In the tutorial section we will explore on Move, Copy & Paste, Find and Replace Text In Word Document

Read More...

Microsoft Word Document Basic Features

In this tutorial, I will provide you the brief description about Basic feature of Microsoft Word Document. Microsoft Word has many feature s and in this section, it will covers typing, using the Backspace key, using the Delete key, inserting text, bolding, underlining, and italicizing.

Type, Backspace, and Delete

In Microsoft Word, you create documents by typing with keyboard. For example, if you want to create a document, you open Microsoft Word and then begin typing. You do not have to do anything when your text reaches the end of a line and you want to move to a new line—Microsoft Word automatically moves your text to a new line.  If you want to start a new paragraph, press Enter. Microsoft word creates a blank line to indicate the start of a new paragraph.  If you make a mistake, you can delete what you typed and then type your correction.

You can use the Backspace key to delete. Each time you press the Backspace key, Microsoft Word deletes the character that precedes the insertion point. The insertion point is the point at which your mouse pointer or the cursor is located. You can also delete text by using the Delete key.

Insert

While creating your document, you may find you need to insert text—place new text between existing texts. For instance, you type the sentence, "What is Word?"  After typing it, you decide you want to change the sentence to "What is Microsoft Word." With Microsoft Word, inserting a word, phrase, or even several paragraphs is easy.

Bold, Italic, and Underlines

When creating a document, you may need to emphasize particular words or phrases by bolding, underlining, or italicizing. Also, certain grammatical constructs require that you bold, underline, or italicize.  You can bold, underline, and italicize when using Word. You also can combine these features—in other words; you can bold, underline, and italicize a single piece of text.

Copy, cut and Paste

While working on a document, you may need to copy or move the texts from one location to another. Microsoft Word provides you the Copy, Cut and Paste command to work with this.

Find and Replace

While working on editing a document you come across a situation very frequently when you want to search a particular word in your document and many times you will be willing to replace this word with another word at all the few or all the places throughout of the document.  You can use Find and Replace command.

Read More...

Introduction to Microsoft Word and Creating the first Word Document File

Microsoft Word is one of the tools in the Microsoft Office Suite; it is used to create document, report, eBook, etc. It has many features to work with text. It also provided the feature to the users to work behind the existing features and tools and can enhance its capabilities. To implement functionality beyond a regular Word, Microsoft Visual Basic programming environment is provided with Microsoft Office Word. This programming language is called as Visual Basic for Application (VBA).

In this section we will explore the MS Word

Getting Started

1. Double Click on the Excel Icon on your Desktop

Or

2. Go To Run and type winword to Start Word Application

Or

3. Click on Start Button => Select All Programs => Select Microsoft Office => Click On Word to Start Word Application

You will see the following screen

Explore Word 

Read More...

Getting Start with Microsoft word 2007

In this tutorial we will introduce the basic feature of Microsoft Office Word, one of the most popular of Suite in Microsoft Office Package.

What is Ms. Word?

It is a word processor application which is used for entering text to create the document and store this text in a file called Word Document with extension (.doc and .docx)

Microsoft Office Word 2010 allows you to create and edit personal and business documents, such as letters, reports, invoices, emails and books

Why should MS Word?

MS Word is used for many ways:

MS Word allows creating personal document and business document.

MS Word is widely used by many organizations

MS Word Can be converted to other file format like HTML, XML

MS Word has the programming capability; you can code and build the tools to automate your tasks.

Starting MS Word         

You can start MS Word by:

1. Double Click on the Microsoft Word Icon on your Desktop

Or

2. Go To Run and type winword to Start Word Application

Or

3. Click on Start Button => Select All Programs => Select Microsoft Office => Click On Word to Start Word Application

Exploring the Word Interface

When you start Word Application, it will open a word document. It has many word window objects like Ribbons

Edit Text, Entering Text, copy & paste, cut & paste, etc

Read More...

How to switch from main view to Endnote view

As I mentioned in the previous article, to make vba code work with endnote or footnote we need to switch the view to endnote or footnote view. In this article I show the code for switching view of word document.

How to switch the view to endnote view

First I use the function EndnotesExist to check whether there is endnote exists in the word document and then use ActiveWindow.ActivePane.View.Type to switch the view

≫ Detect Endnote

FunctionEndnotesExist() As Boolean

    DimStoryRange As Range

    ForEach StoryRange In ActiveDocument.StoryRanges

        IfStoryRange.StoryType = wdEndnotesStory Then

            EndnotesExist = True

            ExitFor

        End If

    Next StoryRange

EndFunction

Read More...