Sunday, May 31, 2009

Macro for creating Venn Diagrams in Excel

I think most of you know what a venn diagram is. For the people who doesn't know what venn diagrams is, by definition, venn diagram (also known as set diagrams) shows a logical relation between a (definitely finite not infinite) collection of sets in the form of overlapping circles or closed curves. In simple terms, one circle in a two-set venn diagram may represent SET A, the other circle may represent SET B and the overlapping area will represent the set of all similarties of SET A and SET B. Ok, now how do you draw these venn diagrams with our favourite excel macros. Let me show you that. Check the below code to draw simple 2-circle and 3-circle venn diagrams. The code shows
1. Different ways of adding shapes.
2. Adding backcolor, forecolor and  two color gradient to shapes.
3. Adjusting position of shapes relative to the other shapes.
4. Adding text to shapes and making adjustments to the text.
5. Representation of ovelap with Transparency.

Code Block

'Macro to draw Venn Diagrams in Excel.
Sub DrawVennDiagrams()
'
' Comment the code accordingly 2 draw one, two or three circles and generate venn diagrams.
'

Sheets.Add
Cells.Select
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With

'First way of adding shapes to the sheet and assigning colors
ActiveSheet.Shapes.AddShape(msoShapeOval, 100#, 40, 240, 240).Select
Selection.ShapeRange.Line.ForeColor.SchemeColor = 24
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 0, 0)
'Making Transparency to 1# is very important to show the overlap in the venn diagram
Selection.ShapeRange.Fill.Transparency = 1#
Selection.Characters.Text = "Marketing"

'Another way of adding shapes to the sheet and assigning colors, two color gradients.
With ActiveSheet.Shapes.AddShape(msoShapeOval, 100#, 40, 240, 240).Fill
.ForeColor.RGB = RGB(128, 0, 0)
.BackColor.RGB = RGB(170, 170, 170)
.TwoColorGradient msoGradientHorizontal, 1
End With
ActiveSheet.Shapes("Oval 2").Select
'Adjust the position of circles relative to the other circles
Selection.ShapeRange.IncrementLeft 160
Selection.ShapeRange.Fill.Transparency = 1#
Selection.Characters.Text = "Sales"

ActiveSheet.Shapes.AddShape(msoShapeOval, 100#, 160, 240, 240).Select
Selection.ShapeRange.IncrementLeft 80
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Line.ForeColor.SchemeColor = 28
Selection.ShapeRange.Line.BackColor.RGB = RGB(2, 2, 2)
Selection.ShapeRange.Fill.Transparency = 1#
Selection.Characters.Text = "Support"
'Below code shows how to make adjustments to the text inside the venn diagram.
With Selection.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 32
End With

ActiveWorkbook.Save
End Sub

Hope you had fun drawing the venn diagrams. Let me know your comments.


Locations of Excel 2003 commands in Excel 2007 : Forms

Excel 2003 Location Excel 2007 Location
Label Developer | Controls | Insert | Label (Form Control)
Edit Box Developer | Controls | Insert | Edit Box (Form Control)
Group Box Developer | Controls | Insert | Group Box (Form Control)
Button Developer | Controls | Insert | Button (Form Control)
Check Box Developer | Controls | Insert | Check Box (Form Control)
Option Button Developer | Controls | Insert | Option Button (Form Control)
List Box Developer | Controls | Insert | List Box (Form Control)
Combo Box Developer | Controls | Insert | Combo Box(Form Control)
Combination List-Edit Developer | Controls | Insert | Combination List-Edit (Form Control)
Combination Drop-Down Edit Developer | Controls | Insert | Combination Drop-Down Edit (Form Control)
Scroll Bar Developer | Controls | Insert | Scroll Bar (Form Control)
Spinner Developer | Controls | Insert | Spinner (Form Control)
Control Properties Developer | Controls | Properties
Edit Code Developer | Controls | View Code
Toggle Grid View | Show/Hide | Gridlines
Toggle Grid Page Layout | Sheet Options | Gridlines | View
Run Dialog Developer | Controls | Run Dialog

Source: Microsoft (http://www.microsoft.com/)


Saturday, May 30, 2009

How to Clear the contents of a cell, range, worksheet and workbook

This is one of the most frequently asked questions in the forums and also one of the most frequently used concept. In real time, according to the requirements many a times you will face a scenario where you need to clear the contents in the sheets without deleting the sheets by itself. Depending on the scope, requirement might vary a lot, like, just to clear contents of a cell or a particular range or a particular sheet or the whole workbook. Also, there might be some specific requirements just to clear the comments or formats not the cell contents (values or formulas). So, keeping that in mind I have written the below code to give a jump start and the code is flexible to tweak according to your requirements. 

Code Block

Sub ClearWorkbookContents()

'Loops through all the sheets of the workbook and clears the contents accordingly
For Each WrkSht In Sheets

'Clears the contents of a cell in the worksheet
WrkSht.Cells(1, 1).Clear

'Clears the contents of a range in the worksheet
WrkSht.Range("A2:B5").Cells.Clear

'Comment this if you don't want to clear the whole sheet. Clears the contents of the whole worksheet
WrkSht.Cells.Clear

'If you want to clear only some particular items use the following accordingly
'Cells.ClearComments 'Clears only comments
'Cells.ClearContents 'Clears only values and formulas
'Cells.ClearFormats
'Cells.ClearNotes
'Cells.ClearOutline

Next WrkSht

End Sub

Apart from the above if you want to clear the contents of mulitple ranges (say they are named, exists in the same worksheet / across different worksheets and the names are SalesRange and HealthRange) then use the below code

Range("SalesRange, HealthRange").Clear

Hope this helps in solving some of your requirements with ease. Let  me know your comments.

Locations of Excel 2003 commands in Excel 2007 : Chart (Toolbar)

Excel 2003 Location Excel 2007 Location
Chart Objects Chart Tools | Layout
Format Selected Object Chart Tools | Format | Current Selection | Format Selection
Chart Type Chart Tools | Design | Type | Change Chart Type
Legend Chart Tools | Layout | Labels | Legend
Data Table Chart Tools | Layout | Labels | Data Table
Series in Rows Chart Tools | Design | Data | Switch Row/Column
Series in Columns Chart Tools | Design | Data | Switch Row/Column
Angle Clockwise Home | Alignment | Orientation | Angle Clockwise
Angle Counterclockwise Home | Alignment | Orientation | Angle Counterclockwise

Source: Microsoft (http://www.microsoft.com/)


Friday, May 29, 2009

Which is Good? Refer Excel Sheets by Index Number or Name

According to Microsoft with respect to sheets, index number is a sequential number assigned to a sheet based on the position of the sheet tab (counting from left) among the sheets of same type. There is a reason for highlight the words "same type" because index number varies depending on the type of property you use for identifying sheets as explained in the article Difference between WorkSheets and Sheets

So, if you have Dialog1, Chart1, Macro1, Sheet1 in a sequence in workbook then "Worksheets(1).Activate" , "Charts(1). Activate" and "Sheets(1).Activate" will activate different sheets though their index number is same since the sheets are of different type. It is confusing right. This is the main reason why Sheet Name makes more sense to use for refering a Sheet rather than index number. Also, if you move, add or delete sheets from the workbook index order changes and this creates more mess while coding if there are lot of sheets. Specially if you maintaining big workbooks with lots of macros involving index numbers, this will be a nightmare.

Though Sheets("Sheet1"). Activate is better than index numbers it is still not the best because if you have more numbers of sheets it will be very difficult for you to remember the type of content in each sheet. So, the best is to rename the default sheet name to some name that represents the content of the sheet and use it in the code.  To present this as an example:

Sheets(1).Activate -> Creates Confusion
Sheets("Sheet1").Activate -> Better than the above but not the best
Sheets("Sales").Activate -> Best of all ("Sales" represent a functional name for the content in Sheet1)

Let me know your comments.


Locations of Excel 2003 commands in Excel 2007 : Reviewing

Excel 2003 Location Excel 2007 Location
New Comment Review | Comments | New Comment
Previous Comment Review | Comments | Previous
Next Comment Review | Comments | Next
Show/Hide Comments Review | Comments | Show/Hide Comment
Hide All Comments Review | Comments | Hide All Comments
Show All Comments Review | Comments | Show All Comments
Delete Comment Review | Comments | Delete
Insert Ink Annotations Review | Ink | Start Inking
Hide Ink Annotations Review | Comments | Show Ink
Delete All Ink Annotations Ink Tools | Pens | Select | Delete All Ink
Create Microsoft Office Outlook Task Office Button | Excel Options | Customize | All Commands | Create Microsoft Office Outlook Task
Update File Office Button | Excel Options | Customize | All Commands | Update File
Mail Recipient (as Attachment) Office Button | Send | E-mail
Reply with Changes Office Button | Excel Options | Customize | All Commands | Reply with Changes
End Review Office Button | Excel Options | Customize | All Commands | End Review

Source: Microsoft (http://www.microsoft.com/)


Thursday, May 28, 2009

Difference between Worksheets and Sheets Property in Excel

If you are working extensively with all the type of sheets (Worksheets, Charts, Modules / Macro, Dialog sheets) and playing with macros or VBA code then it is very important that you know the difference between Worksheets and Sheets Properties in Excel. 

Primary difference between these two is Worksheets property identifies only the type "Worksheets" in excel but Sheets is more general and identifies all the types of sheets (Worksheets, Charts, Modules / Macro, Dialog sheets).  

To test this and understand the concept clearly, just open a new workbook (default it displays Sheet1, Sheet2 and Sheet3) . Insert a dialog sheet Dialog1, Chart Sheet Chart1 and Macro Sheet Macro1 in the workbook (order of the sheets now is Dialog1, Chart1, Macro1, Sheet1, Sheet2 and Sheet3). Now go to the VB Editor and play around with the below code commenting the necessary lines.

Sub TestSheetsWorksheets()

Worksheets(1).Activate 'Activates Sheet1
Worksheets(2).Activate 'Activates Sheet2
Worksheets(3).Activate 'Activates Sheet3
Worksheets(4).Activate 'Throws error "Run Time Error '9': Subscript out of range"
Worksheets(5).Activate 'Throws error "Run Time Error '9': Subscript out of range"
Worksheets(6).Activate 'Throws error "Run Time Error '9': Subscript out of range"

Sheets(1).Activate 'Activates Dialog1
Sheets(2).Activate 'Activates Chart1
Sheets(3).Activate 'Activates Macro1
Sheets(4).Activate 'Activates Sheet1
Sheets(5).Activate 'Activates Sheet2
Sheets(6).Activate 'Activates Sheet3
 
End Sub

So, next time when you see "Run Time Error '9': Subscript out of range" while you are using Worksheets in the code just take a look at the type of the sheet apart from the number of sheets. You might fix the problem in a second.

Hope you had fun playing around with Sheets and WorkSheets. Let me know your comments.

Header and Footer Properties useful for print macros

Format code Description
&L Left aligns the characters that follow.
&C Centers the characters that follow.
&R Right aligns the characters that follow.
&E Turns double-underline printing on or off.
&X Turns superscript printing on or off.
&Y Turns subscript printing on or off.
&B Turns bold printing on or off.
&I Turns italic printing on or off.
&U Turns underline printing on or off.
&S Turns strikethrough printing on or off.
&"fontname" Prints the characters that follow in the specified font. Be sure to include the double quotation marks.
&nn Prints the characters that follow in the specified font size. Use a two-digit number to specify a size in points.
&colorPrints the characters in the specified color. User supplies a hexidecimal color value.

VBA code Description
&D Prints the current date.
&T Prints the current time.
&F Prints the name of the document.
&A Prints the name of the workbook tab.
&P Prints the page number.
&P+number Prints the page number plus the specified number.
&P-number Prints the page number minus the specified number.
&& Prints a single ampersand.
&N Prints the total number of pages in the document.
&Z Prints the file path.
&G Inserts an image.

Source: http://msdn.microsoft.com/

Macro to Save Excel Sheets or Content as Web pages (HTML)

This might not be a regular requirement to most of the people but say you have a desire to prepare a static HTML presentation of all your excel tables, charts, pivot tables etc., or publish them as web pages. How do you do this? One way to do this is save the workbook manually as a web page checking the various options related to ranges, sheets etc.,. This might be a tedious process if you have many worksheets. So, better way to go is to write a macro for this. In Microsoft Excel, you can save a workbook, worksheet, chart, range, query table, PivotTable report, print area, or AutoFilter range to a Web page or HTML page. Also, you can edit HTML files directly in Excel. Check the code below that explains a part of what Microsoft supports and the rest is for you to explore. Code explains the below
1. Save a Range of data in HTML file.
2. Save a Worksheet in HTML file.

Code Block

Sub SaveRangeAsHTML()
Dim myRange1 As Range
Set myRange1 = Range("A1:H5")
Set myRange2 = Range("A6:H10")
myRange1.Value = 2
myRange2.Value = 5
'Save the Range myRange1 as myRange.htm
ActiveWorkbook.PublishObjects.Add(xlSourceRange, "C:\MyRange.htm", Sheet:=myRange1.Parent.Name, _
Source:=myRange1.Address, HtmlType:=xlHtmlStatic).Publish
'Save the worksheet with Ranges myRange1 and myRange2 as MyWorksheet.htm
'Here you can loop through the all the sheets in the workbook to get one webpage for
'each sheet. Check the sample in "Generate Excel Sheet List" for looping
ActiveWorkbook.PublishObjects.Add(xlSourceSheet, "C:\MyWorksheet.htm", Sheet:="Sheet1", HtmlType:=xlHtmlStatic).Publish
End Sub

In the above code play with the sourcetype (xlSourceRange, xlSourceSheet, xlSourceChart etc.,) to save different objects of excel in web pages.

Hope you had fun playing with the excel sheets and html pages. Let me know your comments.

Locations of Excel 2003 commands in Excel 2007 : PivotTable

Excel 2003 Location Excel 2007 Location
PivotTable | Format Report Pivot Table Tools | Design| PivotTable Styles
PivotTable | PivotChart PivotTable Tools | Options | Tools | PivotChart
PivotTable | PivotTable Wizard Office Button | Excel Options | Customize | All Commands | PivotTable and PivotChart Wizard
PivotTable | Refresh Data PivotChart Tools | Analyze | Data | Refresh
PivotTable | Refresh Data PivotTable Tools | Options | Data | Refresh
PivotTable | Offline OLAP PivotTable Tools | Options | Tools | OLAP tools | Offline OLAP
PivotTable | Hide Dimension Row/Column Context Menu | Delete
PivotTable | Select | Label PivotTable Tools | Options | Actions | Select | Label
PivotTable | Select | Data PivotTable Tools | Options | Actions | Select | Data
PivotTable | Select | Label and Data PivotTable Tools | Options | Actions | Select | Label and Data
PivotTable | Select | Entire Table PivotTable Tools | Options | Actions | Select | Entire Table
PivotTable | Select | Enable Selection PivotTable Tools | Options | Actions | Select | Enable Selection
PivotTable | Group and Show Detail | Hide Detail PivotTable Tools | Options | Active Field | Collapse Entire Field
PivotTable | Group and Show Detail | Show Detail PivotTable Tools | Options | Active Field | Expand Entire Field
PivotTable | Group and Show Detail | Group PivotTable Tools | Options | Group | Group Selection
PivotTable | Group and Show Detail | Ungroup PivotTable Tools | Options | Group | Ungroup
PivotTable | Formulas | Calculated Field PivotTable Tools | Options | Tools | Formulas | Calculated Field
PivotTable | Formulas | Calculated Item PivotTable Tools | Options | Tools | Formulas | Calculated Item
PivotTable | Formulas | Solve Order PivotTable Tools | Options | Tools | Formulas | Solve Order
PivotTable | Formulas | List Formulas PivotTable Tools | Options | Tools | Formulas | List Formulas
PivotTable | Order | Move to Beginning PivotTable Cell Right Click Menu | Move | Move to Beginning
PivotTable | Order | Move Up PivotTable Cell Right Click Menu | Move | Move Up
PivotTable | Order | Move Down PivotTable Cell Right Click Menu | Move | Move Down
PivotTable | Order | Move to End PivotTable Cell Right Click Menu | Move | Move to End
PivotTable | Order | Move to Column PivotTable Cell Right Click Menu | Move | Move to Columns
PivotTable | Field Settings PivotTable Tools | Options | Active Field | Field Settings
PivotTable | Subtotals PivotTable Tools | Design | Layout | Subtotals
PivotTable | Sort and Top 10 PivotTable Tools | Options |Sort
PivotTable | Property Fields PivotTable Tools | Options | Tools | OLAP tools | Property Fields
PivotTable | Table Options PivotTable Tools | Options | PivotTable | Options
PivotTable | Show Pages PivotTable Tools | Options | PivotTable | Options | Show Report Filter Pages
PivotChart | Field Settings PivotTable Tools | Options | Active Field | Field Settings
PivotChart | Options PivotTable Tools | Options | PivotTable | Options
PivotChart | Refresh Data PivotChart Tools | Analyze | Data | Refresh
PivotChart | Formulas | Calculated Field PivotTable Tools | Options | Tools | Formulas | Calculated Field
PivotChart | Formulas | Calculated Item PivotTable Tools | Options | Tools | Formulas | Calculated Item
PivotChart | Formulas | Solve Order PivotTable Tools | Options | Tools | Formulas | Solve Order
PivotChart | Formulas | List Formulas PivotTable Tools | Options | Tools | Formulas | List Formulas
PivotChart | Remove Field Click the PivotChart, and then in the PivotTable Field List, clear any field checkbox.
Format Report Pivot Table | Design | PivotTable Styles
Chart Wizard Office Button | Excel Options | Customize | All Commands | PivotTable and PivotChart Wizard
Hide Detail PivotTable Tools | Options | Active Field | Collapse Entire Field
Show Detail PivotTable Tools | Options | Active Field | Expand Entire Field
Refresh Data PivotTable Tools | Options | Data | Refresh
Field Settings PivotTable Tools | Options | Active Field | Field Settings
Show Field List PivotTable Tools | Options | Show/Hide | Field List
Ungroup PivotTable Tools | Options | Group | Ungroup
Group PivotTable Tools | Options | Group | Group Selection
PivotTable and PivotChart Report Office Button | Excel Options | Customize | All Commands | PivotTable and PivotChart Wizard
Show Pages PivotTable Tools | Options | PivotTable | Options | Show Report Filter Pages
Refresh All PivotTable Tools | Options | Data | Refresh | Refresh All
Generate GetPivotData PivotTable Tools | Options | PivotTable | Options | Generate GetPivotData
Property Fields PivotTable Tools | Options | Tools | OLAP tools | Property Fields
Sort and Top 10 PivotTable Tools | Options |Sort
Offline OLAP PivotTable Tools | Options | Tools | OLAP tools | Offline OLAP

Source: Microsoft (http://www.microsoft.com/)

Wednesday, May 27, 2009

Locations of Excel 2003 commands in Excel 2007 : Formatting

Excel 2003 Location Excel 2007 Location
Font Home | Font | Font
Font Size Home | Font | Font Size
Bold Home | Font | Bold
Italic Home | Font | Italic
Underline Home | Font | Underline | Underline
Align Left Home | Alignment | Align Left
Center Home | Alignment | Center
Align Right Home | Alignment | Align Right
Merge and Center Home | Alignment | Merge & Center
Merge and Center Home | Alignment | Merge | Merge & Center
Currency Style Home | Number | Accounting Number Format
Percent Style Home | Number | Percent Style
Comma Style Home | Number | Comma Style
Increase Decimal Home | Number | Increase Decimal
Decrease Decimal Home | Number | Decrease Decimal
Decrease Indent Home | Alignment | Decrease Indent
Increase Indent Home | Alignment | Increase Indent
Borders Home | Font | Borders
Fill Color Home | Font | Fill Color
Font Color Home | Font | Font Color
Chart | Chart Type Chart Tools | Design | Type | Change Chart Type
Chart | Source Data Chart Tools | Design | Data | Select Data
Chart | Source Data PivotChart Tools | Design | Data | Select Data
Chart | Chart Options Chart | Layout
Chart | Location Chart Tools | Design | Location | Move Chart
Chart | Location PivotChart Tools | Design | Location | Move Chart
Chart | Add Data Chart Tools | Design | Data | Edit Data Source
Chart | Add Trendline Chart Tools | Layout | Analysis | Trendline
Chart | 3-D View Chart Tools | Layout | Background | 3-D View
AutoFormat Home | Styles | Format as Table
Cells Home | Cells | Format | Format Cells
Increase Font Size Home | Font | Increase Font Size
Decrease Font Size Home | Font | Decrease Font Size
Text Direction Home | Alignment | Orientation
*Text Direction | Sheet Left-to-Right Home | Alignment | Left-to-Right | Left-to-Right
*Text Direction | Sheet Right-to-Left Home | Alignment | Left-to-Right | Right-to-Left
*Text Direction | Context Home | Alignment | Left-to-Right | Context
*Sheet Right-to-Left Page Layout | Sheet Options | Right-to-Left Document

Source: Microsoft (http://www.microsoft.com/)

Tuesday, May 26, 2009

How to copy multiple workbooks data into a single Workbook using an Excel Macro

Working with multiple files is a very common scenario. Say, you have a process that generates multiple excel files in a folder populating one worksheet for each file and at the end of the day you job is to consolidate all these worksheets into one big master file. If the number of files generated is one or 2 manually copy pasting will do the job. But if the files are like 50 or 100, doing such monotonous job is painful. Only solution is to write a simple macro that does the job for you. Check the below code to make your job easy. 

This code covers the following:
1. Lists all the excel files in the given folder (Make sure to see cell A1 is active in the active workbook from where you are running the code).
2. Creates a new summary workbook. 
3. Saves all the worksheets from the above file list into the summary workbook. 

Code Block

Sub SummaryOfSheets()

Dim new_workbook_name As String
Dim cur_workbook_name As Workbook
Dim full_path As String
Dim fName As String
Dim rowcount As Integer

Application.ScreenUpdating = False

'Make sure active cell is A1
'Get the list of all files in the folder
F = Dir("C:\Test\*.XLS")
Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop

'Creates a new excel file for the summary worksheet
new_workbook_name = "SummaryWorksheet.xls"
full_path = ActiveWorkbook.Path & "\" & new_workbook_name
Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
full_path, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'Get data from each excel file. Assuming data is there in only sheet1 of each excel file.
rowcount = 1

Do While Sheets("Sheet1").Range("A" & rowcount) <> ""

Set cur_workbook_name = Workbooks.Open("C:\Test\" & Sheets("Sheet1").Range("A" & rowcount).Value)
fName = Sheets("Sheet1").Range("A" & rowcount).Value
Workbooks(fName).Sheets(1).Copy Before:=Workbooks(new_workbook_name).Sheets(1)
cur_workbook_name.Close
rowcount = rowcount + 1

Loop

Application.ScreenUpdating = True
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

Hope your are having fun leaving the boring part to Excel. Let me know your comments.

How to Open URL list from Excel using Macro

Opening of an URL from Excel and performing some actions in the browser might be a regular activity of some excel users. This article helps in automating a part of this. Say, you have a list (10 or more) of URL links in column A and your requirements are as follows
1. Open up each url link and perform some tasks (code provides a place holder for performing the tasks. Tweak accordingly).
2. Use the same browser window to open the URLs.
3. Close the browser window once all the work is done.

Check the below code to get the desired result.

Code Block

Public Sub OpenURLList()

Dim rowcount As Integer
Dim urlToOpen As String

Set ie = CreateObject("InternetExplorer.Application")
'Show browser
ie.Visible = True
rowcount = 1
Do While Sheets("Sheet1").Range("A" & rowcount) <> ""

urlToOpen = Sheets("Sheet1").Range("A" & rowcount).Value
'Open the links in the same browser
ie.Navigate2 urlToOpen
'Perform your events here
MsgBox urlToOpen & " is open. Check your browser"
rowcount = rowcount + 1

Loop

'Close Browser
ie.Quit

End Sub

Hope you had fun playing with the URLs and Excel. Let me know your comments.

Locations of Excel 2003 commands in Excel 2007 : Standard

Excel 2003 Location Excel 2007 Location
New Office Button | New | Blank Worksheet
Open Office Button | Open
Save Quick Access Toolbar | Save
Save Office Button | Save
Permission Office Button | Prepare | Restrict Permission | Restrict Permission As
Email Office Button | Send | Email
Print Office Button | Print | Quick Print
Print Preview Office Button | Print | Print Preview
Spelling Review | Proofing | Spelling
Research Review | Proofing | Research
Cut Home | Clipboard | Cut
Copy Home | Clipboard | Copy
Paste Home | Clipboard | Paste
Format Painter Home | Clipboard | Format Painter
Undo Quick Access Toolbar | Undo
Redo Quick Access Toolbar | Redo
Ink Annotations Review | Ink | Start Inking
Hyperlink Insert | Links | Hyperlink
AutoSum Home | Editing | AutoSum
AutoSum Formulas | Function Library | AutoSum
Sort Ascending Data | Sort & Filter | Sort A to Z
Sort Descending Data | Sort & Filter | Sort Z to A
Chart Wizard Insert | Charts
Drawing These commands are available in the Drawing Tools tabs when you insert or select a shape.
Zoom "View | Zoom | Zoom
Status Bar | Zoom Slider"
Microsoft Excel Help Upper Ribbon | Help
PivotTable and PivotChart Report "Insert | Tables | PivotTable | PivotTable
Insert | Tables | PivotTable | PivotChart"
Comment Review | Comments | New Comment
AutoFilter "Home | Editing | Sort & Filter | Filter
Data | Sort & Filter | Filter"

Source: Microsoft (http://www.microsoft.com/)

Monday, May 25, 2009

How to get Google Search Results count in Excel?

Everyone who browses internet might have visited "http://www.google.com" atleast once in lifetime. Atleast as of today that is the best search engine available. I know many of the excel users will be curious to play with google search results and excel. So, I will just show you one requirement today and will let you explore the rest. Say, you have a list of values in column A of excel and you want to display the search results count of that list in the corresponding column B. How do you do this? You can definitely go to Google search for the values, check the number of results and paste the result in the adjacent column of the search values. But this is a tedious process if the list is long. So, writing a macro to automate this manual process is the best solution. Check the code below for achieving the desired result.

Code Block

Public Sub ExcelGoogleSearch()

Dim searchWords As String

With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
searchWords = .Range("A" & RowCount).Value

' Get keywords and validate by adding + for spaces between
searchWords = Replace$(searchWords, " ", "+")

' Obtain the source code for the Google-searchterm webpage
search_url = "http://www.google.com/search?hl=en&q=" & searchWords & "&meta="""
Set search_http = CreateObject("MSXML2.XMLHTTP")
search_http.Open "GET", search_url, False
search_http.send
results_var = search_http.responsetext
Set search_http = Nothing

' Find the number of results and post to sheet
pos_1 = InStr(1, results_var, "b> of", vbTextCompare)
pos_2 = InStr(3 + pos_1, results_var, ">", vbTextCompare)
pos_3 = InStr(pos_2, results_var, "<", vbTextCompare)
NumberofResults = Mid(results_var, 1 + pos_2, (-1 + pos_3 - pos_2))

Range("B" & RowCount) = NumberofResults
RowCount = RowCount + 1
Loop
End With

End Sub

Hope you had fun in getting the Google search results count in excel. Let me know if you have any comments.

Macro to Add shapes to the Worksheet and make Adjustments

'Add a shape to the Worksheet and make Adjustments to the shape
Sub AddDiamondShape()

Dim objDoc As Worksheet
Set objDoc = Worksheets(1)

'Below are the 3 ways to add a diamond shape to the Worksheet
'with different coordinates at 3 different places on the Sheet.

objDoc.Shapes.AddShape msoShapeDiamond, 1, 2, 50, 50
objDoc.Shapes.AddShape 4, 1, 60, 200, 50
objDoc.Shapes.AddShape Type:=msoShapeDiamond, Left:=1, Top:=120, Width:=50, Height:=200

'Make the adjustments to the above 4 items (Left, Top, Width, Height) to play with the shapes

'Alternatively use the below code to make adjustments leaving the above as default

'With objShape.Adjustments
'    .Item(1) = 1    'adjusts the Left Coordinate
'    .Item(2) = 2    'adjusts the Top Coordinate
'    .Item(3) = 50   'adjusts the Width of the Diamond
'    .Item(4) = 50   'adjusts the Height of the Diamond
'End With

End Sub

Sunday, May 24, 2009

Excel 2007 Screen Explanation


















Source: Purdue University

Saturday, May 23, 2009

How to Link External Data from Excel to a Word Document

You are preparing a design document in Microsoft word but you have all you tables, charts etc., in Excel. Now your requirement is to have all those tables and charts in Word document and work on them. But you hate word and when it comes to tables / charts you are very comfortable with Excel than Word. So, what are you going to do? There is a way to make your work easy, if you like Excel a lot and want to work only in Excel but display the data dynamically in word. Just follow the below steps to achieve the result:

1. Open both the Word document and the Excel workbook that contains the data you want to link to Word.
2. Go to Excel, and select the entire worksheet, a range of cells / tables, or the chart you want.
3. Click Edit -> Copy (or) use shortcut CTRL + C.
4. Now switch to the Word document, and then click where you want to insert the data.
5. On the Edit menu, click Paste Special (In Word 2007, Home -> Paste -> Paste  Special).
6. Choose Paste Link. In the As box, click Microsoft Office Excel Worksheet Object.
7. Now see the magic. If you change the Excel document, the Word document will reflect that
change.

Hopefully the above trick made you work easy and more fun. Let me know your comments.

Excel 2003 Screen Explanation
















Source : http://www.customguide.com/

Friday, May 22, 2009

Macro to hyperlink excel worksheets

Yesterday in my article "Generate List of Worksheet Names" I have shown you how to generate the list of worksheets and mentioned that maintenance point of view it will be very good to have such a list. But what are going to do once you get that list. How will you go and see the worksheet that you want with ease. Yep, you started thinking in right direction, this can be done by hyperlinks. Now comes the problem, say if you have more than 100 worksheets, I don't think you want sit and link each of those worksheets. Simple solution is to write a macro to do this job for you. Check the below code to achieve the desired result.

Code Block 1

Sub ListSheetNamesWithLink()

Dim i As Integer

For i = 1 To Sheets.Count

Range("A" & i) = Sheets(i).Name
Range("A" & i).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Sheets(i).Name & "!A1"

Next

End Sub

Okay, I can see a sigh of great relief in you now. Hope you had fun getting control over all your worksheets now. Let me know if you have any comments.

List of Shape Types and Shape Constants in Excel

Shape Type Shape Constant
’รจ” ’l
msoShapeMixed -2
msoShapeRectangle 1
msoShapeParallelogram 2
msoShapeTrapezoid 3
msoShapeDiamond 4
msoShapeRoundedRectangle 5
msoShapeOctagon 6
msoShapeIsoscelesTriangle 7
msoShapeRightTriangle 8
msoShapeOval 9
msoShapeHexagon 10
msoShapeCross 11
msoShapeRegularPentagon 12
msoShapeCan 13
msoShapeCube 14
msoShapeBevel 15
msoShapeFoldedCorner 16
msoShapeSmileyFace 17
msoShapeDonut 18
msoShapeNoSymbol 19
msoShapeBlockArc 20
msoShapeHeart 21
msoShapeLightningBolt 22
msoShapeSun 23
msoShapeMoon 24
msoShapeArc 25
msoShapeDoubleBracket 26
msoShapeDoubleBrace 27
msoShapePlaque 28
msoShapeLeftBracket 29
msoShapeRightBracket 30
msoShapeLeftBrace 31
msoShapeRightBrace 32
msoShapeRightArrow 33
msoShapeLeftArrow 34
msoShapeUpArrow 35
msoShapeDownArrow 36
msoShapeLeftRightArrow 37
msoShapeUpDownArrow 38
msoShapeQuadArrow 39
msoShapeLeftRightUpArrow 40
msoShapeBentArrow 41
msoShapeUTurnArrow 42
msoShapeLeftUpArrow 43
msoShapeBentUpArrow 44
msoShapeCurvedRightArrow 45
msoShapeCurvedLeftArrow 46
msoShapeCurvedUpArrow 47
msoShapeCurvedDownArrow 48
msoShapeStripedRightArrow 49
msoShapeNotchedRightArrow 50
msoShapePentagon 51
msoShapeChevron 52
msoShapeRightArrowCallout 53
msoShapeLeftArrowCallout 54
msoShapeUpArrowCallout 55
msoShapeDownArrowCallout 56
msoShapeLeftRightArrowCallout 57
msoShapeUpDownArrowCallout 58
msoShapeQuadArrowCallout 59
msoShapeCircularArrow 60
msoShapeFlowchartProcess 61
msoShapeFlowchartAlternateProcess 62
msoShapeFlowchartDecision 63
msoShapeFlowchartData 64
msoShapeFlowchartPredefinedProcess 65
msoShapeFlowchartInternalStorage 66
msoShapeFlowchartDocument 67
msoShapeFlowchartMultidocument 68
msoShapeFlowchartTerminator 69
msoShapeFlowchartPreparation 70
msoShapeFlowchartManualInput 71
msoShapeFlowchartManualOperation 72
msoShapeFlowchartConnector 73
msoShapeFlowchartOffpageConnector 74
msoShapeFlowchartCard 75
msoShapeFlowchartPunchedTape 76
msoShapeFlowchartSummingJunction 77
msoShapeFlowchartOr 78
msoShapeFlowchartCollate 79
msoShapeFlowchartSort 80
msoShapeFlowchartExtract 81
msoShapeFlowchartMerge 82
msoShapeFlowchartStoredData 83
msoShapeFlowchartDelay 84
msoShapeFlowchartSequentialAccessStorage 85
msoShapeFlowchartMagneticDisk 86
msoShapeFlowchartDirectAccessStorage 87
msoShapeFlowchartDisplay 88
msoShapeExplosion1 89
msoShapeExplosion2 90
msoShape4pointStar 91
msoShape5pointStar 92
msoShape8pointStar 93
msoShape16pointStar 94
msoShape24pointStar 95
msoShape32pointStar 96
msoShapeUpRibbon 97
msoShapeDownRibbon 98
msoShapeCurvedUpRibbon 99
msoShapeCurvedDownRibbon 100
msoShapeVerticalScroll 101
msoShapeHorizontalScroll 102
msoShapeWave 103
msoShapeDoubleWave 104
msoShapeRectangularCallout 105
msoShapeRoundedRectangularCallout 106
msoShapeOvalCallout 107
msoShapeCloudCallout 108
msoShapeLineCallout1 109
msoShapeLineCallout2 110
msoShapeLineCallout3 111
msoShapeLineCallout4 112
msoShapeLineCallout1AccentBar 113
msoShapeLineCallout2AccentBar 114
msoShapeLineCallout3AccentBar 115
msoShapeLineCallout4AccentBar 116
msoShapeLineCallout1NoBorder 117
msoShapeLineCallout2NoBorder 118
msoShapeLineCallout3NoBorder 119
msoShapeLineCallout4NoBorder 120
msoShapeLineCallout1BorderandAccentBar 121
msoShapeLineCallout2BorderandAccentBar 122
msoShapeLineCallout3BorderandAccentBar 123
msoShapeLineCallout4BorderandAccentBar 124
msoShapeActionButtonCustom 125
msoShapeActionButtonHome 126
msoShapeActionButtonHelp 127
msoShapeActionButtonInformation 128
msoShapeActionButtonBackorPrevious 129
msoShapeActionButtonForwardorNext 130
msoShapeActionButtonBeginning 131
msoShapeActionButtonEnd 132
msoShapeActionButtonReturn 133
msoShapeActionButtonDocument 134
msoShapeActionButtonSound 135
msoShapeActionButtonMovie 136
msoShapeBalloon 137
msoShapeNotPrimitive 138

Thursday, May 21, 2009

Macro to Generate a List of Worksheet Names

Say you are given a file with large number of worksheets, like more than 30 worksheets; how are you going to maintain this workbook without having the list of all the worksheets handy. Yahh, it is difficult to maintain without a list. So, let's write a small macro that lists all the worksheets names of the workbook in column A of active sheet. Check the below code to achieve the desired result.

Code Block 1

Sub ListWorkSheetNames()

Dim i As Integer

For i = 1 To Sheets.Count
Range("A" & i) = Sheets(i).Name
Next

End Sub

Let me know if you have any questions or comments.

Wednesday, May 20, 2009

Macro to Send Multiple Emails Along with Attachments in Excel

I have discussed in one of my previous articles "Copy Worksheets From Master Workbook" about generating a Results.xls from Master workbook. Let us discuss one more real time scenario today. Once the results excel is generated, next thing many users do is to send an email of those results to concerned people. This can be done manually but just to make your work more easy think of automating this process. Got any thought? Ok, let me give you the code. You can tweak this code according to your requirements. Check the code below to send multiple emails to multiple people with different subjects and different attachments to each one.

Just for writing the code let us assume that an excel worksheet is organized with email related data as shown in the below figure.

(Enlarge the figure to get a better view).

Code Block 1

'Tweak the code according to your requirements.
Sub SendMultipleEmails()

Set myOlApp = CreateObject("Outlook.Application")
Set mail = myOlApp.CreateItem(olMailItem)
Set attach = mail.Attachments

For i = 2 To Columns.SpecialCells(xlCellTypeLastCell).Count + 1
mail.To = Cells(i, 1)
mail.CC = Cells(i, 2)
mail.BCC = Cells(i, 3)
mail.Subject = Cells(i, 4)
mail.Body = Cells(i, 5)
If Cells(i, 6) <> "" Then
attach.Add "" & Cells(i, 6) & ""
End If
mail.send
Next

End Sub

Hope you had fun sending multiple emails throught excel. Let me know your comments.

Tuesday, May 19, 2009

Macro to Copy Worksheets from Master Workbook without macros

Let me explain you one more common scenario today. Say, you have big master excel that has all the macros. And, you have a requirement that once the macro is run copy the results sheets Result1, Result2 and Result3 to another workbook "Results.xls" and save it in the same directory as Master excel. Generally with these kind of requirements you will have some important point to note, like
1. Do not copy the macros of the Master excel along with the results worksheet.
2. Remove the worksheets from Master excel once a copy of results worksheet is done.

So, how do you do this? Check the below code to get the desired result.

Code Block 1

Sub CopyResultsSheets()

Dim new_workbook_name As String
Dim full_path As String
Dim cur_workbook_name As String
Dim first_sheet As String
Dim second_sheet As String
Dim third_sheet As String

Application.ScreenUpdating = False

cur_workbook_name = ActiveWorkbook.Name
new_workbook_name = "Results.xls"

full_path = ActiveWorkbook.Path & "\" & new_workbook_name

Workbooks.Add

ActiveWorkbook.SaveAs Filename:= _
full_path, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

first_sheet = "Result1"
second_sheet = "Result2"
third_sheet = "Result3"

'make sure the first sheet is the active one
Windows(cur_workbook_name).Activate
' do sheets right to left
Sheets(third_sheet).Select
Sheets(third_sheet).Copy Before:=Workbooks(new_workbook_name).Sheets(1)

'make sure the first sheet is the active one
Windows(cur_workbook_name).Activate
' do sheets right to left
Sheets(second_sheet).Select
Sheets(second_sheet).Copy Before:=Workbooks(new_workbook_name).Sheets(1)

'make sure the first sheet is the active one
Windows(cur_workbook_name).Activate
' do sheets right to left
Sheets(first_sheet).Select
Sheets(first_sheet).Copy Before:=Workbooks(new_workbook_name).Sheets(1)


' delete the default sheets from the new workbook
Application.DisplayAlerts = False
Windows(new_workbook_name).Activate
Sheets("sheet1").Delete
Sheets("sheet2").Delete
Sheets("sheet3").Delete
Application.DisplayAlerts = True
' save the new workbook
ActiveWorkbook.Save
Windows(new_workbook_name).Close

' delete the Result sheets from the current workbook
Application.DisplayAlerts = False
Windows(cur_workbook_name).Activate
Application.ScreenUpdating = True

Sheets(first_sheet).Delete
Sheets(second_sheet).Delete
Sheets(third_sheet).Delete
Application.DisplayAlerts = True
' save the master workbook
ActiveWorkbook.Save

End Sub

Note : To distinguish the Results files, just in case if you are generating them daily, add date at the end of the File. This can be done via VBA Code or by manually.

Hope you had fun playing with result worksheets. Let me know your comments.

Monday, May 18, 2009

Formula for Identifying Start and End of Similar Data (or Group) in a List

Today I am going to show you one more real time scenario with an example. Say, you have a list as shown in the below figure (Street Numbers with corresponding zip codes).


In the above list, how do you identify the start and end of all the zip codes? Just paste the formula "=IF(B2<>B1,"Start",IF(B2=B3,"","End"))" in D2 and copy paste all the way down till the last row to get the desired result as shown in the below figure.


Let me know if you have any questions or comments.

Sunday, May 17, 2009

Use Of Excel 2007 In Teaching And Laboratory Situations

The look-and-feel for Excel essentially remained the same for all the versions from Excel 97 to Excel 2004. With Excel 2007, there was a major change in the menus, the look-and-feel of worksheets, the accessing of functions and routines, and the appearances of worksheets and charts.

Essentially all the books, textbooks and manuals available for Excel 2003 are now obsolete. Their how-to-instructions do not apply to Excel 2007.

Generating charts for displays is now completely different, with respect to menus, sub-menus, results of selections, and the final appearances of the charts. It takes longer going through complex menus and dealing with single word selections that do not convey what the selection word means. This basically says that all the existing textbooks and CD’s with problem data, and publisher add-ins are now obsolete for Excel 2007.

Building charts has completely changed, and their appearances also completely different. There is an increased ability to put in chartjunk’, lighting effects, shading, 3D renderings, flashy, distracting figures, silly variations, insertion of icons, visual distractions, etc. This is what the business world wants, the ability to insert effects to obscure, bias or just to add variety to frequent presentations (See Few 2006). This can lead students to become very adept at obscuring what the data shows.

Good, effective, clean charts are difficult to build in Excel 2007. Karl Ove Hufthammer recommends Tufte 2001, Robbins 2004 and Few 2004 and 2006 as good sources for making good statistical charts.. There are other sources for building better business charts such as from juiceanalytics.

The default charts shown in the Data Analysis routines are inadequate. It is quite difficult to start with the general default chart and to create a clean, positive, simple chart showing relationships. This is a real loss for displaying laboratory and statistical results. Colors are no longer bright and positive, but gray, smudged pastels. The process of cleaning up default charts is too long and involved.

A common use of Excel was to create data plots and data reductions, and then cut-and-paste the chart into a WORD document. This no longer works well when the document is a WORD 2003 document and the cut is from EXCEL 2007. Consequently one has to use the WORD 2007 version of the file to correctly cut-and-paste.

Microsoft has essentially added a lot of complexity that makes it difficult for students to use it as a convenient analytical and display tool. Therefore any teacher/instructor has to reconsider if they will use EXCEL 2007 in their course.

EVALUATION AND REPORTING OF ERRORS AND FAULTS
It is important that errors, faults and problems in a software package be identified. Altman (2000) points out the importance here of being able to “rely” on the numerical results. He states, “Numerical accuracy can mislead social scientists that are caught by it unawares – so we must pay attention”. A user can be misled by the display of numbers.

McCullough (1998 and 1999) established the current criteria for evaluating statistical software. McCullough and Wilson (1999 and 2000) made important assessments of the capabilities of Excel and the problems encountered in using Excel for statistical calculations.

This paper attempts to consolidate most of the criticisms, reported errors and faults in statistical applications, and to evaluate their claims. The other purpose is to describe workarounds and fixes that overcome these faults and deficiencies in Excel-2000. Problems, faults and errors that still remain in Excel-2003 and Excel-2007 are also discussed. If the problem in Excel-2000 has been fixed in Excel-2003, it will be discussed. If there is no explicit indication of a change in Excel-2003, then it can be assumed that the problem still occurs in Excel-2003 and Excel-2007.

There has been a small but steady stream of articles about Excel 2007 faults and errors in the main stream professional and academic journals. An index of these has not yet been developed.

Author : David Heiser [dheiser594@gmail.com]

Saturday, May 16, 2009

Excel Errors, Faults And Fixes

EARLIER EXCEL VERSIONS
Several articles, a lot of emails on the stat lists, and many Internet sites have stated that there are errors, faults and problems with Excel. Some of these allude to errors in the earlier versions (See Note B for a list of the versions and when changes were made). RSS (1996) describes errors in versions 5 and 7 that were fixed in later versions. Many of the errors were in earlier versions, and Microsoft chose to ignore these, putting off critical fixes until the 2003 version. The Microsoft KBA series describes some of these earlier problems with Excel and describes the changes made for the later versions (see Note C for a listing of applicable KBAs).

The statistical and statistically related functions and routines in the three versions, Excel 97, Office 2000 and Office 2002 are essentially identical. They are combined under the designation "Excel-2000". Version 11.0 (designated "Excel 2003" or "Excel 2004") made some major changes. Excel-2003 functions and routines that were changed were also tested and evaluated in this paper. For Excel-2007 (Version 12.0) there were no changes to the basic version 11.0 statistical algorithms. Consequently, when the function/routine has not changed from the 2003 version, the reference will be to "Excel 2003 and 2007". When different, the references will be to the separate "Excel-2003" and "Excel-2007" entries.

COMPATABILITY
The appearance of Excel-2007 (look and feel) and the basic file designators were drastically changed for the 2007 version. The changes were to give it the look and feel of the recent Vista operating system, to provide an expanded data (size) handling capability and to change the internal structure of files from the *.xls and *.xlm file formats to a compressed, more secure format. The Excel 2007 files formats now have the extensions: .xlam, .xltm, .xlsm, .xlsb, .xml, .xltx., xlsx or .xlw, all of which are not recognized by prior versions of Excel. The .xlsx extension is the default file extension. There is also a backup file, which can be created with the .xlk extension. These extensions may not appear if you have the folder hide-extensions setting on.

If you open a *.xls file in Excel 2007 you will be in the compatibility mode. If you make any changes using any of the new Excel 2007 features, the compatibility checker intervenes when you try and save. There are many Excel 2007 features that will not work in the compatibility mode.

Excel-2007 files are not backward compatible with any earlier version of Excel, unless they are saved as *.xls files. This looses all the advanced features of 2007. Excel-2007 will work in the Windows XP environment and in the Vista environment. Excel-2003 will work in both Windows XP and in Vista. Excel 2007 charts can be inserted into Word 2003 documents (by copy and save, then back in Word 2003 by paste. But this does not always work.

Word 2007 documents saved as *.docx are not recognized by Word 2003. They have to be saved in the *.doc format to be compatible.

Author : David Heiser [dheiser594@gmail.com]

Friday, May 15, 2009

Other Software For Statistical Computations

As listed in my previous article "TEACHING EXCEL IN STATISTICS COURSES", some of the newer editions of the those textbooks no longer include Excel as a means of solving problems. They have temporary internet links to publisher websites that contain Java based computational tools that emphasize the mathematics of solution. These sites are only accessible as part of the course, and if the student wants to use them for real problems, he will have to pay for that, just like other commercial software.

Another option is to teach use of one or more of the free statistical software packages that can be downloaded from the internet. Robert Dawson has a web site (Dawson 2007) that lists some of these, and what they will do. Some may be only useful for plotting data.

The site (Dawson 2007) does not list all the available software or the other free add-ins to Excel that improve the capabilities of Excel. The software listed , is also "untested" in the sense that the results of running the StRD test data sets through the software have not been done, so that the accuracies are unknown (with the exception of R and DATAPLOT). Most of them are also limited on what statistical problems they will solve (with the exception of R). The other limitation is that the outputs are in a fixed format (number of digits, usually 3 to 5), so that rescaling of the data has to be done in all cases to be sure of even 3 digits (e.g. no allowable exponential notation).

Computations using the internet and Java software do have limits on accuracies. See Kitchen, Drachenberg and Symanzif (2003) and Kahan (2004).The Intel IA-64 instruction set (Cornea-Hagan and Norin (1999)) is the preferred means of calculation, since it retains some of the benefits of the use of the IEEE-754 Long Double format, and has accurate division.

Author : David Heiser [dheiser594@gmail.com]

Thursday, May 14, 2009

Macro to display the list of Add-Ins available and their properties in Excel

Code Block

'Display the list of all Add-Ins available in Excel and their properties.
Sub AddinProperties()
Dim i As Integer
With Worksheets("sheet1")
.Rows(1).Font.Bold = True
.Range("a1:j1").Value = _
Array("Name", "Full Name", "Title", "Installed", "Application", "CLSID", "Creator", "Parent", "Path", "ProgID")
For i = 1 To AddIns.Count
.Cells(i + 1, 1) = AddIns(i).Name
.Cells(i + 1, 2) = AddIns(i).FullName
.Cells(i + 1, 3) = AddIns(i).Title
.Cells(i + 1, 4) = AddIns(i).Installed 'Displays the installation status of the Add - in
.Cells(i + 1, 5) = AddIns(i).Application
.Cells(i + 1, 6) = AddIns(i).CLSID
.Cells(i + 1, 7) = AddIns(i).Creator
.Cells(i + 1, 8) = AddIns(i).Parent
.Cells(i + 1, 9) = AddIns(i).Path
.Cells(i + 1, 10) = AddIns(i).progID
Next
.Range("a1").CurrentRegion.Columns.AutoFit
.Range("a1").CurrentRegion.Columns.Borders.Weight = xlThin
.Range("a1").CurrentRegion.Columns.Borders.ColorIndex = 5
End With
End Sub

Result (click on the image to enlarge the image)

Use And Applications Of Excel

BUSINESS
Excel is used as a spreadsheet program in businesses and in government. It is used for all kinds of analysis, including financial analysis, economic studies and analysis, problem solving, engineering problem solutions, management problems and day-to-day business operations. Excel is generally available on individual computers in all large corporations, government organizations, non-profits and most small and medium sized businesses. Excel worksheets are basic for running a business.

Microsoft (2003) reported that there were 400 million licensed Office installations worldwide. Some recent correspondence from some experts on EUSPRIG, indicated that currently there are about 440 million Excel users worldwide and about 160 million users of other spreadsheet programs.

In business and in engineering there are a lot of "small, frequent" problems. As Hillmer (1996) says, "First, managers have a greater need for statistical tools in problem solving than they do for statistical inference. A manager’s main use of data analysis is in the context of problems they face every day… Third, a beginning required statistics course for future managers should be sure to teach the tools, which are most likely to be relevant to solving problems. Many of the most relevant tools are relatively simple because experience has shown that many times simple tools are adequate for dealing with the majority of managerial problems."

Most of these problems can be very nicely investigated using the spreadsheet capabilities in Excel. The integration with financial and other functions can show effects in terms of future cost, future returns, and expected income. It can augment six sigma quality control efforts, identify "outliers", indicate ways to reduce inventory, reduce investment costs, increase yield, analyze problems and show unforeseen opportunities. The "What-if" type of analysis in Excel is a basic and important tool in business. In six-sigma quality, an outlier is an opportunity to investigate what caused it, not something to reject by "trimming". Excel has the ability to pre-process data and post-process results, which are decided advantages. The pivot table feature is very extensively used in business.

Also, "Windows is becoming less ubiquitous, 5% of PCs are not Windows, and this percentage is growing. Mac and Linux spreadsheets can be ported to Windows, but not conversely" (McCullough 2004). However in the business world, Visual Basic, Access Data bases and SQL is heavily used in applications. FORTRAN is not used, and C++ used with Linux is not used much for application packages. R is becoming dominant as a broad tool to solve data analysis problems, and it is free. The outputs are not directly transferable to Excel, except by text based copy-and-paste operations.

The ability to merge Excel with other business applications and to use a common programming language (Visual Basic, Visual Studio, etc.) is a distinct advantage. The Active X capabilities among Microsoft programs allow a lot of blending of real world data with tools to "work" the data.

The Statistical Service Center 2000 states, "Excel offers an exiting environment for data manipulation and initial data analysis. Its pivot tables are particularly good for cross-tabulations and summary statistics and provide a powerful tool for basic data analysis. The reliability of more advanced statistical functions and wizards is variable." They emphasize the use of pivot tables ("Excel’s pivot tables are very powerful and are an area that is better in Excel than in many statistics packages.") to summarize data and give an extended appendix on how to create and use them.

TESTING, MEASUREMENT, EXPERIMENTS
This is a very broad area involving data collection from instrumentation and from all kinds of physical devices. This includes industrial laboratories, field data collections, university laboratory courses, product development, etc. The essential core here is that the phenomena can be measured and the measurement involves instrumentation, where the output is electrical voltage.

The voltage, as an analog signal can be measured, and converted to a bit sequence, which in turn can end up as a value in an Excel cell. For example, DATAQ (www.dataq.com) (and others) have low cost analog to digital converters (ATD). The connection from the ATD device to a computer is via an USB link. Software (www.ultimaserial.com) within the computer then can manage the "collection" of data, do computations on it and generate charts. DATAQ also provides software that display ATD outputs. A four channel, 0 to 10 volt input, 14 bit integer output ATD capable of 14400 samples per second runs about $120. UltimaSerial Software using Active X then puts the data into Excel cells as normal numbers.

The Excel worksheet route gives a very flexible and low cost route to collecting a large amount of data, and fitting models and "concepts" to the data.

GENERAL DATA ANALYSIS IN EXCEL
Also to be considered are many complete statistical analysis programs that work within the Windows environment. Some are free (internet downloads) but most are commercial that have to be paid for. The commercial software packages can usually download data from Excel worksheets, but have their own peculiar outputs that may not appear as worksheet cells. Some of these programs, as add-ins that work entirely within Excel environment are described in section 19. The free POPTOOLS add-in and other add-ins add a lot of useful mathematical and statistical tools to Excel (but they are not tested for accuracy).

The fact still remains, that Excel has a very limited statistical capability. Where statistical analysis of data is a main job requirement, learning and using one or more of the larger commercial software packages is a must. However where presentations have to be frequently done (i.e. Power Point), the employee must learn how to use Excel, Power Point and the larger packages together to be effective in his job.

Author : David Heiser [dheiser594@gmail.com]

Wednesday, May 13, 2009

Macro to check for Addin Installation and Install or Uninstall accordingly

'Check for Installation of an Addin, Install and Uninstall accordingly.
Sub InstallAddIns()
    Dim objAddIn As AddIn
    Set objAddIn = AddIns("Analysis ToolPak")
    
    'Check whether an add-in is installed or not
    If objAddIn.Installed = True Then
        MsgBox "AddIn for Analysis Toolpak is installed"
        
        'Uninstall the addin
        AddIns("Analysis ToolPak").Installed = False
    
    Else
        
        MsgBox "AddIn for Analysis Toolpak is Not installed"
        
        'Install the addin
         AddIns("Analysis ToolPak").Installed = True
    
    End If

End Sub

Teaching Excel In Statistics Courses

Courses in Statistics have changed over the years with changes in technology, changes in society and changes in demands on the work force. Current guideline for contemporary statistics courses (ASA and Garfield, Hogg, Schau and Whittinghill 2000) now emphasis the need for students to rely on computers using statistical software programs. There is no way that contemporary analysis of data can be done without some form of a computer/calculator being used. Although calculators can be used in introductory courses, they are inadequate for problem solving and solution presentations of real world data.

There are many schools that teach an introductory course in statistics using Excel as a computing tool.

The issue here is that spreadsheets are universally used in business, government, education, research, manufacturing and just about all other sectors. Spreadsheets are an essential backbone in every aspect of what people do. Typically they are employed to:
1. Gather, create and manage data of all kinds; numbers, text, symbols, observations, surveys, money, financial, accounting, designs, etc.
2. To create models and make calculations.
3. To disseminate and share information in a basic form across a wide expanse of users and contributors in a completely connected world.

Spreadsheets have become indispensable tools for getting the informational work done. They are empowering tools that are expressive and apparently simple, yet underneath very complex. Text and numbers can be intermingled. They can be subservient in that they facilitate peer-to-peer sharing, non-technical people can do analysis and share the data and results. They facilitate back-channel, behind the scenes communications.

Raden 2006.

They also create enormous problems with errors in data entry, errors in equations, misuse of data sections, and incorrect use of functions. (This is discussed in my other articles.)
Business, Engineering, Psychology, Accounting and other schools in colleges and universities find they have to teach the intricacies of using spreadsheets such as Excel, because of its universal use in all sectors of the world. Statistics in a broad sense involves every one of the three areas that Raden (2006) points out. Therefore, why not teach statistics in a manner that involves spreadsheets? The other side of the coin is, if Excel has to be taught, why not include teaching about the use of the statistical functions and routines in Excel?

The University of Reading (SSC) has emphasized the use of Excel in statistics and the use of spreadsheets for entering and tabulating data. Arshan (20007a) has a web site that covers all the essential Excel statistical capabilities with respect to business applications, as part of an MBA degree program. Arshan (2007b and 2007c) has other sites that show extensive use of spreadsheets and cell equations to handle business decisions and other problems.

The intent of teaching the use of Excel is to produce graduates who can use the powerful spreadsheet capabilities of Excel, have some understanding of statistical methods and can do quantitative reasoning with statistics. Some other schools combine Excel with other software programs such as Minitab. Levine (1999) and Pelosi (2000) are some the more frequently used business statistics textbooks that incorporate Excel.

Levine and Fan (2000) say "The strongest aspect of the book ("Practical Statistics by Example Using Microsoft Excel", Sincich, Levine and Stephan, Prentice Hall, 1999) is the introduction and incorporation of Excel for doing and learning statistics. The authors rightfully argue in the preface that ‘today an increasing number of individuals use spreadsheet application as the means to retrieve and analyze directly the data they need. Employers now are beginning to desire, if not demand, that their college-educated, entry-level employees have more than just a cursory awareness of spreadsheet applications. Most students are familiar with Excel and/or have easy access to it on a personal computer…" They also point out that there are real dollar and time advantages to universities when Excel is used as part of the course. It allows students to do homework on personal computers, reduces the load on school computer labs and saves the cost of the expensive commercial licenses for use on each pc.

De Levie (2005) finds that Excel is useful in science and engineering laboratory and application areas. "Excel is a powerful spreadsheet. Even though it was developed primarily for business applications, it contains many mathematical functions, and its ease of use and wide distribution make it a very powerful tool for scientists and engineers." Some of the textbooks are Billo (2001), Block (2000), de Levie (2001), de Levie (2004), Gotfried (2000), Liengme (2002) and Orvis (1996). The Visual Basic for Applications (VBA) feature with the ability to program specific reductions and analysis is important in engineering and scientific areas. The main statistical usage here is equation fitting and regression. Graphic display of data and the display of data fits to mathematical relationships is also important.

The use of spreadsheets as a means of explaining subtle ideas by doing simulations is another valuable teaching method (Horgan 1999). See the great spreadsheet by Jacob Eisner (Eisner 2007) to teach the forward-backward algorithm to solve a probability problem. Some of the more difficult ideas in experimentation, sampling, variability and power can be demonstrated.

This paper refers to the following textbooks, which all incorporate the use of Excel for solving statistical problems. This is just a sample of many other textbooks that incorporate Excel.

1. Larson and Farber 2003
2. Levine, Berenson and Stephen 1999
3. Lind, Marchal and Mason 2002
4. Moore and McCabe 2003
5. Pelosi and Sandifer 2000
6. Triola 2001.

Texts 2, 3 and 5 are focused on business and economic applications. Text 4 also shows how to use JMP, Minitab, S-PLUS, SPSS and SAS. I am familiar with the use of 1 in an introductory statistics course.

However including Excel in an introductory statistics course has its own problems. The inclusion of Excel is controversial and is not universally accepted. Some teachers in university statistical departments disparage the use of Excel as a tool for doing statistical calculations. Others have tried it and found severe problems, and have discontinued the practice. Some of their arguments and criticisms are:

• It takes teaching time to teach Excel. Most introductory statistics courses are very time limited to teaching the material in the textbook. Teaching Excel takes away from teaching statistics. It is a lot easier to teach problem solving and to test students using hand calculators then with computer software.
• The Excel default graphics do not fit standard statistical data displays, and takes considerable teachers and students time to change them to standard displays. Excel 2007 makes it more difficult to make changes with the complex expanded menu structures. Excel is not a "one-button" statistics package.
• Excel functions and routines do not fully support the subject matter or the problems in the textbook (from 40% to 70%. of textbook problems are directly supported). Excel does not support expanded applications in line with contemporary statistics. The ANOVA and regression routines are too primitive and size limited. The data analysis routines are too primitive. Many teachers take the view that Excel is not capable of serious data analysis. Excel cannot be used in advanced classes (McCullough, 2004).
• The field of statistics is always evolving, with new ideas and methods to analyze data. At the introductory statistics level, some of this is being introduced. Excel is essentially a locked-in-time (ca 1990) approach to statistics, and has not introduced any new functions or routines (re: Excel 2007), relative to what has evolved since then.
• Uncertainty about the reported errors, faults and inaccuracies in Excel. This is a very weak argument, since it is not raised on other commercial software.
• Excel is not usable for classroom quizzes, tests and examinations based on solving problems to test students for comprehension and understanding. Testing is still a paper-and-pencil process.
• Text-books that incorporate the use of Excel normally include: a CD with data sets, files relating to teaching (illustrations, slides, etc.) and an Excel Add-in. Publishers charge more for this combination, markedly increasing the cost of a textbook with the Excel features for students. This is a significant unrecoverable expense for the student. I have heard lots of complaints from students on this.
• Based on my findings, I recommend that if Excel has to be used, the Excel 2003 version be used, because of its simplicity and general freedom from errors. However if the school/university is focused on Excel 2007, then it should be used. Since the look-and-feel of Excel 2007 is so different from Excel 2003, and the future Windows use will be Vista, teaching Excel 2007 would be the choice.

Note A, describes some of the other reported comments on the use of Excel in a teaching situation. Of particular interest is the paper by Peter C. Bell, (Bell 2000) about his course in business statistics using Excel.

Author : David Heiser [dheiser594@gmail.com]

Tuesday, May 12, 2009

Macro to Copy and Paste a Range in Worksheet

This is one of the frequently asked questions.  Check the code below to copy and paste a range in worksheet and perform a transpose on the copied data while pasting.

Code Block 1

Sub copyRange()

'declare a variable name for the content of a specific cell or a range.
Dim copyRange As Range
Set copyRange = Range("A1:A5")
Application.CutCopyMode = False
copyRange.Copy
Range("A8").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

End Sub

Macros to delete Blank or Empty Cells, Rows and Columns in Excel

Every excel user encounters the problem of having blank cells in their data and wants to delete them with ease. This can be done manually by following the below steps:

  • Select all cells (CTRL + A)
  • Edit-> GoTo (CTRL + G) -> Special -> Choose Blanks. This will select all the blank cells.
  • Edit -> Delete / Right Click -> Delete (select the direction of delete and shifting of cells accordingly).

  • You can also record a macro for the above and automate the process. Code might little lengthy based on your requirement. But this can be done with a single line of code. Check the below code blocks for the same. I have the below requirements in the code block.

    Code Block 1 : Delete empty cells without any direction.
    Code Block 2 : Delete empty cells and shift cells left.
    Code Block 3 : Delete empty cells and shift cells up.
    Code Block 4 : Delete entire row based on a blank cell in a particular column. This also covers deletion of all the blank rows from a worksheet.
    Code Block 5 : Delete entire column based on a blank cell in a particular row. This also covers deletion of all the blank columns from a worksheet.


    Code Block 1

    'Delete Blank Cells
    Sub DeleteEmptyCells()
    Cells.SpecialCells(xlCellTypeBlanks).Delete
    End Sub

    Code Block 2
    'Delete Blank Cells and Move Left
    Sub DeleteBlankMoveLeft()
    Cells.SpecialCells(xlCellTypeBlanks).Delete (xlToLeft)
    End Sub

    Code Block 3
    'Delete Blank or Empty Cells and Move Up
    Sub DeleteEmptyMoveUp()
    Cells.SpecialCells(xlCellTypeBlanks).Delete (xlUp)
    End Sub

    Code Block 4
    'Delete entire row based on a blank cell in the column
    'This can be used to delete all the empty rows
    Sub DeleteRowColumnBlank()
    '1 represent column A below. Replace 1 with any other column according to the requirement.
    Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub

    Code Block 5
    'Delete entire column based on a blank cell in the row
    'This can be used to delete all the empty columns
    Sub DeleteColumnRowBlank()
    'Used 1 below for First row. Replace 1 with a row according to the requirement.
    Rows(1).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
    End Sub

    Greatest advantage of using specialcells is, code is single line, so you need not loop through the whole sheet for finding out the results and acting on them.

    As a bonus the above code can be extended to delete rows / columns with a particular value without looping. Say, if you want to delete all the rows with text value "test" or numeric value '0' in column A. So, insted of looping for the values in the worksheet, just replace "test" and '0' with blank and use code block 4. Check the code below.

    Code Block 6

    Sub DeleteRowsBasedOnValue()
    Dim a As Boolean, b As Boolean
    a = Columns(1).Replace(0, "")
    b = Columns(1).Replace("test", "")
    '1 represent column A below. Replace 1 with any other column according to the requirement.
    Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub

    Hope you had fun with various deletion scenarios. Let me know your comments.

    Followers

    Recent Comments

      © Blogger template 'Perfection' by Ourblogtemplates.com 2008

    Back to TOP