VBA functions in AS2005
(By Irina Gorbach)
Similarly to Analysis Services 2000, Analysis Services 2005 uses stored
procedure technology to extend the number of built-in MDX functions. It
automatically registers two libraries: the Visual Basic for Applications library
and the Microsoft Excel worksheet library. Visual Basic for Applications library
is registered as a CLR assembly and Excel library – as COM assembly. MDX
expressions can use any of supported VBA and Excel functions, as if there
were native MDX functions.
However there is an important difference between native MDX functions and
VBA or Excel functions – performance. In chapter 14 of our book “Microsoft
SQL Server 2005 Analysis Services” I explained how CLR and COM
assemblies are hosted inside of Analysis Services process. Basically CLR
assemblies are hosted in a separate application domain and in addition to
marshaling from native to managed code and back, all the calls are also
marshaled across application domains. Therefore calling a VBA function is an
expensive operation comparing to calling an MDX function.
To make performance critical customer applications run faster, we have
implemented some of the most often used functions as part of Analysis
Services code base. I’ll call this set of functions “internal VBA”. And even
through, I personally dislike this direction our product took; “internal” VBA
functions are much faster then regular VBA functions.
You don’t need to do anything special to enable “internal” functions - they
are on by default. However, when developing MDX expressions it useful to
know internal functions, so here is table that contains all VBA functions.
Functions that have internal implementation today – February 8, 2007 are
marked as internal.
Function Is internal Help
Abs Yes Returns the absolute value of
a specified number.
Asc No Returns an Integer value
representing the character code
corresponding to a character.
AscW No Returns an Integer value
representing the character code
corresponding to a wide-
character.
Atn No Returns a Double value
containing the angle whose
tangent is the specified
number.
CBool No Converts a value to Boolean
CByte No Converts a value to Byte
CCur No Converts a value to Currency
CDate Yes Converts a value to Date
CDbl No Converts a value to Double
CDec No Converts a value to Decimal
Choose No Selects and returns a value from
a list of arguments.
Chr No Returns the character associated
with the specified character code.
ChrW No Returns the Unicode character
associated with the specified
character code.
CInt Yes Converts a value to Integer
CLng Yes Converts a value to Long
Cos No Returns a Double value
containing the cosine of the
specified angle.
CSng No Converts a value to Single
CStr Yes Converts a value to string
CVar No Converts a value to variant
Date No
DateAdd No Returns a Date value containing
a date and time value to which a
specified time interval has been
added.
DateDiff No Returns a Long value specifying
the number of time intervals
between two Date values.
DatePart No Returns an Integer value
containing the specified
component of a given Date
value.
DateSerial No Returns a Date value
representing a specified year,
month, and day, with the time
information set to midnight
(00:00:00).
DateValue No
Returns a Date value
containing the date
information represented by
a string, with the time
information set to midnight
(00:00:00).
Day No Returns an Integer value from 1
through 31 representing the day
of the month.
DDB No Returns a Double specifying the
depreciation of an asset for a
specific time period using the
double-declining balance method
or some other method you
specify.
Exp No Returns a Double value
containing e (the base of
natural logarithms) raised to
the specified power.
Fix No Return the integer portion of a
number.
Format No Returns a string formatted
according to instructions
contained in a format String
expression.
FV No Returns a Double specifying the
future value of an annuity based
on periodic, fixed payments and
a fixed interest rate.
Hex No Returns a string representing the
hexadecimal value of a number.
Hour No Returns an Integer value from 0
through 23 representing the hour
of the day.
IIF No Returns one of two objects,
depending on the evaluation of
an expression.
InStr Yes (SP2) Returns an integer specifying the
start position of the first
occurrence of one string within
another.
Int Yes Return the integer portion of a
number.
IPmt No Returns a Double specifying the
interest payment for a given
period of an annuity based on
periodic, fixed payments and a
fixed interest rate.
IRR No Returns a Double specifying the
internal rate of return for a series
of periodic cash flows (payments
and receipts).
IsArray Yes Returns a Boolean value
indicating whether a variable
points to an array.
IsDate No Returns a Boolean value
indicating whether a variable
points to a date.
IsEmpty No Returns a Boolean value
indicating whether a variable has
been initialized.
IsError Yes Returns a Boolean value
indicating whether an expression
is an exception type.
IsNull No Returns a Boolean value that
indicates whether an expression
contains no valid data (Null).
IsNumeric No Returns a Boolean value
indicating whether an expression
can be evaluated as a number
LCase No Returns a string or character
converted to lowercase.
Left Yes Returns a string containing a
specified number of characters
from the left side of a string.
Len Yes Returns an integer containing
either the number of characters
in a string or the number of bytes
required to store a variable.
Log No Returns a Double value
containing the logarithm of a
specified number.
LTrim No Returns a string containing a
copy of a specified string with no
leading spaces (LTrim), no
trailing spaces (RTrim), or no
leading or trailing spaces (Trim).
Mid Yes Returns a string containing a
specified number of characters
from a string.
Minute No Returns a string containing a
specified number of characters
from a string.
MIRR No Returns a Double specifying the
modified internal rate of return
for a series of periodic cash flows
(payments and receipts).
Month No Returns an Integer value from 1
through 12 representing the
month of the year
Now Yes Gets a DateTime that is the
current local date and time on
this computer.
NPer No Returns a Double specifying the
number of periods for an annuity
based on periodic, fixed
payments and a fixed interest
rate.
NPV No Returns a Double specifying the
net present value of an
investment based on a series of
periodic cash flows (payments
and receipts) and a discount rate.
Oct No Returns a string representing the
octal value of a number
Partition No Returns a string representing the
calculated range that contains a
number.
Pmt No Returns a Double specifying the
payment for an annuity based on
periodic, fixed payments and a
fixed interest rate
PPmt No Returns a Double specifying the
principal payment for a given
period of an annuity based on
periodic, fixed payments and a
fixed interest rate
PV No Returns a Double specifying the
present value of an annuity
based on periodic, fixed
payments to be paid in the future
and a fixed interest rate.
QBColor No Returns an Integer value
representing the RGB color code
corresponding to the specified
color number.
Rate No Returns a Double specifying the
interest rate per period for an
annuity
RGB No Returns an Integer value
representing an RGB color value
from a set of red, green and blue
color components
Right Yes Returns a string containing a
specified number of characters
from the right side of a string
Rnd No Returns a random number of
type Single
Round Yes Returns a Double value
containing the number
nearest the specified value.
RTrim No Returns a string containing a
copy of a specified string with no
leading spaces (LTrim), no
trailing spaces (RTrim), or no
leading or trailing spaces (Trim).
Second No Returns an Integer value from 0
through 59 representing the
second of the minute
Sgn No Returns an Integer value
indicating the sign of a
number.
Sin No Returns a Double value
specifying the sine of an
angle.
SLN No Returns a Double specifying the
straight-line depreciation of an
asset for a single period
Space No Returns a string consisting of the
specified number of spaces
Sqr No Returns a Double value
specifying the square root of
a number.
Str No Returns a string consisting of the
specified number of spaces
StrComp No Returns -1, 0, or 1, based on the
result of a string comparison
StrConv No Returns a string converted as
specified.
String No Initializes a new instance of the
String class to the value
indicated by a specified Unicode
character repeated a specified
number of times
Switch No Evaluates a list of expressions
and returns an Object value of
an expression associated with the
first expression in the list that is
True
SYD No Returns a Double specifying the
sum-of-years digits depreciation
of an asset for a specified period
Tan No Returns a Double value
containing the tangent of an
angle.
Timer No Returns a Float value
representing the number of
seconds elapsed since
midnight.
TimeSerial No Returns a Date value
representing a specified hour,
minute, and second, with the
date information set relative to
January 1 of the year 1.
TimeValue No Returns a Date value containing
the time information represented
by a string, with the date
information set to January 1 of
the year 1
Trim No
Returns a string containing
a copy of a specified string
with no leading spaces
(LTrim), no trailing spaces
(RTrim), or no leading or
trailing spaces (Trim).
TypeName No Returns a String value
containing data type information
about a variable.
UCase No
Returns a string or character
containing the specified
string converted to
uppercase.
Val No Returns the numbers contained
in a string as a numeric value of
appropriate type
Weekday No Returns an Integer value
containing a number representing
the day of the week
Year No Returns an Integer value from 1
through 9999 representing the
year
