Introduction

---

Brief Description

Hi Friend, Welcome to ExcelItems –  You’ve been come to right place, if you’re an excel user and looking for some related material. From books to trainers, macros to free downloads, solutions on discussion forums, we’re fully equipped with Excel items here. Ok, have you heard about OpenXL yet? Yessss ! and then you should know it’s our product helping thousands of excel users from newbie to amateur to professionals one. If you’re still unaware of it, then use it today.

---

Discussion Board

Our discussion board is largest Google group with the name, MS Excel and VBA Macros, Here is the link: http://groups.google.com/group/excel-macros/.

This group is 2 years old and 100% resistant to spam, serving above 4000 worldwide members. Microsoft’s Most Valuable Professionals(MVP) like Rodney Powell is active member of this community.

---

Open Excel

It’s Power of Open Source in Microsoft Excel. Yes, It’s a free add-in with source code provided to you on this website from our developer, Ashish Jain. It solves many Excel 2007 problems and automates frequently used tasks for you.

For e.g. it provides Excel 2003 style menu in Excel 2007, super hide a worksheet, run charts slide show, calculate number of business days between two dates, remove all macros, can append 0 to any number of cells and much more. To explore – visit OpenXL here.

---

VBA Macros Collection

Have you ever visited a website with full collection of VBA macros, so that you need not to go on another website? We hope you find us of this type. We are in continuous endeavor to provide you with latest research of us on Macros, so that you don’t waste your precious time in developing what already has been developed. Click here to visit our VBA Macros collection.

---

Tutorials

Today, there are so many institutions, schools, colleges and teachers but where are the good ones? The Good one is you itself, yes we can learn and study better than ever and without anyone. But yes, we need a best guide for best results. Our tutorials are designed in step-by-step manner to help every kind of excel user from newbie to professional and from America to East-Asia. Moreover, they are online and Free.

Click here to access them.

---

Books

If you talk about our experience then we must say books are far better than any online material. If you’re really serious about learning VBA, VSTO or Excel/Access 2007 then we’ve searched and collected some books for our readers. They’ll help you a lot.

and yes the first link on this Books page is MS Office 2007 Home and Student edition in its cheapest price only through our website. Always use Genuine products, since purchasing a product provides you with support and keeps you away from hassles. Click here to access books.

---

Free Downloads

We keep on researching and developing the best excel products and add-ins for you. Moreover they’re free. Please explore our downloads section and you’ll find Stock Trackers for almost major world exchanges, Open XL, EMI Calculator, Budget Planner, CPI calculator and much more . . .

We do not warrant that downloads and examples will meet your requirements or that their operations will be error free but if you report us, we may help you out in removing errors, if any or will improve it to meet your requirements. Please report enhancements/bugs to our Forums. Click here to explore our downloads section.

---

Call a Trainer

If you’re looking to learn excel in step-by-step manner, we’re here to help you. Our professional tutors are Microsoft Certified developers and users and are in the field of training for 3 years (on average).

We deal in every level of training for your every need. i.e. from Beginner to advanced level of training for Excel, VBA and VSTO.

Click here to Call a Trainer.

---

About me

In India, there is saying “If we know us, we’ll get Nirvana (free from the cycle of birth and death)”. So, the one who is still searching about him, can tell you what about himself?

But if you still wanna know, who is the author of http://www.excelitems.com, please visit this link.

---

VBA Macro Codes Examples Collection

---

Visual Basic for Automation (Macros Collection)

---

Workbook

Worksheets

1. Sort all sheets
2. Remove blank worksheets from Current workbook
3. Color all Sheet Tabs
4. Remove color from all sheet tabs
5. Protect all worksheets
6. Remove Password (Unprotect) all worksheets
7. Insert ‘n’ number of Blank Worksheets

Range

1. Color a range based on its percentage value

Select

1. Invert the Selection

Number


Rows/Columns

1. Delete Blank Rows
2. Remove Empty Columns

Text

1. Change Case (Upper, Lower, Proper, Sentence and Toggle)
   
2. Remove Extra Spaces (Left, Right, Both sides and from Inside)

Format

1. Partly Background Color a Cell (Coloring only a portion of Cell)

Form

1. Resize Form during Run-Time

Event Macro

1. Example of Worksheet_Change Event Macro

Miscellaneous

1. Access Internet Explorer (Search Engine Example)
2. Change Image Location
3. Copy Excel Worksheets to MS Word

 

---

Use of Random Function (Rnd) and Select Case statement

---

Brief Description:
Here, I present the code to use random function in association with the Select Case statement. It use random function to generate a random value of S, H, D, or C for the four card suits (spades hearts diamonds or clubs). When user clicks the button, an input box pops up to accepts a value of S, H, D, C If the guess matches the random number respond with “Good guess”, if not then it respond with “Bad Guess”.

View VBA Macro Code Here

---

Delete or Remove Event Macros, VBA Code and Module

---

Brief Description: 

This macro will delete all event macros, sub routines and user defined functions from Active workbook. This requires reference to “Microsoft Visual Basic for Applications Extensibility 5.3” and trusted access to VBA project model.(Developer –> Macro security –> Trust access to VBA project model).

View VBA Macro Code Here

---

Close all saved workbooks

---

Brief: Imagine you’re working on some important excel files and gradually your taskbar is full of open workbooks. Some have similar names (not same ofcourse) and now you want to close all the saved workbooks and want unsaved workbooks to remain open. This macro will definitely solve your purpose.

View VBA Macro Code Here

---

Force Save and Unsave of Workbooks

---

Brief: Sometimes event macros are of great help. These two macros are example of that. While one macro protects a workbook from saving any changes, other macro force changes to be saved, if the former macro is present in the workbook. Both can be considered hacks of excel programming or a trick, but they’re always useful and as an excel programmer you should know these two fundas.

Macro #1: Make Workbook unacceptable to any changes.
Pre-requisite: Event Programming, VBA and Excel
Logic: Use of Workbook’s Saved property and Workbook’s BeforeSave event. As soon as excel encounter any changes, it changes Saved property value to FALSE so when you attempt to save the workbook, excel checks this property value and save the workbook, if it founds to be FALSE but if it is TRUE, then it ignores the operation.
Now we use BeforeSave event and turn this property value to TRUE everytime BeforeSave event fired. So this makes workbook immune to any changes.
Code:
‘PLACE THIS CODE IN THISWORKBOOK, NOT IN SHEETS OR MODULE
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
‘CHEATING APPLICATION BY FORCING SAVED PROPERTY TO BE TRUE
    ThisWorkbook.Saved = True
End Sub

Macro #2. Force changes to be saved in such workbooks (as mentioned above).
Pre-requisite: Event Programming, VBA and Excel
Logic: Disable Events, Save the workbook and then enable events again. Yes, it is that simple and here is its code.
Code:
 Sub ForceSave()
    On Error Resume Next
‘THIS WILL DISABLE ALL EVENTS INCLUDING Workbook_BeforeSave.
    Application.EnableEvents = False
‘SAVE THE WORKBOOK
    ActiveWorkbook.Save
‘ENABLE THE EVENTS AGAIN
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Note: You fill find workbooks with Macro#1 generally VBE password protected, in such case if you want to use Macro#2, either write it in Personal workbook and assign a shortcut key or assign it to a toolbar button.

---

How to Embed Flash in Excel (Office Applications)

---

Query Source : Excel Macros Google Group
Solution Type : Flash and MS Excel
Query by        : Mahesh Parab
Solution by     : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:

Dear All, Any one know the way how to embed a flash project into Excel ?

---

Solution:

1. Select the sheet in which you want to embed the flash project.

2. Go to View -> Toolbars -> Control Toolbox and click on the icon (More Controls in the last) which looks like a little hammer.

3. Select “Shockwave Flash Object” from the alphabetical list.

4. Now your cursor has been reformed into the plus shape, this allows you to create a rectangle shape on the sheet to make space for your Flash Object.

5. Right click on the cross-shaped rectangle box object and click on properties.

6. In the properties box, double click the first option. i.e.Custom

7. Now do the following options as suggested in the “Property Pages” dialog box.
    i.   Provide the Movie URL
    ii.  Check the “Embed Movie” checkbox.
    iii. Check the “Play” checkbox.

8. Click Apply and then Ok.

9. Close the properties box and Control Toolbox.

---

Invert the Selection

---

Description: This will deselect the selected and will select the deselected range in Used range of Active sheet.

View VBA Macro Code Here

---

UnProtect all WorkSheets in the WorkBook

---

Description: This macro will protect all the worksheets of the Active workbook with the password you will provide to them.
View VBA Macro Code Here

---

Protect all WorkSheets in the WorkBook

---

Description: This macro will protect all the worksheets of the Active workbook with the password you will provide to them.
View VBA Macro Code Here

---

Sort all worksheets on the basis of Sheet names

---

Description: This VBA macro code will sort all the worksheets on the basis of their corresponding names.

View Code Here

---

Remove Color of all Sheets tabs

---

Description: This VBA macro code will remove colour from all worksheets tabs in the activeworkbook.

View Code Here

---

Color all sheet tabs in the workbook

---

Description: This VBA Macro will colour all the worksheet tabs present in the Active/Current Workbook.

View VBA Macro Code Here

---

Delete/Remove Blank/Empty Worksheets

---

Description: This VBA Macro will delete blank worksheets from the active workbook.
View VBA Macro Code Here

---

Insert ‘n’ number of worksheets in Active workbook

---

Description: This VBA macro code will allow you to insert ‘n’ number of worksheets in Active workbook.

View VBA Macro Code Here

---

Copy Excel Worksheets to Microsoft Word

---

Description: This VBA Macro code will copy all worksheets of Active workbook to Microsoft Word Document.

View VBA Macro Code

---

Change Picture or Image Location

---

Query Source  : Excel Macros Google Group
Solution Type  : VBA Macro
Query by              : Carlos Pedro (Sister Ray)
Solution by         : Ashish Jain (MCAS; MCA; Lead Trainer, Success Electrons)

---

Query / Problem: I’m trying to change a Picture object that is included in a Excel Worsheet, since I haven’t had much sucess I was wondering if it is possible to change the image source (the file that is being displayed) using VBA.

---

View Solution Here

---

Color only a part of cell using Macro or REPT Function

---

Query Source    : Excel Macros Google Group
Solution Type   : VBA Macro
Query by               : ExcelUser777
Solution by         : Ashish Jain (MCAS; MCA; Lead Trainer, Success Electrons)

---

Query / Problem:
HI
Basically i’d like to only show coloring in part of a cell, so for example
Cell A1 is grey only 33% of the cell is grey -I’d like to color one cell grey 33%. I have several cells to color, different percentages. Once I see one macro I can create the other percentages. Is that possible with a macro?
Appreciate all your help.
Excel 2003 preferably
Thanks,
ExcelUser777

---

Solution:

1. Using Macro

i)   Select the range (It should be in terms of percentage, else change the following code accordingly.)
ii)  Press Alt+F11.
iii) Paste the following code in Code window.
iv) Run the macro and enjoy.

View VBA Macro Code here.

---

2. Using REPT Function

i) Use REPT function to repeat the pipe symbol, | as displayed in formula box Below.

---

How to use VLookUp Function?

---

Query Source    : Excel Macros Google Group
Solution Type   : Functions and Formulas
Query by               : Gargi, FICO-SAP
Solution by         : Ashish Jain (MCAS; MCA; Lead Trainer, Success Electrons)

---

Query / Problem:
Dear All,
Can any of you please help me with how to work on V Look Up , and use functions such as transpose and concatenation. I am working In SAP as FICO .It would be of great help if any of you can give other  relevant functions required for SAP datas to be taken up in Excel.
Thanks in Advance
Regards,
Gargi

---

Solution:

The VLOOKUP function returns a value of a cell from one of the table columns. The VLOOKUP function is an extension of the LOOKUP function.
The letter V at the beginning of the function is an abbreviation for vertical. VLOOKUP searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table.

Figure 1: Shows the results and use of Vlookup function.

i) Searching a value

ii) Searching and Returning a value

Figure 2: Shows the Arguments Box for Vlookup function.

Description of Arguments:

1.Lookup_value: The value for which the calculation is made or the value to be searched in first column of Table_array.
2. Table_array: The range of data where lookup_value is to be searched and the range containing the result.
3. Col_Index_num: Column number of result column. This can be from 1 to total number of columns in Table_array.
4. Range_lookup: This can be True(1) or False(0) only as you need exact match or approximate match respectively.

---

Delete/Remove Blank/Empty Rows/Columns

---

Description: This VBA Macro will delete blank rows from the selection in the active worksheet.
View VBA Macro Code Here

Description: This VBA Macro will remove empty columns from the active worksheet.
View VBA Macro Code Here
 

---

Access Search Engines on Browser (Internet Explorer) using VBA/Excel

---

Query Source   : Excel Macros Google Group
Solution Type   : Internet Explorer and VBA
Query by          : Dave K
Solution by       : Ashish Jain (MCAS;MCA;Lead Trainer, Success Electrons)

---

Query / Problem:
Is there code that could perform the following:

1. Copy the data that lies in the active cell.
2. Go to an open window (in explorer or mozilla) of http://www.ebay.com (or open an entirely new window of ebay.com, if that makes it easier).
3. Paste the data into the search box at http://www.ebay.com (which by default is the active field when a new window of ebay is opened)
4. Hit “enter” at ebay to display the search results.

I am new to VBA and computer programming, but any suggestions or guidance you can provide would be very much appreciated. 

Solution: 
1. Press Alt+F11. (To write the macro in Code Editor.)
2. Go to Tools –> References –> Select “Microsoft Internet Controls”.

3. Copy-Paste the code available here.

4. Now, run this macro as desired and whenever required.

---

Excel Add-ins and Products Downloads

---

Guidelines:

1. All downloads are not free.
2. Join our Google Group to access the download.
3. Please enable macros when prompted.

---

ID: AP0001
Name: Open XL – A Powerful Excel-AddIn
Description : Since its first release Open XL is gaining popularity for its features, user-friendly interface and lots of time-saving features. It requires Microsoft .NET framework 3.5 and VSTO 3.0 Runtime before it can be installed. Run the charts slide show, access internet explorer, calculate business working days, insert or delete text across ranges, take snapshot of ranges, export to MS Word or MS PowerPoint, sort worksheets, Superhide a worksheet, spell numbers, apply rainbow color to ranges and much more. Why don’t you install and check it out yourself. Addins similar to this worth $50 over the internet. Take it as gift from http://www.excelitems.com
Type: Shareware
Download Link: Click here to Download Open XL

---

ID: FE0001
Name: Indian Stock Markets Tracker
Description : Use this excel file to track the two major Indian Stock Market i.e. BSE30 and NSE50. It also carries sector-wise updates of the Indian Stock Market. This small and fast utility can be very useful for Stock analysts and investors. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download Indian Stock Market Tracker

---

ID: FE0002
Name: Australian Stock Markets Tracker
Description : Use this excel file to track the Australia’s major indices like ASX200 Stocks, Midcap 50 etc., Sector-wise updates like S&P Industrials, Energy, Materials, Healthcare and much more. This small and fast utility can be very useful for Stock analysts and investors. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download Australian Stock Market Tracker

---

ID: FE0003
Name: Dow Jones Industrial Average Stocks Tracker
Description : Use this excel file to track the US major index Dow Jones Industrial Average of 30 shares. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download Dow Jones Stocks Tracker

---

ID: FE0004
Name: European Stock Markets Tracker
Description : Use this excel file to track the Europe’s major indices/exchanges like FTSE(London), DAX(Frankfurt), CAC(Paris), MIB (Milan), IBEX(Madrid), OMX(Scandinavia) etc. from one file. This small and fast utility can be very useful for Stock analysts and investors. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download European Stocks Markets Tracker

---

ID: FE0005
Name: Germany (DAX) Stock Exchanges/Markets Tracker
Description : Use this excel file to track the Germany’s major indices like DAX30, HDAX, VDAX, MDAX, SDAX, GEX etc. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download Germany Stocks Markets Tracker

---

ID: FE0006
Name: Hong Kong Stock Markets Tracker
Description : Use this excel file to track the Hong kong’s major index Hangseng. This small and fast utility can be very useful for Stock analysts and investors. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download Hang seng Stocks Tracker

---

ID: FE0007
Name: London (FTSE) Stock Markets Tracker
Description : Use this excel file to track the London’s major indices like FTSE100, FTSE350, FTSE ACT250, FTSE TechMark and FTSE 350 Super Sectors like Automobiles, Banks, Chemicals, Health care, Insurance, Media, Retail, Technology, Food, Beverage etc.. This small and fast utility can be very useful for Stock analysts and investors. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download London Stock Markets Tracker

---

ID: FE0008
Name: NASDAQ Stock Markets Tracker
Description : Use this excel file to track the America’s major index NASDAQ 100. This small and fast utility can be very useful for Stock analysts and investors. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download NASDAQ Stocks Tracker

---

ID: FE0009
Name: Paris (CAC) Stock Markets Tracker
Description : Use this excel file to track the Paris’s major indices like CAC 40, SBF120, SBF250, CAC IT40, CAC Next 20, CAC Mid 100 and many more. This small and fast utility can be very useful for Stock analysts and investors. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download Paris Stock Markets Tracker

---

ID: FE0010
Name: Shanghai (SSE Composite) Stock Markets Tracker
Description : Use this excel file to track the Shanghai, China’s major indices like SSE Composite. This small and fast utility can be very useful for Stock analysts and investors. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download Shanghai Stock Markets Tracker

---

ID: FE0011
Name: Singapore (Strait) Stock Markets Tracker
Description : Use this excel file to track the Singapore’s major indices like STRAIT. This small and fast utility can be very useful for Stock analysts and investors. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download Singapore Stock Markets Tracker

---

ID: FE0012
Name: US Stock Markets Tracker
Description : Use this excel file to track the major US indices like DJIA, DJCA, All NASDAQ, S&P, Russell and AMEX indices. This small and fast utility can be very useful for Stock analysts and investors. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download US Stock Markets Tracker

---

ID: FE0013
Name: World Stock Markets Tracker
Description : Use this excel file to track the World’s major stock exchanges like Dow Jones, NASDAQ100, Nikkei225, FTSE100, DAX30, CAC40, BSE30, NSE50, Hangseng, SSE Composite, ASX200, STRAIT etc.. This small and fast utility can be very useful for Stock analysts and investors. Please use it and provide us with your valuable feedback. The content and downloads are free on this website as long you will help us in promoting this website by passing a link to this page to your friends instead of directly mailing our softwares/add-ins and utilities.
Type: Shareware
Download Link: Click here to Download World Stock Markets Tracker

---

CEILING and FLOOR Function

---

Query Source   : Excel Macros Google Group
Solution Type   : Functions and Formulas
Query by          : DM
Solution by       : Ashish Jain (MCAS;MCA;Lead Trainer, Success Electrons)

---

Query / Problem:
Ok, can someone help me out?  I am trying to create formula that is going to round my numbers to x.47 or x.97.
A1=$9.99
B1=75%
C1=A1-B1=$2.49
So what I am trying to do is to take discount from price and if it ends x.49 I would like to round it x.47 and if it ends x.99 I would like to round it to x.97. Does this make sense….so everything between x.51 and x.99 should be rounded to x.97 and everything between x.01 to x.49 should be rounded to x.47. 

---

Solution:

Assuming the contents as follows
Column A (Actual Price) – A1 = 9.99
Column B (Discount)      – B1 = 75%
Column C (Sale Price)    – C1 = ‘=A1*(1-B1)‘

The Solution would be
Column D (Tag Price)     – D1 = ‘=FLOOR(C2,1)+IF(CEILING(C2,1)-(C2)<0.5,0.97,0.47)‘

---

Example:

---

Resize Form using Worksheet Event macro

---

Query Source  : Excel Macros Google Group
Solution Type   : Forms and VBA
Query by          : Kanwaljit
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:
I have a User Form which needs to take a specified size depending upon the data source. Data Source is in Range DataS1 and DataS2. The following range (named as “Dimensions”) shows the dimensions which the User Form needs to have when ever we use DataS1 or DataS2.UserFormSpec    DataS1      DataS2
FormWidth                   50            100
FormHeight                  40             80

Cell A1 of Sheet1 contains a dropdown containing the names of the datasources, i.e., DataS1 and DataS2. The following code is used to show the UserForm.

Private Sub MyForm_Click()
        UserForm.Show
End Sub

What is wish is that when I select DataS1 or DataS2 from Cell A1, the UserForm width and height changes according to the numbers in range “Dimensions”.

---

Solution:

Brief Description: Solution requires use of Event Macros, VBA, Forms, Object Hierarchy. Even if you don’t know refer below to solve such problems in a very easy manner.
As you want to re-size the form whenever value in Cell A1 of Sheet1 changes, you mustuse event macro to achieve this. Since event macros are automatically fired in response to associated events. Following are some examples of excel events for which event macros are already in the MS Excel applications:
      Name of Event                           |             Event Macro
1. Opening of Workbook              |          Workbook_Open()
2. Double click a cell                     |          Worksheet_BeforeDoubleClick
3. Activate a Sheet                        |          Worksheet_Activate()
4. Pivot Table Update                  |           Worksheet_PivotTableUpdate
5. Change Cell Value                     |            Worksheet_SelectionChange
etc etc etc

Since, each event macro is associated with an event, it is also associated with a particular object. Like, if defined then a workbook event macro is associated to the workbook where it is defined. So if you’ve written a workbook event macro in Workbook A then it won’t affect Workbook B. Same for Sheets, pivot tables etc.

Full Solution to the Query:
1. Open the desired Excel file.
2. Go to VBE by pressing Alt+F11.
3. Under Microsoft Excel Objects for this workbook, Double Click on Sheet1 to open it’s code window.
4. Paste the Code Available here in it.

5. Test your Macro by changing the value of Cell A1.

---

 Example: 

---

10 ways to Move in a worksheet in Excel 2007

---

To move around in a worksheet, you can use the arrow, Tab, Page Up, Page Down and Enter Keys. Look down, to see how these keys can be used to move around in a worksheet.

1. The Right Arrow Key
Press the right arrow key several times and notice that right adjacent cell to current active cell will get active.

2. The Down Arrow Key
Press the down arrow key several times and notice that bottom adjacent cell to current active cell will get active.

3. The Left Arrow Key
Press the left arrow key several times and notice that left adjacent cell to current active cell will get active.

4. The Up Arrow Key
Press the up arrow key several times and notice that top adjacent cell to current active cell will get active.

5. The Tab Key
Press the Tab key several times and notice that right adjacent cell to current active cell will get active. The difference between tab and right arrow key is that when you are modifying a cell content, tab will still function in the same way as mentioned but Right arrow key will traverse through character by character in the cell.
Example: 1. Change the value of cell A1 to “Hello Excel”.
2. Select cell A1 and press F2. Now try left and right arrow keys.
3. Now Press Tab.

I hope the difference must be cleared now.

6. The Shift-Tab Key Combination
Press the Shift-Tab key several times and notice that left adjacent cell to current active cell will get active.

7. The Page Down Key
Press the Page Down key several times and notice that cursor or selected cell moves down by one page.

8. The Page Up Key
Press the Page Up key several times and notice that cursor or selected cell moves up by one page.

9. The Ctrl+Home Key Combination
Press the Ctrl+Home key from any selected cell and cursor will move to cell A1 always.

10. The Ctrl+End Key Combination
Press the Ctrl+End Key Combination and notice that cursor or selected cell moves to the last cell (last column and last row of used range).

---

Remove Extra Spaces from a Cell Value – Text VBA Macro

---

Left Trim
Description: This module will remove extra spaces from left side of selected cells. For e.g if a cell value is ”    excel    “ then after running the macro it will become “excel    “. (Notice spaces from left side has been removed.)

View VBA Macro Code Here

---

Right Trim
Description:This module will remove extra spaces from right side of selected cells. For e.g if a cell value is ”      excel   “ then after running the macro it will become ”     excel”. (Notice spaces from right side has been removed.)

View VBA Macro Code Here 

---

 Trim from Both sides
Description:This module will remove extra spaces from both sides of selected cells. For e.g if a cell value is ”      excel   “ then after running the macro it will become “excel”. (Notice spaces from both sides has been removed.)
View VBA Macro Code Here

---

Remove Extra Excessive Spaces
Description:This module will remove extra spaces from both sides and from inside the selected cells. For e.g if a cell value is ”      excel       items   “ then after running the macro it will become “excel items”. (Notice spaces from left , right and between excel and items words has been removed.)
View VBA Macro Code Here 

---

Change Case (Upper Case, Lower Case, Proper Case, Toggle and Sentence Case)

---

Change Text Case

---

UPPER CASE

Description: A word or sentence is said to be in upper case if its all the letters are in capital letters like this: EXCELITEMS IS BEST COLLECTION OF EXCEL RELATED STUFF.
View VBA Macro Code Here

---

lower case
 Description: A word or sentence is said to be in lower case if its all the letters are in small letters like this: excelitems is best collection of excel related stuff.
View VBA Macro Code Here

---

Proper Case 
Description: A word or sentence is said to be in proper case if first letter of each word is in capital letter and rest are in small letters like this: Excelitems Is Best Collection Of Excel Related Stuff.
View VBA Macro Code Here

---

Sentence case
Description: A sentence is said to be in sentence case if first letter of sentence is in capital letter and rest are in small letters like this: Excelitems is best collection of excel related stuff.
View VBA Macro Code Here

---

ToGgLe CaSe
Description: A word or sentence is said to be in toggle case if first letter of sentence is in capital letter and next is in small letter then next in capital letter and so on like this: ExCeLiTeMs iS BeSt cOlLeCtIoN Of eXcEl rElAtEd sTuFf.
View VBA Macro Code Here

---

ViewColor Entirerow based on a column’s value

---

Query Source   : Excel Macros Google Group
Solution Type   : Event Macro and Data Validation
Query by          : Avinash Daga
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:

I have two buttons on column P which called active and deactivate.When I click active it should change color of  that row as green,means that project is active and once that is completed i can deactivate that by changing it to original text color. This is working for raw 1 but when I am dragging it down to make it work for all rows I am not able to do so.Please help me to have that thing changed for each row. 
Link to Problem File

---

 Solution:

Instead of using buttons a better solution is to include Data Validation (as you’ve used it in other columns) and Worksheet Event Macro (Worksheet_Change). Here is how you can do this:
Data Validation
1. Select Cell P2.
2. Go to Data –> Validation –> Settings.
3. Change Allow to List and Source to Active,Deactive. Click Ok.
4. Copy the cell P2 using Fill series/Fill Down or any other method you wish.

Use Worksheet Event Macro
Event macros should be placed only in the code window of target object. Since in this case our target is Sheet 3, we will place our macro in Sheet 3 only. This is because event macros only listens the events with which they are attached to. Here is event macro for this problem.

---

View VBA Macro Code Here:

---

Example: How and where the macro is to be placed?
Link to Solution File.

---

Show the First Day of Current Week

---

Query Source   :Excel Macros Google Group
Solution Type  : Custom Formula
Query by          : Savio
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:
is there any way to have a cell in excel update itself every week to show the first day of the week?
say the week starts on a monday, the cell displays 23/02/09, retains this value thoughout the week and displays 02/03/09 the following monday.

Solution:
1. Select the desired cell.
2. Press F2 and paste the following formula in it.
     =WEEKNUM(NOW())*7*(YEAR(NOW())-2008)+39804
3. press Enter and you’re done with solution.
Example:

---

Count 5 Consecutive Zeroes

---

Query Source   :Excel Macros Google Group
Solution Type   : User Defined Function
Query by        : Lavprasad Kori (MIS Executive, Hindustan UniLever Limited)
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query/Problem :

I want an urgent help for the below metioned data.Attaching hererwith the data file.

I want a help to count the zero value if it comes continues 5 times or more than 5 times then count as 1 time & If it comes continue less than 5 times then ignore it.

Solution:

1. Press Alt + F11.

2. Paste the following code there.(Create a User Defined Function)

---

View User Defined Function Code Here

---

3. Press Alt+Q.

4. Type “=Count_5_Zeroes()” in AD4 as above in the picture.

5. You’re done in just 4 simple steps.

---

Tutorials to Learn MS Excel 2007

---

Tutorials

---

Microsoft Excel 2007

—-Beginner
      1. Introduction to Microsoft Excel 2007 and its components
      2. 10 ways to move in a worksheet in MS Excel 2007
      3. View more data in a worksheet
      4. Move between worksheets and Open workbooks
      5. Go to Cells quickly in Excel 2007
      6. Select Cells Using Keyboard and Mouse
      7. How to enter and delete data in MS Excel 2007
      8. How to edit or modify a cell in Excel 2007

—-Intermediate

—-Advanced

---

Excel Functions

—-Beginner
—-Intermediate
—-Advanced

---

MS Excel Macros Programming

—-VBA

——-Beginner
——-Intermediate
——-Advanced

—-VSTO

——-Beginner
——-Intermediate
——-Advanced

---

How to edit or modify a cell in Excel 2007?

---

1. Using Formula Bar.
a. Select the cell of which contents needs to be changed.
b. Click the formula area of formula bar.
c. Place your cursor according to modification and use backspace/delete key to remove the undesired contents and insert new data by simply entering it.

2. Using F2 key.
a. Select the cell of which contents needs to be changed.
b. Press F2 key.
c. Place your cursor according to modification and use backspace/delete key to remove the undesired contents and insert new data by simply entering it.

3. Using Double Click.
a. Double click on the cell of which contents needs to be changed.
b. Place your cursor according to modification and use backspace/delete key to remove the undesired contents and insert new data by simply entering it.

4. Replace the cell contents.
a. Select the cell of which contents needs to be replaced.
b. Start typing the new data. (Remember, its replacement not modification, so old data will be replaced with new content, you’ve just typed.)

---

How to Enter and Delete data in Excel 2007 ?

---

1. Enter Data in a Cell.
a. Select the cell in which you want to enter the data.
b. Type some data and press Enter.
c. If you need to correct what you’ve entered, please read the next post or type backspace before step b.

2. Delete Data from a Cell.
a. Select the cell of which you want to delete the data.
b. Press Delete.
*Note: Read next post (How to edit a cell?), if you only need to delete some characters, not the whole data of a cell.

---

Select Cells using keyboard and Mouse in Excel 2007

---

To select or activate specific cells or a group of cells, we can use variety of ways to go through them. Some of them are listed and explained below.

1. The Name Box
Like “Go to” explained in previous post, the NAME Box can also be used to go to a specific cell. Just type the cell you want to go to in the NAME box and then press Enter.

---

2. Select last (or first) cell in a range.
 Use any of the keyboard combinations to move to the first (or last) cell in a range:
   i. Ctrl+Down Arrow –> Move to last cell vertically (Alternate: [End] then Down Arrow)
   ii. Ctrl+Up Arrow –> Move to first cell vertically (Alternate: [End] then Up Arrow)
   iii. Ctrl+Right Arrow –> Move to last cell horizontally (Alternate: [End] then Right Arrow)
   iv. Ctrl+Left Arrow –> Move to last cell horizontally (Alternate: [End] then Left Arrow)

---

3. Select the last cell of worksheet in used range.
  As we keep on enter the data in a worksheet, Excel keeps on storing the address of the last used cell. To find out which cell is the last used cell in the used area in active worksheet,
    a. Using Keyboard
        i. Press Ctrl+End key combination.
    b. Using Mouse
        i. Click Home
        ii. Click Find & Select
        iii. Click “Go to Special…”
        iv. Select “Last cell”
        v. Click Ok.

---

4. Select the first cell (A1) of Worksheet.
    Press Ctrl+Home key combination.

---

5. Select a group of cells / Select an contiguous range area of worksheet.
    a. Using Mouse
        i. Select top-left cell of the desired range to be selected.
        ii. Press Left button of mouse and don’t release it until asked.
        iii. Keep moving mouse and you can see the selected range area in different color.
        iv. Reach to the bottom-left cell of the desired range.
        v. Release the left mouse button.
    b. Using Keyboard
        i. Select top-left cell of the desired range to be selected.
        ii. Press F8 key to anchor the cursor.
        iii. Select bottom-left cell of the desired range.
        iv. Press Esc.

---

6. Select Non-adjacent cells.
    a. Using Mouse
        i. Select top-left cell of the desired range to be selected.
        ii. Press Ctrl and don’t release it until asked.
        iii. Select desired ranges.
        iv. Release the Ctrl button.
    b. Using Keyboard
        i. Press Shift-F8 key to anchor the cursor. Sentence “Add to selection” will appear in status bar.
        ii. Select desired ranges.
        iii. and when you’re done, press Esc to toggle off the option of non-contiguous selection.

---

7. Select Horizontal range of adjacent cells.
   Use any of the keyboard combinations to select the horizontal range of adjacent cells:
        i. Ctrl+Shift+Right Arrow –> Select horizontal range of adjacent cells which contains data from left to right.
        ii. Ctrl+Shift+Left Arrow –> Select horizontal range of adjacent cells which contains data from right to left.

---

8. Select vertical range of adjacent cells.
    Use any of the keyboard combinations to select the vertical range of adjacent cells:
        i. Ctrl+Shift+Up Arrow –> Select vertical range of adjacent cells which contains data from bottom to top.
        ii. Ctrl+Shift+Down Arrow –> Select vertical range of adjacent cells which contains data from top to bottom.

---

9. Select the current region.
    Continuous range of cells that contains data around the selection (including the selection) is termed as current region. thus, current region is always enclosed by either blank rows or blank columns or blank cells or all.
    a. Using Keyboard
        i. Press Ctrl+Shift+8.
    b. Using Mouse
        i. Click Home
        ii. Click Find & Select
        iii. Click “Go to Special…”
        iv. Select “Current region”
        v. Click Ok.

---

Move between Worksheets and Open Workbooks

---

Move between Worksheets

An excel workbook can contain a large number of worksheets. In such cases, switching between the sheets is difficult using a mouse. In addition, if the sheets name are long, this could further increase the problem of selection. apart from annoying method of selecting tabs from mouse, you can use any of the following available methods:

Using Keyboard
Use Ctrl+Page Down key combination to select next sheet in active workbook
and similarly, use Ctrl+Page Up key combination to select previous sheet in active workbook.

Using shortcut menu
1. Look at the bottom-left of worksheet (to the left of sheet tabs), there are small arrow buttons.

2. Place the mouse pointer over one of the arrows and right click.
3. A shortcut menu, displaying all the sheets will appear, select your sheet from there.

---

Move between Open Workbooks
Using Mouse
1. Click view.

2. Click Switch Windows and choose a workbook from list of open workbooks.

Using Keyboard
1. Use Ctrl+F6 or Ctrl+Tab key combination to move forward through list of open workbooks.
2. Similarly, use Ctrl+Shift+F6 or Ctrl+Shift+Tab key combination to move backward through list of open workbooks.

---

View more data in a worksheet

---

An Excel worksheet contains thousands of columns and millions of rows, but if your data is spread even over a hundreds of rows and columns, it becomes difficult to view the data. Depending on the data and requirement, you can magnify or reduce the selected data region to the size of window.

---

1. Fit Selection to size of window
a. Press Ctrl+* (Ctrl+Shift+8 key Combination). This will select the active used range region.
b. Click View.

c. Select Zoom.

d. Select Fit selection.

e. click ok.

---

2. Magnify or reduce the view size of selected region
a. To magnify the worksheet view, move the Zoom Slider(placed in bottom right of the workbook) to right.

b. To reduce the worksheet view, move the Zoom Slider(placed in bottom right of the workbook) to left.

---

3. Increase the amount of data (Fullscreen view)
This will hide the ribbon, formula bar and status bar, allowing you to view more amount of data without magnifying or reducing the workshet view.
a. Click View.

b. Select FullScreen.

---

Introduction to Microsoft Excel 2007 and its components

---

Objective: This tutorial will navigate through Introduction to Microsoft Excel 2007 and its components. Knowledge of windows environment will be helpful, however tutorial is prepared for a computer novice.

---

Open Microsoft Excel 2007

Microsoft Excel is an electronic spreadsheet that can be used to organize data rows and columns, to perform mathematical calculations quickly and can also be programmed to send your mails at pre-defined time.

If you have purchased a valid licensed copy of Microsoft Excel 2007 then open it following the instructions given below:
1. Go to Start –> Programs –> Microsoft Office –> Microsoft Excel 2007

---

The Microsoft Excel 2007 Window

The Microsoft Excel 2007 window appears and our screen looks similar to the one shown here.

Screen always might not look exactly like the screen shown. In Excel 2007, display of window depends on the size of the monitor and the resolution to which the monitor is set. Resolution determines how much information the computer monitor can display. A low resolution means less information fits on the screen, but the size of text and images are larger. Inversely, a high resolution means more information fits on the screen, but the size of the text and images are smaller. Also, settings in Excel 2007, Windows Vista, and Windows XP allow you to change the color and style of your windows.

---

The Microsoft Office Button

Look at the upper-left corner of the Excel 2007 window, it is the Microsoft Office button. When you click this button, a menu will appear. This menu can be used to create a new file, open an existing file, save a file, and perform many other tasks.

---

The Quick Access ToolBar

Right to the Microsoft Office button is the Quick Access toolbar. The Quick Access toolbar gives access to commands we frequently use. By default, Save (to save your files), Undo (to rollback an action), and Redo (to reapply an rolled back action) appear on the Quick Access toolbar.

---

The Title Bar

Next to the Quick Access toolbar is the Title bar. On the Title bar, Microsoft Excel displays the name of the workbook, which is currently in use.

---

The Ribbon

We use commands to tell Microsoft Excel what to do. In Microsoft Excel 2007, we use the Ribbon to issue commands. The Ribbon is located near the top of the Excel window and below the Quick Access toolbar. At the top of the Ribbon are several tabs; clicking a tab displays several related command groups. Within each group are related command buttons. Buttons are clicked to issue commands or to access menus and dialog boxes. You may also find a dialog box launcher in the bottom-right corner of a group. When you click the dialog box launcher, a dialog box makes additional commands available.

---

The Formula Bar

If the Formula bar is turned on, then in the Name box (located on left side) it displays the cell address of the cell you are in. Cell entries which can be a values or formulas are displayed on the right side of the Formula bar. To turn on the Formula bar in Excel 2007 window, perform the following steps:

1. Click the View tab.
2. Click Formula Bar in the Show/Hide group. The Formula bar appears.

---

The Status Bar

The Status bar appears at the bottom of the Excel 2007 window and provides quick information such as the count, sum, average, minimum, and maximum value of selected numbers. We can change what displays on the Status bar by right-clicking on the Status bar and selecting the options we want from the Customize Status Bar menu. We just need to click a menu item to select it and click it again to deselect it. A check mark appearing next to an item means the item is selected.

---

The Worksheet

Microsoft Excel 2007 consists of worksheets (Default Names are “Sheet1”, “Sheet2” and “Sheet3”). Each worksheet contains columns and rows. The columns are lettered A to Z and then continuing with AA, AB, AC to AZ and then continuing with AAA, AAB and so on up to XFD (total columns are 16384); the rows are numbered 1 to 1,048,576.

The combination of a column coordinate and a row coordinate defines a cell address. For example, the cell located in the upper-left corner of the worksheet is cell A1, meaning column A, row 1. Cell C7 is located under column C on row 7. We enter our data into the cells on the worksheet.

---

1. Enter Data in a Cell.
a. Select the cell in which you want to enter the data.
b. Type some data and press Enter.
c. If you need to correct what you’ve entered, please read the next post or type backspace before step b.

2. Delete Data from a Cell.
a. Select the cell of which you want to delete the data.
b. Press Delete.
*Note: Read next post (How to edit a cell?), if you only need to delete some characters, not the whole data of a cell.

---

Go to Cells Quickly in Excel 2007 ( Home –> Find & Select –> Go to )

---

1. Open “Go to” Window
  a. Using Mouse
      i. Click Home.

      ii. Click “Find & Select”
      iii. Select “Go to”

  b. Using Keyboard
      i. Press F5 or Ctrl+G key combination.

2. In the reference field, type the name of cell. e.g. J10, B5, AE100
    (*Note: Column name appear first in this style, followed by Row number.)

3. Click Ok

---

VSTO Macro Codes Examples

---

Visual Studio Tools for Office ( Macros Collection )

---

Workbook

Worksheets

1. Sort all sheets
2. Remove blank worksheets from Current workbook
3. Color all Sheet Tabs
4. Remove color from all sheet tabs
5. Protect all worksheets
6. Remove Password (Unprotect) all worksheets
7. Insert ‘n’ number of Blank Worksheets

Range

1. Color a range based on its percentage value

Select

1. Invert the Selection

Number


Rows/Columns

1. Delete Blank Rows
2. Remove Empty Columns

Text

1. Change Case (Upper, Lower, Proper, Sentence and Toggle)
   
2. Remove Extra Spaces (Left, Right, Both sides and from Inside)

Format

1. Partly Background Color a Cell (Coloring only a portion of Cell)

Form

1. Resize Form during Run-Time

Event Macro

1. Example of Worksheet_Change Event Macro

Miscellaneous

1. Access Internet Explorer (Search Engine Example)
2. Change Image Location
3. Copy Excel Worksheets to MS Word

---

About us

---

Meet your Blog Author

---

Name : Ashish Jain
Age : 25 years.
Interests: Writing Blogs, Solving Excel queries, River Rafting in Rishikesh, Freaking out with Friends

---

Job Profile : I’m working with CSC India Pvt. Ltd. as Demand Coordinator, helping the organization with my Excel Automation expertise. My total work experience tolls to 20+ months. I consider MS Excel as best analytical tool that is used by newbie to highly technical person all over the world. I particularly like Pivots, Charts and Macros in Excel.

---

Academic Qualification : Master of Computer Applications, M.Sc. (Mathematics)
Technical Qualification : Microsoft Certified Professional, Microsoft Certified Application (Excel) Specialist

---

Other Interests : Passionate about Success, Deliver lectures and seminars on Leadership, Positive Thinking and Relationship Handling (Behavioral) and on MS Excel, Scripting Languages like Perl, Shell Scripting and VB Script(Technical).

---

Call/Contact Trainer for Excel/VBA/VSTO

---

What makes us one of the best corporate excel trainer?

1. Our Course Content (as given below)
2. Microsoft Certified Persons
3. Training exposure to all industries. viz. Electronics to Finance.
4. Ready spreadsheets for hands on experience.
5. Step-wise example files.
6. Understanding of real needs.
7. Quality assessment
8. Relevant examples.

We’re flexible with training hours and course content. Please go through our standard course content given below and feel free to contact us anytime for your queries and confirmations.

Call us: 09999-40-48-43
Mail: jainashish@live.com
Course Content available here

---

Books on Excel, VBA and VSTO Macros

---

Title: Microsoft Office Home and Student 2007
Product by: Microsoft
Product Features:
1. Essential software suite for home computer users makes it a pleasure to complete schoolwork and other tasks
2. Includes 2007 versions of Excel, PowerPoint, Word, and OneNote.
3. Intuitive user interface that exposes commonly used commands; updated graphics and formatting galleries help you to easily produce high-quality documents.
4. Work with confidence and security thanks to the improved automatic Document Recovery tool and the Document Inspector tool, which removes personally identifiable information from your document
5. Enhanced Help system includes online tutorials with step-by-step instructions; includes OneNote, a digital notebook that helps you gather, organize, and search many types of information in one place.

---

Title: Excel 2007 Power Programming with VBA
Author: John Walkenbach
Pages: 1104
Description: This power-user’s guide is packed with procedures, tips, and ideas for expanding Excel’s capabilities with Visual Basic® for Applications. Excel 2007 has a few new tricks up its sleeve, and John Walkenbach helps you make the most of them all.
You’ll learn to customize Excel UserForms, develop new utilities, use VBA with charts and PivotTables, and create event-handling applications. Work with VBA subprocedures and function procedures, facilitate interactions with other applications, build user-friendly toolbars, menus, and help systems, and much more. Get ready to make Excel do your bidding.

---

Title: Excel 2007 Formulas
Author: John Walkenbach
Pages: 840
Description: If you already know your way around the newest Excel and want to concentrate on using formulas to extend its capabilities even further, you’ve come to the right place.

It’s estimated that only about ten percent of Excel users really understand how to get the most out of worksheet formulas. If you’re ready to join that elite group, here’s your book which has:
1. Master operators, error values, and absolute vs. relative references.
2. Find errors and debug formulas.
3. Use pivot tables to eliminate formulas.
4. Create single- and multi-cell array formulas.
5. Build custom functions with VBA.

---

Title: Access 2007 VBA Programmer’s Reference
Authors: Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Armen Stein
Pages: 1152
Description: Written by Access MVPs and Microsoft Access testers, this resource will give you the tools to leverage Access 2007’s built-in functionality and VBA in order to build more dynamic applications. It explores all aspects of VBA programming for Access and describes the latest innovations. You’ll also find several commonly used techniques and sample code to help you get started, as well as expert tips to make your code easier to maintain.

The expert author team will show you how to create and name variables, manipulate data using Data Access Object (DAO) and ActiveX® Data Object (ADO), handle errors, and create classes. You’ll also learn how to utilize the latest wizards as well as modify and enhance the code that the wizards create. And you’ll learn how to take advantage of key new objects, enhanced macros, and the Office Ribbon.

---

Title: Microsoft Visual Basic 2005 Step by Step
Author: Michael Halvorson
Pages: 608
Description: Visual Basic 2005 focuses on enabling developers to rapidly build applications, with enhancements across its visual designers, code editor, language, and debugger that help accelerate the development and deployment of robust, elegant applications across the Web, a business group, or an enterprise. Now you can teach yourself the essentials of working with Microsoft Visual Studio® 2005 and the new features of the Visual Basic language—one step at a time. With STEP BY STEP, you work at your own pace through hands-on, learn-by-doing exercises. Whether you’re a beginning programmer or new to this specific language, you’ll understand the core capabilities and fundamental techniques for Visual Basic 2005. Each chapter puts you to work, showing you how, when, and why to use specific features of Visual Basic and guiding as you create actual components and working applications for Microsoft Windows®. You’ll also explore data management and Web-based development topics.

---

Title: VBA and Macros for Microsoft Office Excel 2007
Author: Bill Jelen and Tracy Syrstad
Pages: 624
Description: The book assumes that you know Excel well, but there is no need for prior programming experience. This book describes everything you could conceivably need to know to automate reports and design applications in Excel VBA. Whether you want to automate reports for your office or design full-blown applications for others, this book is for you.

“In this day and age of ‘too much information and not enough time,’ the ability to get to the bottom line quickly and in a concise method is what excels companies to the top of their industry. The techniques in this book will allow you to do things you only dreamt of.”
—Jerry Kohl, president of Brighton Collectibles

---

Title: Programming Excel with VBA and .NET
Author: Jeff Webb and Steve Saunders
Pages: 1114
Description: This book teaches you how to use Excel VBA by explaining concepts clearly and concisely in plain English, and provides plenty of downloadable samples so you can learn by doing. You’ll be exposed to a wide range of tasks most commonly performed with Excel, arranged into chapters according to subject, with those subjects corresponding to one or more Excel objects. With both the samples and important reference information for each object included right in the chapters, instead of tucked away in separate sections, Programming Excel with VBA and .NET covers the entire Excel object library. For those just starting out, it also lays down the basic rules common to all programming languages. Developers looking forward to .NET development will also find discussion of how the Excel object model works with .NET tools, including Visual Studio Tools for Office (VSTO).

---

Title: Integrating Excel and Access
Author: Michael Schmalz
Pages: 232
Description: Introducing “Integrating Excel and Access,” the unique reference that shows you how to combine the strengths of Microsoft Excel with those of Microsoft Access. In particular, the book explains how the powerful analysis tools of Excel can work in concert with the structured storage and more powerful querying of Access. The results that these two applications can produce together are virtually impossible to achieve with one program separately.

But the book isn’t just limited to Excel and Access. There’s also a chapter on SQL Server, as well as one dedicated to integrating with other Microsoft Office applications.

---

Title: VBA for Modelers: Developing Decision Support Systems Using Microsoft® Excel
Author: S. Christian Albright
Pages: 204
Description: Learn more effective modeling techniques than ever before! VBA FOR MODELERS: DEVELOPING DECISION SUPPORT SYSTEMS USING MICROSOFT ® EXCEL shows you the easy way to automate methods and models and create special applications. You’ll learn sophisticated techniques through a simple and clean interface, so there’s no more long nights of trying to make it work. And because it works with Microsoft ® Excel, you’ll be saving time while the program crunches all the numbers.

---

Title: Visual Studio Tools for Office 2007: VSTO for Excel, Word, and Outlook
Author: Eric Carter and Eric Lippert
Pages: 1120
Description: This is the definitive book on VSTO 2008 programming, written by the inventors of the technology. VSTO is a set of tools that allows professional developers to use the full power of Microsoft Visual Studio 2008 and the .NET Framework to program against Microsoft Office 2007.

This book delivers in one place all the information you need to succeed using VSTO to program against Word 2007, Excel 2007, and Outlook 2007, and provides the necessary background to customize Visio 2007, Publisher 2007, and PowerPoint 2007. It introduces the Office 2007 object models, covers the most commonly used objects in those object models, and will help you avoid the pitfalls caused by the COM origins of the Office object models. Developers who wish to program against Office 2003 should consult Carter and Lippert’s previous book, Visual Studio Tools for Office.

In VSTO 2008, you can build add-ins for all the major Office 2007 applications, build application-level custom task panes, customize the new Office Ribbon, modify Outlook’s user interface using Form Regions, and easily deploy everything you build using ClickOnce.

---

Title: Pro Office 2007 Development with VSTO
Author: Ty Anderson
Pages: 320
Description: This book is for Professional Microsoft Office application developers (and those aspiring to be so) who are interested in creating enterprise applications on the solid base of Microsoft Office 2007. The complexity of the examples assumes an intermediate–to–advanced level of experience with Microsoft .NET. Proficient VBA developers wishing to take their skills to the next level will also benefit by this book.

This takes you far beyond traditional VBA programming, showing you how to transform Microsoft Office 2007 into a complete enterprise application development platform. By the end of the book, you will be creating your own powerful, customized Office Business Applications (OBAs), using the techniques Ty has taught you throughout. The author demonstrates how to leverage all aspects of the Microsoft Office application platform (covering Word, Excel, Outlook, PowerPoint, Visio, and InfoPath) so you can create OBAs that increase information worker productivity, unlock business data stored in documents, reduce end–user training costs, increase developer productivity, and reduce IT operations costs.

---

Title: VSTO for Mere Mortals(TM): A VBA Developer’s Guide to MS Office Development Using VSTO
Author: Paul Stubbs and Kathleen McGrath
Pages: 736
Description: This is for VBA developers who are interested in migrating their skills to the next generation of Office development. Readers will benefit from a straightforward, practical introduction to writing managed code applications for Word 2003, Excel 2003, and Outlook 2003. Readers will also learn how to create add-ins for the most popular applications for Office 2003 and the 2007 Microsoft Office system using VSTO 2005 SE.

The expert authors provide a wealth of code samples that show off popular features of VSTO, such as smart tags and the actions pane. Sample code also shows you how to customize the new UI features of the 2007 Microsoft Office system, including the ribbon, custom task pane, and Outlook forms region.

---

Title: Beginning Office 2007 Development with VSTO
Author: Vivek Thangaswamy
Pages: 300
Description: This book starts from the beginning, building a solid programming foundation in VSTO for brand–new Office developers and keen enthusiasts. Author Vivek Thangaswamy, a professional application developer and Microsoft MVP, uses his vast experience to help you better understand the ins and outs of VSTO in the context of each of the popular Office applications: Word, Excel, InfoPath, Outlook, PowerPoint, and Visio.

This book takes you beyond the traditional VBA programming boundaries and shows you the new world of transforming Microsoft Office 2007 into a complete enterprise application development platform. By the end, you will be able to create your own fully featured Office extensions using the simple examples that the author lays out for you.

---

Title: Word 2007 Document Automation with VBA and VSTO
Author: Scott Driza
Pages: 500
Description: This book focuses on innovative ways to create customized Word documents and templates. It contains an in-depth introduction to VBA (Visual Basic for Applications), which is the embedded programming language in the Microsoft Office 2007. VBA provides a complete integrated development environment (IDE) that allows for document automation, the process of using an automated template for creating documents. The book also includes coverage of the new features of Word 2007 including Content Controls, programming the Ribbon, and more.

---

Title: Excel 2007 Bible
Author: John Walkenbach
Pages: 912
Description: Whether you’re already a power user or just starting, find out how to get the most out of this major new release of Excel from expert instructor and Microsoft MVP, “Mr. Spreadsheet,” John Walkenbach. Each of the book’s seven parts thoroughly focuses on key elements, so no matter what your level of expertise, you’ll find what you need in the hundreds of examples, techniques, and tips in this comprehensive resource.

This book is a single reference that’s indispensable for Excel beginners, intermediate users, power users, and would-be power users everywhere.It is fully updated for the new release, this latest edition provides comprehensive, soup-to-nuts coverage, delivering over 900 pages of Excel tips, tricks, and techniques readers won’t find anywhere else. John Walkenbach is one of the world’s leading authorities on Excel. Thoroughly updated to cover the revamped Excel interface, new file formats, enhanced interactivity with other Office applications, and upgraded collaboration features. It includes a valuable CD-ROM with templates and worksheets from the book.

---

Title: Office 2007 All-in-One Desk Reference For Dummies
Author: Peter Weverka
Pages: 816
Description: It gives you a set of tools for managing documents and data, analyzing numbers, organizing your e-mail and schedule, building presentations, and designing publications – and this book covers them all! Get the scoop on all the Office programs, find out how to supercharge your productivity by using them together, and much more.

---

Title: Excel 2007 VBA Programming For Dummies
Author: John Walkenbach
Pages: 368
Description: Make Excel 2007 work for you! This clear, nonintimidating guide shows you how to use VBA to create Excel apps that look and work the way you want. Packed with plenty of sample programs, it explains how to work with range objects, control program flow, develop custom dialog boxes, create custom toolbars and menus, and much more.

---

Title: iWork ’09: The Missing Manual
Author: Josh Clark
Pages: 850
Description: With iWork ’09, Apple’s productivity applications have come of age. Unfortunately, their user guides are stuck in infancy. That’s where iWork ’09: The Missing Manual comes in. This book quickly guides you through everything you need to know about the Pages word-processor, the Numbers spreadsheet, and the Keynote presentation program that Al Gore and Steve Jobs made famous.

Friendly and entertaining, iWork ’09: The Missing Manual gives you crystal-clear and jargon-free explanations of iWork’s capabilities, its advantages over similar programs — and its limitations. You’ll see these programs through an objective lens that shows you which features work well and which don’t.

---