SearchBox Week, Day 4

Yesterday there was a group of different searches, so today's post will be an amalgam of different code samples and formulas.

1) "convert to upper case + excel"

Converting text to upper case can be accomplished in two ways.

In Excel, use the UPPER function:

In VBA, use the Ucase$ function:

Or you could roll your own, it would go something like this: The Long Way toUpper

If you read the description of the Ucase function from Excel's help file, you get the following:

Returns a Variant (String) containing the specified string, converted to uppercase.

When you see "Variant (String)", that means you can append a dollar sign ($) at the end of the function name to use the string version of the function. This should provide a noticeable speed increase, because VBA does not have to convert the string to a Variant, process it, then convert it back to a Variant to return the updated value.

You can do this with other functions in VBA such as Left, Mid, Right.

2) "changing upper case to lower case in excel"

See above, substitute "LOWER" for "UPPER" and "LCASE$" for "UCASE$"

3) "excel"+"resolve names"+"email address"

The best way to send email using Outlook automation is to use email addresses, not names. You can use names if you are sure your address book(s) only contain(s) a single entry with that name; otherwise, the Resolve Names dialog box will prompt you to choose the person to which you intend to send the message. This will interrupt your VBA code if you are trying to automate the sending or processing of emails.

Programmatically, resolving names (in Excel VBA via Outlook automation) will trigger the security prompt, because the Resolve Method acts on the Recipient Object, which is protected object by the OOM (Outlook Object Model).

4) excel vba search for value in column, return row number function

There are a few ways we can locate a specific value in a column. The fastest way I've found is the Find Method. This code searches for the number 5 in range A1:A10 and returns the row number.

Sub FindRowNumber()
Dim rng As Excel.Range
 Set rng = Range("A1:A10").Find("5")
 MsgBox rng.Row

Set rng = Nothing
End Sub

Here's a function that allows you to pass in a Range Object and search term to it, and it returns the row number where the search term was found within that range. If the term isn't found, the function returns a zero.

Function Return_Found_Row(rng As Excel.Range, FindWhat As Variant) As Long
Dim rRng As Excel.Range
On Error Resume Next
Set rRng = rng.Find(FindWhat)
On Error GoTo 0

If rRng Is Nothing Then
  Return_Found_Row = 0
  GoTo ExitProc
Else
  Return_Found_Row = rRng.Row
End If

ExitProc:
Set rRng = Nothing
End Function