VBA for Excel 2007 tutorial-VBA Built-in Functions


VBA Built-in Functions

In VBA, there are some built-in functions that you should know:

-Ucase(String): Converts a string to upper case
Example:
      Dim s1 As String
      s1="hello"
      Dim s2 As String
      s2=Ucase(s1)
      Msgbox s2

-Lcase(String): Converts a string to lower case, in contrast to UCase.
-Trim(String): Removes leading and trailing space from the string.
Example:
      Dim s1,s2 As String
      s1="hello "
      s2=Trim(s1)
      Msgbox s2  
 
-LTrim(String): Removes leading spaces.
-RTrim(String): Removes trailing spaces.
-Space(number_of_spaces): Adds spaces to a string.
Example:

      Dim s1 As String
      s1="hello"
      s1=s1 & space(2)
      Msgbox s1  

-Mid(string,start,length): Gets a sub string from the string from the left starting from start argument to the character specified by length.
Example:
      Dim s1,s2 As String
      s1="hello"
      s2=Mid(s1,1,3)
      Msgbox s2  
    

-Val(String_value): Convert a string of value to a value that can be computed.
      Example:
      Dim val1 As String
      Dim val2 As Integer
      val1="12"
      val2=Val(val1)
  Msgbox "The square=" & val2*val2

-Left(String. length): Get a sub string from the string starting from the left first character to the character specified by length.
Example:
Dim fname,lstname As String
fname="Yuk Sovandara"
lstname=Left(fname,3)
Msgbox lstname

-Right(String, length): Get a sub string from the string starting from the first right character to the character specified by length.

-Day(Date_value): Get the day from the date value.
Example:

      Msgbox Day("05-02-2012")

-Month(Date_value): Get the month from the date value.
-Year(Date_value): Get the year from the date value. -DateDiff(“Interval”,Date1,Date2[,firstdayofweek[,firstweekof year]]): Calculates the number of time intervals between the two dates—Date1 and    Date2.See the lists below to understand the interval, firstdayofweek, and firstweekofyear arguments.

The interval
argument:

Setting

Description

yyyy

Year

q

Quarter

m

Month

y

Day of year

d

Day

w

Weekday

ww

Week

h

Hour

n

Minute

s

Second

 The firstdayofweek argument:

Constant

Value

Description

vbUseSystem

0

Use the NLS API setting.

vbSunday

1

Sunday (default)

vbMonday

2

Monday

vbTuesday

3

Tuesday

vbWednesday

4

Wednesday

vbThursday

5

Thursday

vbFriday

6

Friday

vbSaturday

7

Saturday

The firstweekofyear argument:

Constant

Value

Description

vbUseSystem

0

Use the NLS API setting.

vbFirstJan1

1

Start with week in which January 1 occurs (default).

vbFirstFourDays

2

Start with the first week that has at least four days in the new year.

vbFirstFullWeek

3

Start with first full week of the year.

 
Example: If you want to find the number of days between current date and the date 01-04-1990, you can write:
Dim d As Integer
d=DateDiff("d","01-04-1990",Now(),1)
Msgbox d

-DateAdd("Interval",Number,Date_value): Adds time intervals to the date value.
Example: If you want to find the date that you add 60 days to the current date, you can write:
Dim enddate As Date
enddate=DateAdd("d",60,Now())
Msgbox enddate

-IsDate(value): Checks to see whether the value is a real date. It returns true if the value is a date, otherwise returns false.


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.