UEU-co logo

Invoking Excel Functions in VBA Instructions


Previous Page

Next Page

Invoking Excel Functions in VBA Instructions

If a VBA function that’s equivalent to one you use in Excel is not available, you can use Excel’s worksheet functions directly in your VBA code. Just precede the function with a reference to the WorksheetFunction object. For example, VBA does not have a function to convert radians to degrees. Because Excel has a worksheet function for this procedure, you can use a VBA instruction such as the following:

Deg = Application.WorksheetFunction.Degrees(3.14)

The WorksheetFunction object was introduced in Excel 97. For compatibility with earlier versions of Excel, you can omit the reference to the WorksheetFunction object and write an instruction such as the following:

Deg = Application.Degrees(3.14)

Note 

There are no new VBA functions in Excel 2007.

Table B-2: SUMMARY OF VBA FUNCTIONS
Open table as spreadsheet

Function

Action

Abs

Returns the absolute value of a number

Array

Returns a variant containing an array

Asc

Converts the first character of a string to its ASCII value

Atn

Returns the arctangent of a number

CallByName

Executes a method, or sets or returns a property of an object

CBool

Converts an expression to a Boolean data type

CByte

Converts an expression to a Byte data type

CCur

Converts an expression to a Currency data type

CDate

Converts an expression to a Date data type

CDbl

Converts an expression to a Double data type

CDec

Converts an expression to a Decimal data type

Choose

Selects and returns a value from a list of arguments

Chr

Converts a character code to a string

CInt

Converts an expression to an Integer data type

CLng

Converts an expression to a Long data type

Cos

Returns the cosine of a number

CreateObject

Creates an Object Linking and Embedding (OLE) Automation object

CSng

Converts an expression to a Single data type

CStr

Converts an expression to a String data type

CurDir

Returns the current path

CVar

Converts an expression to a variant data type

CVDate

Converts an expression to a Date data type (for compatibility, not recommended)

CVErr

Returns a user-defined error value that corresponds to an error number

Date

Returns the current system date

DateAdd

Adds a time interval to a date

DateDiff

Returns the time interval between two dates

DatePart

Returns a specified part of a date

DateSerial

Converts a date to a serial number

DateValue

Converts a string to a date

Day

Returns the day of the month of a date

DDB

Returns the depreciation of an asset

Dir

Returns the name of a file or directory that matches a pattern

DoEvents

Yields execution so the operating system can process other events

Environ

Returns an operating environment string

EOF

Returns True if the end of a text file has been reached

Error

Returns the error message that corresponds to an error number

Exp

Returns the base of natural logarithms (e) raised to a power

FileAttr

Returns the file mode for a text file

FileDateTime

Returns the date and time when a file was last modified

FileLen

Returns the number of bytes in a file

Filter

Returns a subset of a string array, filtered

Fix

Returns the integer portion of a number

Format

Displays an expression in a particular format

FormatCurrency

Returns an expression formatted with the system currency symbol

FormatDateTime

Returns an expression formatted as a date or time

FormatNumber

Returns an expression formatted as a number

FormatPercent

Returns an expression formatted as a percentage

FreeFile

Returns the next available file number when working with text files

FV

Returns the future value of an annuity

GetAllSettings

Returns a list of settings and values from the Windows Registry

Table B-2: SUMMARY OF VBA FUNCTIONS
Open table as spreadsheet

Function

Action

GetAttr

Returns a code representing a file attribute

GetObject

Retrieves an OLE Automation object from a file

GetSetting

Returns a specific setting from the application’s entry in the Windows Registry

Hex

Converts from decimal to hexadecimal

Hour

Returns the hour of a time

IIf

Evaluates an expression and returns one of two parts

Input

Returns characters from a sequential text file

InputBox

Displays a box to prompt a user for input

InStr

Returns the position of a string within another string

InStrRev

Returns the position of a string within another string from the end of the string

Int

Returns the integer portion of a number

IPmt

Returns the interest payment for a given period of an annuity

IRR

Returns the internal rate of return for a series of cash flows

IsArray

Returns True if a variable is an array

IsDate

Returns True if a variable is a date

IsEmpty

Returns True if a variable has not been initialized

IsError

Returns True if an expression is an error value

IsMissing

Returns True if an optional argument was not passed to a procedure

IsNull

Returns True if an expression contains a Null value

IsNumeric

Returns True if an expression can be evaluated as a number

IsObject

Returns True if an expression references an OLE Automation object

Join

Combines strings contained in an array

LBound

Returns the smallest subscript for a dimension of an array

LCase

Returns a string converted to lowercase

Left

Returns a specified number of characters from the left of a string

Len

Returns the number of characters in a string

Loc

Returns the current read or write position of a text file

LOF

Returns the number of bytes in an open text file

Log

Returns the natural logarithm of a number

LTrim

Returns a copy of a string with no leading spaces

Mid

Returns a specified number of characters from a string

Minute

Returns the minute of a time

MIRR

Returns the modified internal rate of return for a series of periodic cash flows

Month

Returns the month of a date as a number

MonthName

Returns the month of a date as a string

MsgBox

Displays a modal message box

Now

Returns the current system date and time

NPer

Returns the number of periods for an annuity

NPV

Returns the net present value of an investment

Oct

Converts from decimal to octal

Partition

Returns a string representing a range in which a value falls

Pmt

Returns a payment amount for an annuity

Ppmt

Returns the principal payment amount for an annuity

PV

Returns the present value of an annuity

QBColor

Returns a red/green/blue (RGB) color code

Rate

Returns the interest rate per period for an annuity

Replace

Returns a string in which a substring is replaced with another string

RGB

Returns a number representing an RGB color value

Right

Returns a specified number of characters from the right of a string

Rnd

Returns a random number between 0 and 1

Round

Returns a rounded number

RTrim

Returns a copy of a string with no trailing spaces

Second

Returns the seconds portion of a specified time

Table B-2: SUMMARY OF VBA FUNCTIONS
Open table as spreadsheet

Function

Action

Seek

Returns the current position in a text file

Sgn

Returns an integer that indicates the sign of a number

Shell

Runs an executable program

Sin

Returns the sine of a number

SLN

Returns the straight-line depreciation for an asset for a period

Space

Returns a string with a specified number of spaces

Spc

Positions output when printing to a file

Split

Returns a one-dimensional array containing a number of substrings

Sqr

Returns the square root of a number

Str

Returns a string representation of a number

StrComp

Returns a value indicating the result of a string comparison

StrConv

Returns a converted string

String

Returns a repeating character or string

StrReverse

Returns a string, reversed

Switch

Evaluates a list of Boolean expressions and returns a value associated with the first True expression

SYD

Returns the sum-of-years’ digits depreciation of an asset for a period

Tab

Positions output when printing to a file

Tan

Returns the tangent of a number

Time

Returns the current system time

Timer

Returns the number of seconds since midnight

TimeSerial

Returns the time for a specified hour, minute, and second

TimeValue

Converts a string to a time serial number

Trim

Returns a string without leading spaces and/or trailing spaces

TypeName

Returns a string that describes the data type of a variable

UBound

Returns the largest available subscript for a dimension of an array

UCase

Converts a string to uppercase

Val

Returns the number formed from any initial numeric characters of a string

VarType

Returns a value indicating the subtype of a variable

Weekday

Returns a number indicating a day of the week

WeekdayName

Returns a string indicating a day of the week

Year

Returns the year of a date

Previous Page

Next Page

Leave a Reply


Time limit is exhausted. Please reload the CAPTCHA.

Categories

apply_now Pepperstone Group Limited