VBA Commands

1
VBA Commands

• 11-07-05

2
Types of Commands

• Statements
• Chdir(\mydocs)
• date
• Functions
• X Tan(angle)
• radomize
• Methods of built-in objects
• Debug.Print(something went wrong here)
• Err.Raise

3
Formatting Data

• Format(experession,format)
• Format (now,Long Date)
• Named Formats Numeric
• General number
• Currency
• Fixed (2 decimal places)
• Standard (thousands separators and 2 dec places)
• Percent
• Scientific

4
Formatting Data

• Boolean
• Yes/No
• True/False
• On/OFF

5
Formatting Data

• Date
• General Date
• Long Date
• Medium Date
• Short Date
• Time
• Long Time
• Medium Time
• Short Time

6
Specifying Custom Formats

• MyStr Format(5459.4, ",0.00") ' Returns
  "5,459.40".
• MyStr Format(334.9, "0.00") ' Returns
  "334.90".
• MyStr Format(5, "0.00") ' Returns "500.00".
• MyStr Format("HELLO", "lt") ' Returns "hello".
• MyStr Format("This is it", "gt") ' Returns "THIS
  IS IT".

7
Working with hex and octal values

• Precede each literal with a special code
• H for hex
• HB
• O for octal
• O12
• intBases 10 O12 HA

8
Converting between numbers and strings

• Asc(string) character code of first character in
  string
• Chr(charcode) character corresponding to charcode
• Hex(numString) converts numString to hex

9
String Functions

• InStr(Start,string1,string2) insert into string1,
  string2 at start
• Left(string,l) take l chars from left side of
  string
• Right(string,r) take r chars from right side of
  string
• Len(string) the numbers of chars in string
• LCase(string) converts all chars to lowercase

10
String Functions

• LTrim(string) removes all leading spaces
• Mid(string,start,length) take from the middle
• Oct(numStr)
• Right(strength,r) take r chars from the right
  side
• Rtrim(string) remove all trailing spaces
• Space(number) creates a string with the specified
  number of spaces

11
String Functions

• Split(string,delimiter) creates an array of
  string broken up by the delimiter
• StrComp(Str1,Str2) 0 if equal, -1 if Str1 is lt
  Str2, 1 if Str1 is gt Str2
• StrReverse(string)
• Trim(string) removes leading trailing blanks
• UCase(string) converts all to upper case

12
Working with Dates and Times

• Date()
• Day(date) returns integer corresponding to day of
  the month
• Hour(time) returns an integer between 0 and 23
• Minute(time) 0-59
• Month(date) 1-12
• MonthName(date)
• Now

13
Working with Dates and Time

• Second(time) whole number 0-59
• Time
• Timer(timevalue) number of seconds since midnight
• Weekday(date) 1-7
• Year(date)

14
Calculating with dates

• DateAdd(interval,number,datevalue)
• When DateAdd(s,-90,Now)
• DateDiff(interval,date1,date2)
• HowLong DateDiff(m,2/12/90,10/12/01)

15
DateAdd/DateDiff Arguements

• yyyy
• q
• m
• y (day of year)
• d
• w (weekday)
• ww (week)
• h (hour)
• n (minute)
• s (second)

16
Fancier MsgBoxex

• msgBox(promt ,buttons , title
• , helpfile , context)

17
VBA Button Constants

• vbOKOnly
• vbOKCancel
• vbAbortRetryIgnore
• vbYesNoCance
• vbYesNO
• vbRetryCancel

18
VBA Constants

• vbCritical the critical message icon
• vbQuestion warning query icon
• vbExclamation warning message icon
• vbInformation information message icon
• vbDefaultButton1 2,3,4
• Specifies the names button as the default
• msgBox(Pick a button, vbYesNO vbCritical
  vbDefaultButton3)

19
Math Functions

• Abs()
• Atn()
• Cos()
• Exp() e raises to power
• Int()
• Log()
• Rnd()
• Sin()
• Sqr() square root
• Tan()

20
Financial functions

• FV(rate, nper, pmt, pv) future value
• NPer(rate, pmt, pv) number of periods for an
  annuity
• NPV(rate, values()) net present value
• Pmt(rate, nper, pv) the payment for an annuity
• PV(rate, nper, pmt) present value
• Rate (nper, pmt, pv) the interest rate

21
Miscellaneous Build-In Commands

• ChDir(pathname)
• CurDir returns current path as string
• Filecopy(source,destination)
• MkDir pathname
• RmDir pathname
• Beep
• RBG(red,gree,blue) whole number of the mixture
  color
• Randomize, Rnd()