Advance Excel With Macro & VBA Programming

[Pages:3]4/9/2016

smartbitcomputers.in/printexcel2.php

Advance Excel With Macro & VBA Programming

Technique for Creating Macro's & VBA Lavel II

Chapter 1: What Is VBA? Okay, So What Is VBA? What Can You Do with VBA Inserting a bunch of text Automating a task you perform frequently Automating repetitive operations Creating a custom command Creating a custom button Developing new worksheet functions Creating complete, macrodriven applications Creating custom addins for Excel Advantages and Disadvantages of VBA VBA advantages VBA disadvantages VBA in a Nutshell An Excursion into Versions TEAM LinG Chapter 2: Jumping Right In First Things First What You'll Be Doing Taking the First Steps Recording the Macro Testing the Macro Examining the Macro Modifying the Macro Saving Workbooks that Contain Macros Understanding Macro Security More about the NameAndTime Macro Chapter 3: Working In the Visual Basic Editor What Is the Visual Basic Editor? Activating the VBE



Chapter 9: Using VBA and Worksheet Functions What Is a Function Using Builtin VBA Functions VBA function examples VBA functions that do more than return a value Discovering VBA functions Using Worksheet Functions in VBA Worksheet function examples Entering worksheet functions More about Using Worksheet Functions Using Custom Functions Chapter 10: Controlling Program Flow and Making Decisions Going with the Flow, Dude The GoTo Statement Decisions, decisions The IfThen structure The Select Case structure Knocking Your Code for a Loop ForNext loops DoWhile loop DoUntil loop Looping through a Collection Chapter 11: Automatic Procedures and Events Preparing for the Big Event Are events useful? Programming eventhandler procedures Where Does the VBA Code Go? Writing an EventHandler Procedure

Chapter 16: UserForm Basics Knowing When to Use a UserForm Creating UserForms: An Overview Working with UserForms Inserting a new UserForm Adding controls to a UserForm Changing properties for a UserForm control Viewing the UserForm Code window Displaying a UserForm Using information from a UserForm A UserForm Example Creating the UserForm Adding the CommandButtons Adding the OptionButtons Adding eventhandler procedures Creating a macro to display the dialog box Making the macro available Testing the macro

Chapter 17: Using UserForm Controls Getting Started with Dialog Box Controls Adding controls Introducing control properties Dialog Box Controls: The Details CheckBox control ComboBox control CommandButton control

Chapter 18: UserForm Techniques and Tricks

1/3

4/9/2016

smartbitcomputers.in/printexcel2.php

Understanding VBE components

Introductory Examples

Using Dialog Boxes

Working with the Project Explorer

The Open event for a workbook

A UserForm Example

Adding a new VBA module

The BeforeClose event for a workbook Creating the dialog box

Removing a VBA module

The BeforeSave event for a workbook Writing code to display the

Exporting and importing objects

Examples of Activation Events

dialog box

Working with a Code Window

Activate and deactivate events in a Making the macro available

Minimizing and maximizing windows

sheet

Trying out your dialog box

Creating a module

Activate and deactivate events in a Adding eventhandler

Getting VBA code into a module

workbook

procedures

Entering code directly

Workbook activation events

Validating the data

Using the macro recorder

Other WorksheetRelated Events

Now the dialog box works

Copying VBA code

The BeforeDoubleClick event

More UserForm Examples

Customizing the VBA Environment

The BeforeRightClick event

A ListBox example

Using the Editor tab

The Change event

Selecting a range

Using the Editor Format tab

Events Not Associated with Objects Using multiple sets of

Using the General tab

The OnTime event

OptionButtons

Using the Docking tab

Keypress events

Using a SpinButton and a

Chapter 4: Introducing the Excel Object Chapter 12: ErrorHandling

TextBox

Model

Techniques

Using a UserForm as a progress

Excel Is an Object?

Types of Errors

indicator

Climbing the Object Hierarchy

An Erroneous Example

Creating a tabbed dialog box

Wrapping Your Mind around Collections The macro's not quite perfect

Displaying a chart in a dialog

Referring to Objects

The macro is still not perfect

box

Chapter 5: VBA Sub and Function

Is the macro perfect yet

A Dialog Box Checklist

Procedures

Giving up on perfection

Subs versus Functions

Handling Errors Another Way

Looking at Sub procedures

Revisiting the EnterSquareRoot

Chapter 19: Accessing Your

Looking at Function procedures

procedure

Macros

Naming Subs and Functions

About the On Error statement

Through the User Interface

Executing Sub procedures

Chapter 13: Bug Extermination

CommandBars and Excel 2007

Executing the Sub procedure directly

Techniques

Excel 2007 Ribbon

Executing the procedure from the Macro Species of Bugs

Customization

dialog box

Identifying Bugs

Working with CommandBars

Executing a macro by using a shortcut key Debugging Techniques

Commanding the

Executing the procedure from a button or Examining your code

CommandBars collection

shape

Using the MsgBox function

Listing all shortcut menus

Executing the procedure from another

Inserting Debug.Print statements

procedure Executing Function procedures Calling the function from a Sub procedure Calling a function from a worksheet formula Chapter 6: Using the Excel Macro Recorder Is It Live or Is It VBA Recording Basics Preparing to Record Relative or Absolute Recording in absolute mode Recording in relative mode What Gets Recorded? Recording Options Macro name Shortcut key Store Macro In Description

Using the VBA debugger

About the Debugger

Chapter 20: Creating

Setting breakpoints in your code

Worksheet Functions

Using the Watch window

and Living to Tell about It

Using the Locals Window

Why Create Custom Functions?

Bug Reduction Tips

Understanding VBA Function

Chapter 14: VBA Programming

Basics

Examples

Writing Functions

Working with Ranges

Working with Function

Copying a range

Arguments

Copying a variablesized range

Function Examples

Selecting to the end of a row or column A function with no argument

Selecting a row or column

A function with one argument

Moving a range

A function with two arguments

Looping through a range efficiently A function with a range

Prompting for a cell value

argument

Determining the selection type

A function with an optional

Identifying a multiple selection

argument

Changing Excel Settings

A function with an indefinite



2/3

4/9/2016

smartbitcomputers.in/printexcel2.php

Chapter 7: Essential VBA Language Changing Boolean settings

number of arguments

Elements

Changing nonBoolean settings

Functions That Return an Array

Using Comments in Your VBA Code

Working with Charts

Returning an array of month

Using Variables, Constants, and Data Types Modifying the chart type

names

Understanding variables

Looping through the ChartObjects Returning a sorted list

What are VBA's data types

collection

Using the Insert Function

Declaring and scoping variables

Modifying chart properties

Dialog Box

Working with constants

Applying chart formatting

Displaying the function's

Working with strings

VBA Speed Tips

description

Working with dates

Turning off screen updating

Argument descriptions

Chapter 8: Working with Range Objects Turning off automatic calculation

Other Ways to Refer to a Range

Eliminating those pesky alert messages

The Cells property

Simplifying object references

Chapter 21: Creating Excel

The Offset property

Declaring variable types

AddIns

Referring to entire columns and rows

Using the WithEnd With structure Okay . . . So What's an AddIn?

Some Useful Range Object Properties

Why Create AddIns

The Value property

Chapter 15: Simple Dialog Boxes Working with AddIns

The Text property

Why Create UserForms

Addin Basics

The Count property

The MsgBox Function

An Addin Example

The Column and Row properties

Displaying a simple message box

Setting up the workbook

The Address property

Getting a response from a message box Testing the workbook

The HasFormula property

Customizing message boxes

Adding descriptive information

The Font property

The InputBox Function

Protecting the VBA code

The Interior property

InputBox syntax

Creating the addin

The Formula property

An InputBox example

Opening the addin

The NumberFormat property

The GetOpenFilename Method

Distributing the addin

Some Useful Range Object Methods

The syntax

Modifying the addin

The Select method

A GetOpenFilename example

Chapter 22: Ten VBA

The Copy and Paste methods

Selecting multiple files

Questions (And Answers)

The Clear method

The GetSaveAsFilename Method

The Top Ten Questions about

The Delete method

Getting a Folder Name

VBA

Displaying Excel's Builtin Dialog

Boxes

Chapter 23: (Almost) Ten

Excel Resources

The VBA Help System

Microsoft Product Support

Internet Newsgroups

Internet Web Sites

Excel Blogs

Google

Local User Groups

For More Details Please Feel Free To Contact Rahul Sir on 8888228222.



3/3

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download