Search notes:

VBA functions

Conversion functions: asc, cBool, cByte, cCur, cDate, cDec, cDbl, chr, cInt, cLng, cLngLng, cLngPtr, cSng, cStr, cVar, cvErr, hex, oct, str, val.

array

array() creates an array and returns it as a variant.
option explicit

sub main() ' {

    dim ary as variant
    ary = array("zero", "one", "two", "three", "four", "five")

    dim i as long
    for i = lBound(ary) to uBound(ary)

        debug.print i & ": " & ary(i)

    next i

end sub ' }
'
' 0: zero
' 1: one
' 2: two
' 3: three
' 4: four
' 5: five
Github repository about-VBA, path: /functions/array.bas

chr and chrW

chr(n) converts the ascii code n to a (single character) string.
chrW(u) converts the Unicode codepoint u to a character.

choose

sub main()

   printNumber  2
   printNumber  1
   printNumber 99

end sub

sub printNumber(nr as integer)

    debug.print choose(nr, "one", "two", "three")

end sub
Github repository about-VBA, path: /functions/choose.bas
Compare with the select statement and the switch function.

date

date returns the current date. Compare with now

dateAdd

dateAdd(…) adds an interval to a point in time.

dateSerial

dateSerial(year, month, day) constructs a date from the three integers year, month and day.
See also timeSerial(hr, mi, ss).

environ

environ("ENVVAR") returns the value of the environment variable ENVVAR.

fix

fix(num) removes the fractional part of num.
fix(9.7) returns 9, fix(-9.7) returns -9, compare with int(num).
See also fix() in Access SQL to return the day of a date.

format

Gone here

freeFile

freeFile finds the next availabe number for open
option explicit

sub main() ' {

    dim fOne, fTwo, fThree as integer

    fOne   = openFile(environ("TEMP") & "\one.txt"  )
    fTwo   = openFile(environ("TEMP") & "\two.txt"  )
    fThree = openFile(environ("TEMP") & "\three.txt")
    
    print# fOne  , "This is file one"
    print# fTwo  , "This is file two"
    print# fThree, "This is file three"
    
    close# fOne
    close# fTwo
    close# fThree

end sub ' }

function openFile(fileName as string) ' {
  '
  ' Open a file, use freeFile to determine next
  ' file number
  '
  
    dim fn as integer
    
    fn = freeFile()
    open fileName for output as #fn
    
    openFile = fn

end function ' }
Github repository about-VBA, path: /functions/freeFile.bas
See also the VBA file class that encapsulates this function.

hex

hex converts a number into its hexadecimal representation.
The following snippet prints 42 in hex is 2A.
msgBox "42 in hex is " & hex(42)
The opposite, converting a hexadecimal representation to a number can be achieved with cLng("&h2A").

int

int(num) removes the fractional part of num. It can be used instead of the non-existing function floor.
int(9.7) returns 9, int(-9.7) returns -10, compare with fix(num)

inputBox

inputBox() lets the user enter a string.
Compare with Excel's Application.InputBox()

mid

mid() is the equivalent of the function which is called substr or similar in other programming languages.

now

now returns the current date and time as a date.
Compare with date

isEmpty

isEmpty can be used to determine if a variant was initialized. On other datatypes, this seems not possible (or probably more accurately, they are automatically initialized).
The following example displays that the variant variable is empty but the others are not.
sub testIsEmpty()

    dim varVariant as variant
    dim varLong    as long
    dim varObject  as object

    if isEmpty(varVariant) then
       debug.print "varVariant is empty"
    else
       debug.print "varVariant is not empty"
    end if

    if isEmpty(varLong) then
       debug.print "varLong    is empty"
    else
       debug.print "varLong    is not empty"
    end if

    if isEmpty(varObject) then
       debug.print "varObject  is empty"
    else
       debug.print "varObject  is not empty"
    end if

end sub
Github repository about-VBA, path: /functions/isX/isEmpty.bas
isEmpty is used in Excel to determine if a cell is empty and in dictionaries (scripting runtime) to check if a key is present.
See also null and nothing etc.

lBound

lBound and uBound

len

Determine the number of characters in a string.

lenB

lenB(var) returns the number of bytes a variable requires.

replace

replace() can be used to replace substrings in strings with another string.

rnd

rnd(…) returns a random number.

rtrim

rtrim removes trailing spaces on a string's right side:
sub main()

    dim txt as string
    txt = "     foo bar baz          "
    
    debug.print "txt: >" & txt & "<"
  
  '
  ' Traim trailing spaces on the right side:
  '  
    txt = rTrim(txt)
    
    debug.print "txt: >" & txt & "<"

end sub
Github repository about-VBA, path: /functions/rtrim.bas

shell

shell can be used to start a program (application).

strPtr

See here.

switch

see here.

timeSerial

timeSerial(hr, mi, ss) creates a time (of datatype date) whose hour is hr, minute is mi and second is ss.
See also dateSerial(yr, mm, dy).

timer

The function timer returns a single that contains the number of seconds (to two decimal places) since midnight.

typeName

typeName(var) returns a string according to the datatype of var.
See typeName()

typeOf … is className

The typeOf var is classname construct allows to check if a given variable is of a particular type.
See typeOf … is

uBound

lBound and uBound

varPtr

See here.

varType

varType returns a number that indicates the datatype of a variable.

Other functions

abs
array
asc and ascW.
atn
callByName gets or sets an object's property or executes an object's method. The name of the property or method are dynamic (i.e. might be stored in a variable.
command
cos
createObject: to create an Active X Object.
curDir() returns a variant whose underlying type is a string that stores the current directory. By default, this directory seems to be C:\Users\username\Documents.
cVErr
dDB
derived Math
dir
doEvents
eOF
error
exp
fileAttr
fileDateTime
fileLen
filter
formatCurrency
formatNumber
formatPercent
fV
getAllSettings
getAttr
getObject
getSetting: read a value from the registry that is stored below the key HKEY_CURRENT_USER\Software\VB and VBA Program Settings.
iif
iMEStatus
input
inStr searches for (sub-)strings in strings.
inStrRev
int, Fix
iPmt
iRR
isX(expr) functions check if expr can be recognized as a particular data type or falls in a set of data types with similar characteristics:
isError().
lCase
left
len
loc
lOF
log
lTrim, RTrim, and Trim
macID
macScript
math
mid
mIRR
msgBox
now
nPer
nPV
objPtr
oct
partition
pmt
pPmt
pV
qBColor
rate
RGB(r, g, b) creates a long value that represents a color.
right
rnd
round
seek
sgn
shell
sin
sLN
space
spc
sqr
str
strComp compare strings case sensitively, or case insensitevely.
strConv
string(len, char) creates string whose length is len and consists of the characters indicated by char.
strReverse
SYD: sum-of-years digits depreciation of an asset for a given period.
tab
tan
timeValue
uCase
val

See also

The PowerShell module VB allows to access functions that are found in Visual Basic.
Some operators are used similarly to functions, for example like.
VBA

Index