Posts Tagged ‘Built-in Function’

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”.

Syntax:
Format(Expression,[Format])

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

Examples:

  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

UntitledProblem Statement: I have grade-wise student numbers in column C as given below. I have written a formula in cell [A2] that sums up the total number of students in each grade.

Data

Data

Now I want to put a filter on grade, i.e. column A, and see the total number of students in that grade in all sections. If I simply use Autofilter, it will not give the desired result. (more…)

IsDate() Function : VBA

Posted: June 3, 2013 by Theodoulus in Excel, VBA
Tags: , ,

This function returns TRUE if argument’s data type is Date otherwise it returns FALSEHowever, there is an exception. In case the argument type is String the result depends on the contents of the string. If string is in a recognisable date (or time) format then the function returns TRUE.

One could say that the name of the function is misleading since it checks not just for date but time as well.

The Syntax for the function is:            IsDate(expression) 

Examples:

---------It accepts Date and Time in many formats------------------------ 
IsDate(#01/31/2013#)             'Returns True, as argument is of Date Type
IsDate(#jan-2013#)               'Returns True,as argument is of Date Type
Isdate("January,2013")           'Returns True, as String is in date format 
Isdate(10/15)                    'Return False, It treats it as a double value 
ISDate("10.15")                  'Returns True 
ISDate("10:15")                  'Returns True 
ISDate("10:15 AM")               'Returns True
ISDate("10:15:12")               'Returns True

LTRIM() Function VBA: To Remove Spaces

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

In VBA, LTRIM() function is used to remove the leading spaces from the given string.

Syntax:
The syntax for LTRIM() function is
LTRIM(text)

 text is the String that you wish to remove the leading spaces from.

Example:

Ltrim(“                  UsefulGyaan”)                      ‘ returns  “UsefulGyaan”
Ltrim(“                 UsefulGyaan          “)             ‘ returns  “UsefulGyaan          “
Ltrim(“UsefulGyaan          “)                             ‘ retruns “UsefulGyaan          “

Note: – LTRIM() function doesn’t remove the spaces between or after the string.

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.

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

How to use:

                                 Dim chrA   As String
                                 ChrA=CHR(87)

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.
Solution:
People fix this in two ways

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

Or

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!

CurDir() function VBA

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

Many times you want to know the complete path where you are currently working in.
So, CurDir() is the solution.

Now, the question is, “How to work with Curdir() in VBA”.

Ok, let me tell you the The syntax for the CURDIR function is:
                                                                                                     CurDir( drive )

drive is an optional parameter. If you provide this parameter, this function will give you the path which you provide but if you omit this parameter or pass an empty string, this will return the complete path where your current workbook is placed in.

For Example:

curdir("")                  ' will return "C:\Users\user\Documents"
CurDir()                    ' will return "C:\Users\user\Documents"
CurDir("E")                 ' will return "E:\"

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

(more…)