Code For Excel And Outlook

Q2 2010 Stats

JP — Tue, 27 Jul 2010 11:30:14 +0000

Just realized I didn't post any metrics for Q2 this year.

We had the most single-day visits ever, at 1,255 on June 8th (that number has since been surpassed).

This site receives 80 to 100 refers per day. Link love goes out to the following people who have linked to Code For Excel and Outlook in Q2:

Chandoo from Chandoo.org
Dick from DDoE
Mike from DataPig Technologies
John's Spreadsheet Page
Debra at Contextures
Jon at Peltier Technical Services

And many others, you are not forgotten!

This blog was also mentioned in the NY Times Blog for my use of the NY Times API.

In the next quarter (Q3) we'll be going through more APIs, as well as exploring IE browser methods for IE automation and looking at YQL, the query language for Yahoo.

More stuff:

Zip outgoing attachments

JP — Fri, 23 Jul 2010 11:00:21 +0000

Edouard asks for some code that will automatically archive attachments on outgoing emails. Let's start with the code found at Saving Compressed Attachments and go from there.

We'll also need some help from Ron de Bruin and his code to zip files using VBA.

The Application_ItemSend Event

This Application-level event fires whenever any item is sent from Outlook. We'll use it to check any outgoing items to see if they are emails with attachments. If so, any unzipped attachments will be archived and re-attached to the email.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

On Error GoTo ErrorHandler

Dim msg As Outlook.MailItem
Dim msgAttachments As Outlook.Attachments
Dim attachmentsCount As Long
Dim i As Long
Dim tempFolder As String
Dim zipFileAttachment As String

' check if it's an email
If IsMail(Item) Then
Set msg = Item
' check if there are attachments
Set msgAttachments = GetAttachmentsColl(msg)
attachmentsCount = msgAttachments.Count

If attachmentsCount > 0 Then
' loop through attachments
For i = attachmentsCount To 1 Step -1
' look for non-zipped files
If Not IsArchive(msgAttachments.Item(i)) Then
' save to temp folder, create if nonexistent
Randomize
tempFolder = Environ("temp") & "\temp" & Rnd & "\"
If Not Len(Dir(tempFolder)) > 0 Then
MkDir tempFolder
End If

msgAttachments.Item(i).SaveAsFile tempFolder & msgAttachments.Item(i).fileName
' remove from email
msgAttachments.Item(i).Delete
End If
Next i

' go through temp folder, zip all files there and attach back to email
zipFileAttachment = ZipFiles(tempFolder)

' add zip file back to message
msgAttachments.Add zipFileAttachment
End If
End If

ProgramExit:
On Error Resume Next
Kill tempFolder
Exit Sub
ErrorHandler:
MsgBox Err.number & " - " & Err.Description
Resume ProgramExit
End Sub

Put this code in your ThisOutlookSession module (Where do I put my Outlook code?) and restart Outlook. Go back to the VB IDE and set a breakpoint on the first line, then send an email with an attachment and watch it work!

The first thing we do is check if the given item is an email. If so, we grab the attachments collection using GetAttachmentsColl. If there are attachments, we'll loop through the collection and check each one. The IsArchive function checks if a given attachment matches a pre-determined list of archive types (zip, rar). Add others if you wish.

If a non-archived file is found attached to the email, a temporary folder is created one level below the temp folder. The folder has a random number in it to severely limit the possibility of duplication (although we do delete it when the macro ends). The non-archived file is saved to the temp folder and removed as an attachment from the email.

The files in the temp folder are zipped and then the zip file is attached to the email. The temp folder (and all the files in it) are deleted.

Note that we loop backwards because we may be deleting attachments.

Additional Functions

You'll also need these functions in order for the event handler to work.

Function IsMail(itm As Object) As Boolean
IsMail = (TypeName(itm) = "MailItem")
End Function

Function GetAttachmentsColl(itm As Object) As Outlook.Attachments
Select Case itm.Class
Case olAppointment, olContact, olDocument, olMail, _
olMeetingRequest, olPost, olReport, olTask, olTaskRequestAccept, _
olTaskRequestDecline, olTaskRequest, olTaskRequestUpdate
Set GetAttachmentsColl = itm.Attachments
End Select
End Function

Function IsArchive(attachFileName As String) As Boolean

Dim archiveTypes() As String
Dim fileExt As String

archiveTypes = Split("ZIP, RAR", ",")

' get file extension
fileExt = UCase$(GetFileType(attachFileName))

If UBound(Filter(archiveTypes, fileExt)) > -1 Then
IsArchive = True
End If
End Function

Function GetFileType(ByVal fileName As String) As String
' get file extension
GetFileType = Mid$(fileName, InStrRev(fileName, ".") + 1, Len(fileName))
End Function

Function ZipFiles(folder As Variant, Optional fileName As String = "files") As String
' http://www.rondebruin.nl/windowsxpzip.htm

Dim ZipFilename As Variant
Dim folderName As Variant
Dim ShellApp As Object
Dim tempFolder As Variant

Const ZIP_FILE_EXTENSION As String = ".zip"

' create filename, check for trailing slash
tempFolder = Environ("temp") & "\"

ZipFilename = tempFolder & fileName & ZIP_FILE_EXTENSION

'Create empty Zip File
NewZip (ZipFilename)

Set ShellApp = CreateObject("Shell.Application")

'Copy the files to the compressed folder
ShellApp.NameSpace(ZipFilename).CopyHere ShellApp.NameSpace(folder).Items

'Keep script waiting until Compressing is done
On Error Resume Next
Do Until ShellApp.NameSpace(ZipFilename).Items.Count = _
ShellApp.NameSpace(folder).Items.Count
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0

ZipFiles = ZipFilename

End Function

Sub NewZip(sPath)
' http://www.rondebruin.nl/windowsxpzip.htm
'Create empty Zip File
'Changed by keepITcool Dec-12-2005
If Len(Dir(sPath)) > 0 Then Kill sPath
Open sPath For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
End Sub

More stuff:

Delete duplicate contacts in Outlook using VBA

JP — Tue, 20 Jul 2010 11:00:30 +0000

Hey, how else were we going to do it?

Deleting duplicate contacts is a tricky business. How do you decide when a pair of contacts are the same?

Are they duplicates when they have the same name and phone number?
What if one has the home address and the other doesn't?
And what about the endless variations on a contact's name?

Here's one way. The following code loops through the default Contacts folder and checks for another contact with the same full name. If it finds one, you're prompted to delete it.

To look for duplicates, we use the Restrict method, instead of comparing each contact to all the others (which would be very wasteful).

We create a new Items collection consisting of all the contacts with a given full name. If there are more than one, there's a duplicate (according to our criteria). The ancillary functions are used to return a reference to the Contacts folder Items collection.

Sub DeleteDuplicateContacts()

Dim contactItems As Outlook.Items
Dim contact As Outlook.ContactItem
Dim contactFullName As String
Dim filteredContacts As Outlook.Items
Dim numberOfContacts As Long
Dim i As Long

Set contactItems = GetItems(GetNS(GetOutlookApp), olFolderContacts)
numberOfContacts = contactItems.Count

' loop through contact items folder
For i = numberOfContacts To 1 Step -1
If IsContact(contactItems.Item(i)) Then
Set contact = contactItems.Item(i)

contactFullName = contact.FullName

' check if any other contacts have the same full name
Set filteredContacts = _
contactItems.Restrict("[FullName] = '" & contactFullName & "'")

If Not filteredContacts.Count = 1 Then ' possible dupe
If MsgBox("Duplicate contact found, delete?" & _
vbCrLf & contactFullName, vbYesNo) = vbYes Then
contact.Delete
End If
End If

End If
Next i

End Sub

Function IsContact(itm As Object) As Boolean
IsContact = (TypeName(itm) = "ContactItem")
End Function

Function GetItems(olNS As Outlook.NameSpace, _
folder As OlDefaultFolders) As Outlook.Items
' returns the Items Collection for a given default
' folder and Namespace
Set GetItems = olNS.GetDefaultFolder(folder).Items
End Function

Function GetOutlookApp() As Outlook.Application
' returns reference to native Outlook.Application object
Set GetOutlookApp = Outlook.Application
End Function

Function GetNS(ByRef app As Outlook.Application) _
As Outlook.NameSpace
' returns a Namespace Object to access MAPIFolder objects
Set GetNS = app.GetNamespace("MAPI")
End Function

This code is meant to be used in Outlook and should be placed in a standard module in the Outlook VB IDE. Where do I put my Outlook code?

More stuff:

Which macro shortcut keys do you use?

JP — Fri, 16 Jul 2010 11:00:52 +0000

A quick poll: What shortcut keys do you use for your macros, and what macros are they assigned to? Here are mine.

They're not written as well as they could be, but I don't care. They work and that's good enough for me (until I get really bored; then I'll fix them).

Shortcut key: Ctrl+Shift+P
Macro name: CopyPasteValues
Description: Converts formulas in selected range to their values
Macro:

Sub CopyPasteValues()

Dim rng As Excel.Range

If TypeName(Selection) <> "Range" Then
Exit Sub
End If

Set rng = Selection

rng.Copy

rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

A quick way to remove formulas and paste in the underlying data.

Shortcut key: Ctrl+Shift+F
Macro name: FilterToggle
Description: Adds autofilter arrows to selected range (usually row 1)
Macro:

Sub FilterToggle()
' add data filter arrows
' if error occurs, just skip
On Error Resume Next
Selection.AutoFilter
End Sub

Apply autofilter arrows to any worksheet. Just select the appropriate row first.

Shortcut key: Ctrl+Shift+H
Macro name: FormatHeaderRow
Description: Applies autofilter arrows, bold and color formatting to row 1, centers row 1 text and autofits all columns
Macro:

Sub FormatHeaderRow()

Application.ScreenUpdating = False

If ActiveSheet.AutoFilterMode = False Then
With Range(Range("A1"), Range("IV1").End(xlToLeft))
.AutoFilter
.Font.ColorIndex = 2
.Font.Bold = True

With .Interior
.ColorIndex = 43
.Pattern = xlSolid
End With

.HorizontalAlignment = xlCenter
.WrapText = False
.Columns.AutoFit
End With

' Range("A2").Select
' ActiveWindow.FreezePanes = True
Else
MsgBox _
"Cannot autofilter the header row, there is already an autofilter on this sheet", vbCritical
End If

Application.ScreenUpdating = True
End Sub

This macro makes the header row (usually row 1) look exactly how I like it. The autofilter arrows are applied. The cells' text is centered. The font is bold, white with green background (I change this depending on my mood). Finally, the columns are autofit so I can see the entire header and all the data.

Optionally, I can freeze the window pane so that the header stays on the screen when scrolling.

Shortcut key: Ctrl+Shift+S
Macro name: SetNormal
Description: Removes formatting from worksheet and makes it look "normal" (according to me)
Macro:

Sub SetNormal()
' set worksheet to normal
Application.ScreenUpdating = False

If TypeName(Selection) <> "Range" Then Exit Sub

Dim rng As Excel.Range
Set rng = Selection

If rng.cells.count = 1 Then
Set rng = ActiveSheet.UsedRange
ActiveWindow.Zoom = 80
End If

With rng
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone

With .Font
.Bold = False
.Name = "Tahoma"
.ColorIndex = 0
.Size = 10
End With

.Interior.ColorIndex = xlNone
.HorizontalAlignment = xlGeneral
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
.WrapText = False
.Rows.AutoFit
.Columns.AutoFit
End With

Application.ScreenUpdating = True
End Sub

My "normal" is different from yours. I hate getting spreadsheets with different fonts, font sizes and colors. When I just want to work with the data and not be distracted by the layout, I run this macro. It sets the window zoom to 80%, with a Tahoma font (size 10). It removes any border lines and cell colors. It re-orients any cell to General alignment and unwraps any wrapped cells. Finally, it autofits all rows and columns so all data is visible.

Shortcut key: Ctrl+Shift+U
Macro name: TogglePersonalXls
Description: Hides and unhides personal.xls workbook
Macro:

Sub TogglePersonalXls()
' toggle personal.xls workbook hidden status
Dim window As Boolean
window = Windows("PERSONAL.XLS").Visible
Windows("PERSONAL.XLS").Visible = Not window
End Sub

I don't use this one much anymore, but the shortcut key is still there in case I need it.

So what shortcut keys do you use, and what macros are assigned to them?

More stuff:

What columns are filtered?

JP — Tue, 13 Jul 2010 11:00:30 +0000

Ever set up a monster spreadsheet with dozens of columns, then autofilter it, then forget which columns are autofiltered?

The little autofilter arrows turn blue when a filter is applied, but who wants to lose their eyesight scrolling across a worksheet looking for which ones are in effect?

I don't. So I came up with this little script that tells you which autofilter columns are actually in effect.

Sub CheckFilter()

Dim wksht As Excel.Worksheet
Dim autofilt As Excel.AutoFilter
Dim autofiltRange As Excel.Range
Dim offsetRange As Excel.Range
Dim flt As Excel.Filters
Dim msg As String
Dim i As Long

Set wksht = ActiveSheet

If wksht.FilterMode Then
Set flt = wksht.AutoFilter.Filters

For i = 1 To flt.count
' if Filter is activated, get range name
If flt.item(i).On Then

' Filter parent is AutoFilter Object
Set autofilt = flt.item(i).Parent
Set autofiltRange = autofilt.Range
Set offsetRange = autofiltRange.Resize(1, 1).Offset(, i - 1)

msg = msg & Range(offsetRange.Address).value & vbCrLf
End If
Next i

MsgBox wksht.Name & " in " & wksht.Parent.Name & _
" is filtered on the following columns: " & vbCrLf & msg

Else
MsgBox "Worksheet is not filtered."
End If

End Sub

I put this baby in my PERSONAL.XLS and when I run it, I get a popup telling me which column headers have the autofilter applied. Sweet! It's my giggle moment.

More stuff:

Adding vacation days to Outlook in bulk from Excel

JP — Fri, 09 Jul 2010 11:30:20 +0000

A friend sent me a spreadsheet with a list of dates. He wants to put them on his calendar as vacation days, but doesn't want to do it manually. Hooray, another free job.

The list of dates starts in cell A2, with A1 being a header row. Nice! I love those kinds of worksheets.

All we need now is a little procedure to loop through the range and add an appointment to the Outlook calendar, making it a vacation day. Here's what I came up with.

Sub CreateAppointments(Optional subject As String = "Vacation", _
Optional columnLetter As String = "A")

Dim cell As Excel.Range
Dim rng As Excel.Range
Dim wholeColumn As Excel.Range
Dim startingCell As Excel.Range
Dim oApp As Object ' Outlook.Application
Dim appt As Object ' Outlook.AppointmentItem
Dim wkbk As Excel.Workbook
Dim wksht As Excel.Worksheet
Dim lastRow As Long
Dim arrData As Variant
Dim i As Long

' late bound constants
Const olAppointmentItem As Long = 1
Const olOutOfOffice As Long = 3

' get range of dates
Set wkbk = ActiveWorkbook
Set wksht = wkbk.ActiveSheet
Set wholeColumn = wksht.Range(columnLetter & ":" & columnLetter)
lastRow = wholeColumn.End(xlDown).Row - 2
Set startingCell = wksht.Range(columnLetter & "2")
Set rng = wksht.Range(startingCell, startingCell.Offset(lastRow))

' start Outlook
Set oApp = GetOutlookApp

If oApp Is Nothing Then
MsgBox "Could not start Outlook.", vbInformation
Exit Sub
End If

' read range into array in one go
arrData = Application.Transpose(rng.Value)

' loop through array, not range
For i = LBound(arrData) To UBound(arrData)

' create new appt and set appropriate properties
Set appt = oApp.CreateItem(olAppointmentItem)

With appt
.AllDayEvent = True ' block out the whole day
.body = subject
.Start = arrData(i) ' no need to set End Property for all-day events
.subject = subject
.BusyStatus = olOutOfOffice
.ReminderSet = False ' no need for reminder, right?
.Save
End With
Next i

End Sub

Function GetOutlookApp() As Object
' return Outlook.Application object
On Error Resume Next
Set GetOutlookApp = CreateObject("Outlook.Application")
End Function

The procedure lets you decide what you want to call each appointment. I assume "Vacation", but if you were using this procedure to block out a series of dates for a professional development conference, you would call it something else.

You can also specify the column, if the data is somewhere other than column A. For example, if your dates were in column H starting in cell H2, with H1 as the header cell, you would call the function like this:

Call CreateAppointments(, "H")

The first thing the procedure does is determine the range being used to hold the dates. After starting Outlook, the range is read into a Variant (array) and then looped. For a simple task like this we could have also used a 'For Each cell in Range'-type loop, but I chose to actually follow best practices and use an array instead of touching the worksheet repeatedly.

For each appointment, we set three key properties: AllDayEvent, Start and BusyStatus. These are the ones that will make each one a vacation day.

Sample Usage

Sub TestCreateAppt()

Call CreateAppointments("On my way to Excel DevCon 2010")

End Sub

Download sample workbook

More stuff:

World Cup Flags

JP — Tue, 06 Jul 2010 11:30:39 +0000

If you're like me, stuck at work while the World Cup rages, you might still be lucky enough to be able to visit FIFA.com and watch the play-by-play. I'm not a big soccer fan but I depend on the realtime play-by-play to tell me what's happening during the game.

You might have also noticed those cool country flags on FIFA's website.

Fortunately, they are downloadable for use in your Excel worksheets and VBA programs. Let's build a small app to display and save them.

If you right-click on the flag images on the FIFA homepage, the URL looks like this (for Algeria):

http://img.fifa.com/imgml/flags/m/alg.gif

The icon-size flags have the following URL:

http://img.fifa.com/imgml/flags/s/alg.gif

On a hunch, I tried this URL to get a large size flag:

http://img.fifa.com/imgml/flags/l/alg.gif

The FIFA homepage also has flags with cool mirror reflections under them. They follow a similar naming scheme, but only medium size images are available in reflected form:

http://img.fifa.com/imgml/flags/reflected/m/alg.png

So to build a string that points to the correct flag, we just need to determine what size we want, and the country abbreviation as well as which version we want (normal or reflected). If we want a reflected image, we can only request medium. Note that the reflected images are PNG, while the normal ones are GIF.

The Userform

The first thing I did was create a String list of World Cup countries:

Public Const COUNTRY_LIST As String = "ALG,ARG,AUS,BRA,CHI,CIV,CMR,DEN,ENG,ESP,FRA," _
& "GER,GHA,GRE,HON,ITA,JPN,KOR,MEX,NED,NGA,NZL,PAR," _
& "POR,PRK,RSA,SRB,SUI,SVK,SVN,URU,USA"

Note that you can also get the flags for other countries, provided you know the three-letter abbreviation.

And you can feel free to add more to the app by visiting the FIFA World Cup Archives and adding past teams. You could even add a combo box for the year and have a different set of teams load into the listbox depending on which year you select!

To load the list of countries into a listbox on my userform, I used the following code:

Private Sub UserForm_Initialize()

On Error GoTo ErrorHandler

Dim countriesList() As String

countriesList = Split(COUNTRY_LIST, ",")

Me.CountriesList.List = countriesList

ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub

When the userform loads, this code splits the global constant COUNTRY_LIST by comma into a String array. This is passed into a listbox using its List Property.

The flag is displayed when an item from the listbox is selected. The ListBox_Click event builds the URL string based on the selections made on the form. A WebBrowser control is used to display the image, rather than trying to download it and load it into an Image control. That means you can right-click the control when a flag is displayed and save it to a folder!

Private Sub CountriesList_Click()

On Error GoTo ErrorHandler

Dim imageSize As String
Dim Reflected As Boolean
Dim URL As String

' when a country is selected, check size and reflected
imageSize = GetImageSize
Reflected = IsReflected

' if reflected image is requested, size MUST be medium
If Reflected Then
imageSize = "m"
Me.optMedium.Value = True
End If

' start with global base URL
URL = BASE_URL

' add reflected (if necessary)
If Reflected Then
URL = URL & "reflected" & "/"
End If

' add imagesize
URL = URL & imageSize & "/"

' add country abbrevation
URL = URL & Me.CountriesList.Value

' if reflected, add .png extension, else .gif
If Reflected Then
URL = URL & ".png"
Else
URL = URL & ".gif"
End If

' display image in web browser control
Me.ImageViewer.Navigate URL

ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub

Here is the finished product:

Download

Download sample workbook

More stuff:

Searching for a between formula

JP — Fri, 02 Jul 2010 11:00:58 +0000

In Between Formula in Excel, Chandoo presents some formulas for determining if a given value is in between two known values.

Let's say you have a target value in B3. Start and end values are in cells C3 and D3, respectively.

The first formula Chandoo presents to determine if the value in B3 is between C3 and D3 is:

=IF(AND(B3>=C3,B3<=D3),"Yes", "No")

It assumes that C3 is smaller than D3. Here's one possible correction:

=OR(AND(B3>=C3,B3<=D3),AND(B3<=C3,B3>=D3))

This would correct the first formula in situations when the cell values in C3 and D3 were switched (i.e. the ending value is less than the starting value).

But here's one problem. What's your definition of "between"?

In my example, both formulas return True when checking if 10 is "between" 10 and 20. You may not want that. I know I would only consider the numbers 11 through 19 to be between 10 and 20.

To fix this, all we need to do is remove the equal signs from both formulas:

=IF(AND(B3>C3,B3

=OR(AND(B3>C3,B3D3))

Daniel Ferry goes a step further. He recognizes that the MEDIAN function will return the middle number in a set of three. So for any given number, if it falls between a given starting and ending number (a total of three values), the MEDIAN function will return the given number.

=B3=MEDIAN(B3:D3)

To exclude the starting and ending numbers (to get a true "between" check), this version of the formula will work:

=B3=MEDIAN(B3,C3+1,D3-1)

But I want something a bit more flexible. So I set up Data Validation in cell H2 as follows:

Now I can use the following formula, and cell H2 will be my "toggle button" to switch between inclusive and exclusive "between" formulas. In fact, we can use an actual toggle button!

=B3=MEDIAN(B3,C3+N(H2),D3-N(H2))

The N function converts TRUE and FALSE values in cell H2 to 1 and 0 respectively. If H2 is TRUE, then the formula becomes

=B3=MEDIAN(B3,C3+1,D3-1)

and the formula is exclusive. It will return FALSE in the topmost example. If H2 is FALSE, the formula is

=B3=MEDIAN(B3,C3+0,D3-0)

and the formula is inclusive. It will return TRUE in the topmost example.

Download a workbook with all of these formulas: Between Formulas

Searching for a between formula is Copyright © Code For Excel And Outlook. All Rights Reserved.
More stuff:

Where do I put my Outlook code?

Contribute to this blog

VBA Search Engine

Site Map

Office Links for June

JP — Tue, 29 Jun 2010 11:00:36 +0000

It was a busy month! Here's the link roundup.

UK Excel Developer Conference

Excel DevCon 2010 was announced, and there has been a lot of blogging about it. Simon Murphy, Ross McLean and Charles Williams (of Decision Models) will be presenting.

Ross links to the venue and the agenda. He also included a nice photo showing England's goalie making a save.

Simon provides booking details. If you are going, book now to take advantage of the discount (150 GBP vs 200 GBP in July).

Visit Excel Developer Conference for more information and booking details.

Soccer Mania

The Excel Team Blog has extended their World Cup / Excel 2010 Soccer Challenge. If you have an Excel workbook for tracking World Cup scores, make it stand out by adding realtime scores updated from the web by visiting Realtime World Cup Scores.

Sentence Case

In Convert to Sentence Case, Chandoo provides a formula for converting to sentence case (that is, first word capitalizes, the rest lower case). I've also written an article about case changing in Excel, which includes some VBA functions for doing the same.

My concern is that some proper nouns will be skipped, but coding the logic would be very difficult, even with VBA. I'm sure there's a way to leverage the Wordnik API here to check for proper noun capitalization.

MSDN Links

Microsoft went crazy this month, adding a lot of content to their Office Developer section. Here are just a few of the links:

Visit Windows API Declarations and Constants for a list of Windows API declarations. Includes 64-bit declarations for Office 2010!

For assistance creating charts in Word 2010 using VBA, see Creating Charts with VBA in Word 2010. The code used in the article may be downloaded here.

What's new for developers in Outlook 2010 lists new members for various objects in the Outlook object model.

If you develop solutions for Office 2010 and want to see some icons you can use, visit Office 2010 Add-In: Icons Gallery. You'll need Word 2010 or a compatible viewer.

A few more MSDN articles you might be interested in:

Working with VBA in Office 2010 (32-bit) and Office 2010 (64-bit)

Excel 2010 Performance: Optimizing Performance Obstructions

Excel 2010 Performance: Performance and Limit Improvements

Excel 2010 Performance: Improving Calculation Performance

Microsoft has also introduced the VBA Developer Center, which may be accessed from Office VBA Developer Center or the aptly-named iheartmacros.com (via John Durant's blog).

If you want to see MSDN updates in your RSS reader, instead of waiting for me to post them, add the Microsoft Office Developer Center RSS Feed to your reader.

For a free 184-page E-book about Office 2010, visit First Look: Microsoft Office 2010.

New Pages

I've been steadily documenting the Outlook Object Model with encapsulated functions and sample VBA code.

The AppointmentItem Object

The MailItem Object

The PostItem Object

Sample Wordnik App

I've posted a sample Word add-in that utilizes the Wordnik API on the Wordnik API code samples page. The application is called Wordnik For Word and it uses several of the methods available on that page. I'm planning on updating that page shortly with new developments from Wordnik.

Bad Behaviour

Have you been blocked by the Bad Behaviour plugin? If so, let me know.

The plugin analyzes user agent behavior (among other things) to block malicious access attempts. Invariably, it may block legitimate access attempts, but displays a Turing test to allow you to quickly get past the block.

Code Requests

I've asked before, but I'll ask again: is there some piece of code you'd like to see here?

If so, leave a comment and I'll do my best to get it done. I do have a list of posts I'm working on, but I don't know what you want to see unless you tell me.

If you'd like to contribute, visit the Contribute page and submit your post for consideration.

And by the way, while you're here, take the visitor survey. Your responses will help make this a better site.

See you next time.

Office Links for June is Copyright © Code For Excel And Outlook. All Rights Reserved.
More stuff:

Where do I put my Outlook code?

Contribute to this blog

VBA Search Engine

Site Map

Convert text to numbers in Excel

JP — Fri, 25 Jun 2010 11:00:40 +0000

Sometimes I get a spreadsheet with a list of numbers in text format.

The fastest way I found to convert them into numbers was to select them and then run this little bit of VBA code, typed directly into the Immediate Window:

selection.value = selection.value

Before:

After:

Of course, if your numbers have leading zeroes, you'll need to make other arrangements. What do you do?

Convert text to numbers in Excel is Copyright © Code For Excel And Outlook. All Rights Reserved.
More stuff:

Where do I put my Outlook code?

Contribute to this blog

VBA Search Engine

Site Map