VBA Application Objects tips

Useful Application  Objects

***Use Screen Updating

Application.ScreenUpdating = False to deactivate

Application.ScreenUpdating = True to activate

**Turn Off automatic calculations

Application.Calculation = xlCalculationManual 'To turn off the automatic calculation

Application.Calculation = xlCalculationAutomatic 'To turn On the automatic calculation

***use this code for calculations whenever you want

ActiveSheet.Calculate ' To calculate the formulas of Active Worksheet

Application.Calculate ' To calculate the formulas of Active workbook or all workbooks in current

***To turn off on cut copy mode

Application.cutcopymode=False or true

Application.Displayhorizontalbar=True or false?Application.Version

***To Turn on/off alerts

Application.displayalerts=true or false

Application.displaycommentindicator

Application.dataentrymode=false or trueApplication.displayformulabar=true or false

Application.displayfullscreen=true or false

VBA Class Worksheet Objects

Worksheet Objects

The Worksheet object is a member of the Worksheets collection and contains all the Worksheet objects in a workbook.

Example

Worksheets(1).visible=false or true

Workshets(“sheetname”).visible=true or false

Range Objects

Range Objects represent a cell, a row, a column, or a selection of cells containing one or more continuous blocks of cells.

Example

Worksheets(1).range(“a3”).value=”date”

Worksheets(“sheetname”).value=”time”

TO DELETE RANGE

    Range("A:K").Delete

    Range("1:20").Delete

FONT

    Range("B2") = "Georgetown Dry Cleaning Services"

    Range("B2").Font.Name = "Rockwell Condensed"

    Range("B5") = "Order Identification"

    Range("B5").Font.Name = "Cambria"

CONTINUE

VBA Class

Day 3

Windows  Excel objects

Application Objects

Workbooks Objects

Worksheets Objects

Range Objects

Application Objects

The Application object consists of the following −

• Application-wide settings and options.

• Methods that return top-level objects, such as ActiveCell, ActiveSheet, and so on.

 

Application Object example

 

Set xlapp = Createobject(“Excel.Sheet”)

Xlapp.application.workbooks.open “C:\test.xls”

Application.windows(“text.xls”).activate

 

Application.activecell.font.bold=true

Workbook Objects

The Work book object is member of the Workbooks collection and contains all the Workbook objects currently in Excel.

Example:

Workbooks.Close

Continue---

Shortcut for VBA

DAY 2

Short cuts VBE

·         Alt+F4  - close VBE

·         Alt+F5   - Run Error Handler

·         Alt+F6   - Swithch between last 2 windows

·         Alt+F7   - Step Error Handler

·         Alt+F11 - return to application

·         Alt+A     - AddIns Menu

·         Alt+D     - Debug Menu

·         Alt+E     - Edit Menu

·         Alt+f      - File Menu

·         Ctrl+F2  - Focus Object menu

·         Ctrl+F4  - close window

·         Ctrl+F8  - run to cursor

·         Ctrl+F10- Activate menu bar

·         Ctrl+A   - Select All

·         Ctrl+E    - Export Module

·         Ctrl+F    - Find

·         Ctrl+G   - Immediate Window

·         Ctrl+H   - Replace

·         Ctrl+I     - Turn On Quick Info

·         Ctrl+J     - List Members

·

·         Ctrl+L    - Show call stack

·         Ctrl+M  - Import File

·

·         Ctrl+N   - New Line

·         Ctrl+P    - Print

·         Ctrl+R  - Project Window

·

·         Ctrl+S    - Save

·         Ctrl+T    - Show Availabe Components

·         Ctrl+V   - Paste

·         Ctrl+downarrow-Next Procedure

·

·         Ctrl+spacebar-Turn on complete word

·         Ctrl+Tab - Cycle

VBE

Abbreviated as VBE, Visual Basic Editor is the tool used to

create, modify and maintain Visual Basic for Applications (VBA)

procedures and modules in Microsoft Office applications. The

Visual Basic Editor is included with most Office programs.

To Display Developer Tab 2007

To display the Developer tab, click on the Microsoft Office

button in the top left of the Excel window and then click on the

Excel Options button. When the Excel Options window appears,

click on the Popular option on the left. Select the option

called "Show Developer tab in the Ribbon". Then click on the OK

button.

To Display Developer Tab 2010

To display the Developer tab, follow these steps: Click the

Microsoft Office Button, and then click Excel Options. Click

Personalize. Click to select the Show Developer tab in the

Ribbon check box.

To Display Developer Tab 2013

follow these two steps:

Choose File→Options or press Alt+FT to open the Excel Options

dialog box. Click the Customize Ribbon tab, select the Developer check box

under Main Tabs in the Customize the Ribbon list box on the

right side of the dialog box, and then click OK.

Learn Visual Basic

VBA-Visual Basic Application

VBE-Visual Basic Editor :  ALT+F11

VBA Agenda

1. Vba Editor
2. Functions
3. Procedures
4. Variables
5. Data Types
6. Operators
7. Arrays           
8. Range Object
9. Looping
10. Worksheet and Workbook events
11. Debugging and error message
12. Interacting with users
13. Storing VBA

VBA EDITOR Shot cut keys

VBA Editor:

• Project Window:                              CTR+R
• Code Window   :                              F7
• Properties             :                           F4          
• Object                    :                         Shift+F7
• Definition    :                                    Shift+F2
• Last Positiion:                                  CTR+Shift+F2
• Object Browser:                               CTR+F2
• Object Window:                               F2
• Immediate window:                       Ctrl+G
• Close VBE                                      Alt+F4
• 
  

Will Continue tomorrow

விவசாயத்தை காப்போம்

பணம் இருப்பவர் மனம் வைத்தால் விவசாயம் காக்கப்படும் முடிந்தவரை பெரிய பெரிய கடைகளில் பொருட்கள் வாங்காமல் விவசாயம் செய்பவரிடம் நேரிடையாக பொருட்கள் வாங்குவோம் ,விவசாயியே விலையை நிர்ணயம் செய்ய வேண்டும் இடை தரகர்கள் அல்ல.



வாழ்க விவசாயி, வாழ்க விவசாயம்

CLICK TO GO

Recharge	Banking	Telecom	Mail	Buy	Social
FREECHARGE	ICICI	VODAFONE	GMAIL	AMAZON	FACEBOOK
MOBIKWIK	HDFC	AIRTEL	YAHOO	EBAY	GOOGLE+
RECHARGEITNOW	KOTAK	JIO	OUTLOOK	SHOPCLUES	INSTAGRAM
CHARGEFORU	AXIS	IDEA	SIFY	JABONG	PINTRUST
PAYTM	BOB	DOCOMO	ONE EMAIL	MYNTRA	YOUTUBE
AMULYAM	*****	*****	****	SATHYA	TWITTER

Astrology	Tamilnews	LearnExcel	Govt.Websites	****	*****
Livingextra	MaalaiMalar	ExcelTutorial	TNPDS.RationCard
Scribd-Jothidam	DinaMalar	Chandoo	VoterId
FreeHoroscope	DailyThanthi	ExcelExposure
Astrology	Dinakaran	ExcelBasics
Astroclass Room	OneIndia	ExcelFormulas
Astrology	TheHinduNews	ExcelFormulas