VBA and Excel for Engineers and Scientists – Part 2

String Functions

Why would you want to work with strings (text) in Excel? Aren’t you always just working with numbers? Well Mr. smarty pants, occasionally you might need to label something, or parse a string of comma or tab delimited values, or you might want to use Excel and VBA as a pre- or post-processor from some old program that uses text files for input and output. Or you might want to create a variable chart title or something. Regardless, just take my word for it, you’ll eventually need to work with strings in your applications. Here are the VBA functions you will want to get to know for this kind of work:

• Split() – Split a string into an array using a delimiter.
• Left(), Right(), Mid() – Useful for getting the first, last, or middle X number of characters in a string.
• Format() – Useful for converting numbers into a specific format for display our output.
  • Format(SomeNumber, "0.000″) or
  • Format(SomeDate, "h:mm:ss am:pm”)
• InStr() and InStrRev() – Find a substring within a string. Useful in conjunction with Left(), Right() and Mid() functions.
• Len() – Returns the length of the string, also useful with Left(), Right() and Mid() Functions.
• LCase() and UCase() – Converts case between upper and lower.

Miscellaneous Useful Functions

• Changing data types – CStr(), CLng(), CSng(), CDbl(), CBool()
• UBound() and LBound() – Upper bound and lower bound index value of an array (we’ll get to arrays in the third article)
• Input() – A simple way to get user input
• MsgBox() – Provide feedback to the user. Can also be used to get a Yes or No answer from the user.