Posts Tagged ‘String’

Text Comparison: Option Compare Text

Posted: July 12, 2013 by kulshresthazone in Excel, Outlook, Powerpoint, VBA
Tags: ,

If we compare text in VBA Code then “B” is not equal to “b” since there ASCII codes for both these characters are different. However we can force Excel code to compare text on the basis of  Text not on their Binary values.
Make use of  Option Compare Text on the very First line of the Code module. It will force VBA to compare Text in Text mode not in Binary mode. By Default Text comparison is done in Binary mode.

If we don’t use Option Compare Text then
If we make use of Option Compare Text then

Happy Coding…

Format() Function for Strings : VBA

Posted: July 2, 2013 by Theodoulus in Excel, VBA
Tags: , , , ,

Many times we encounter with such a situation when we have a “String” and from this string we need to fetch a value in fromat of  Date, Percentage, Currency, Fixed, Scientific, Yes/No, On/Off etc.

Here comes the most used function of VBA, The “Format() Function”.


Expression is the String which you pass in order to fetch the desired output. Format is the optional argument. If you don’t pass Format then you will get the same String which you passed as input.
Although, Excel has it’s own formats for the data but You can define your own [Format] too. Here is the list of the Defined Formats in the Excel 2007:

General Number     -This gives you the same string as you passed as input
Fixed              -Atleast one digit on the left and two digits after the decimal place
Currency           -With Thousand Separators along with 2 decimal places
Standard           -Thousand separator, atleast one digit at the left of decimal place and two digit at
                    the right the decimal place
Percent            -Displays the percent value with two digits at the right of decimal place
True/False         -True for zero and False for Non-Zero
On/Off             -OFF for Zero and On for non Zero
Yes/No             -Displays No for zero and Yes for non-zero


  Format("1234","General Number")            'returns 1234
  Format("1234","currency")                  'returns $1,234.00
  Format("1234","Fixed")                     'returns 1234.00
  Format("1234","Standard")                  'returns 1,234.00
  Format("1234","Percent")                   'returns 123400.00%
  Format("1234","Yes/No")                    'returns   Yes
  Format("1234","General Date")              'returns 5/18/1903
  Format("1234","Long Date")                 'returns Monday, May 18, 1903             
  Format("1234","MMM, DDDD YYYY")            'returns May, Monday 1903

VBA Trick of the Week :: Byte Array in VBA

Posted: June 6, 2013 by Transformer in VBA
Tags: , , ,

VBA Trick HatIn VBA, Byte Arrays are special because, unlike arrays of other datatypes, a string can be directly assigned to a byte array. In VBA, Strings are UNICODE strings, so when one assigns a string to a byte array then it stores two digits for each character. The first digit will be the ASCII value of the character and next will be 0.


Sub Test()

        Dim bytArrNames()           As Byte

        bytArrNames() = "UsefulGyaan"

End Sub


VBA Trick of the Week :: MID() Function

Posted: May 30, 2013 by MaxFraudy in VBA
Tags: , ,

MID() Function is generally used to extract a substring from a string. The syntax of the function is:

                          MID(string, Start, [Length])

Here, string is the string that is being manipulated, Start is the position from which substring is to be extracted and Length is the number of characters that are to be extracted. We usually see its use on the right hand side of the “=” sign as below:

                        strSubString = Mid(“Orange Pie”,1,6)

which assigns “Orange” to the variable strSubString. Suppose now I wish to substitute the substring “Orange” with “Banana”. Perhaps the most common way of doing it is by using Replace function. However, this can also be done using the MID function (this time on the left hand side of the “=” sign) as below:

                       strString = Replace(strString,”Orange”,”Banana”)

                       Mid(strString, 1, 6) = “Banana”



CHR() Function VBA

Posted: May 10, 2013 by Theodoulus in VBA
Tags: , ,

In VBA, we have CHR() function that returns a character based on the ASCII value.

The syntax for CHR() function is
The parameter “Ascii_value” is used to retrieve the character.

How to use:

                                 Dim chrA   As String

In the above example, chrA will hold a value “W” which is ASCII equivalent to 87.

Problem Statement:
To insert an IF() statement in a cell as formula, which includes double quotes.
People fix this in two ways

Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0,"""",Sheet1!A1)"


Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0," & CHR(34) & CHR(34) & ",Sheet1!A1)" 

In the first way, we may get confused with the number of double quotes to put in whereas in the second example there is no chance of confusion. So, the second one is the nice choice.

See you soon!

STRCONV Function (VBA)

Posted: May 7, 2013 by Theodoulus in VBA
Tags: , , ,

We all encounter situations when we need to convert a string to different formats like Upper Case, Lower Case, Proper Case, Unicode etc. Here we can use STRCONV() function.

The Syntax for STRCONV() is:               STRCONV(text, Conversion, LCID)  

Parameters Description –
     Text : String that you want to convert.
     Conversion : Format type

  1. vbUpperCase : To convert a string to Upper Case
  2. vbLowerCase : To convert a string to Lower case
  3. vbProperCase : To convert a string to Proper Case
  4. vbUnicode : To convert a string to Unicode
  5. vbFromUnicode: To convert a string from unicode value of the string to the default code page of the system


This function returns the numbers found at the beginning of a string which is passed as an argument. It stops reading the string at the first character that it can’t recognize as part of a number.
But it ignores spaces.

Syntax: VAL(string)


Expression Result Comments
Val(“1234Excel5”) 1234 Stops reading at first alphabet
VAL(“1234.5Excel”) 1234.5 Dot (“.”) recognised as a decimal
VAL(“1234.5.56Excel”) 1234.5 VAL returns a value that is a valid number, so second dot not considered
VAL(“1234 23 34”) 12342334 Spaces ignored
VAL(“12323 .5 6”) 12323.56 Spaces ignored
VAL(“1,234.5”) 1 “,” not recognised as a thousands delimiter
VAL(“123E3Excel”) 123000 “E” recognized as “power of ten” delimiter
VAL(“123e3Excel”) 123000 “e” recognized as “power of ten” delimiter
VAL(“123D3Excel”) 123000 “D” recognized as “power of ten” delimiter
VAL(“123d3Excel”) 123000 “d” recognized as “power of ten” delimiter