吉林师范大学：《VBA》课程电子教案（PPT教学课件）第五章 VBA内部函数

第五章VBA内部函数 5.1数学函数 5.6文件操作函数 5.2数组函数 5.7输入输出函数 53字符串操作函数5.8财会类函数 4旦期和时间函数5.9格式化数据 55数据类型检查与5.10系统与对象函数 拖函

第五章 VBA内部函数 5. 6 文件操作函数 5. 7 输入输出函数 5. 8 财会类函数 5. 9 格式化数据 5. 10 系统与对象函数 5．1 数学函数 5. 2 数组函数 5. 3 字符串操作函数 5. 4 日期和时间函数 5. 5 数据类型检查与 转换函数

数学函数 1. Abs(number) 9. Oct(number) 2. Atn(number) 10. Rnd[(number) 3. Cos(number) 11. Round(expression 4. Exp(number) Lnumdecimalplaces]) 5. Fix(number) 12. Sgn(number) 6. Hex(number) 13. Sin(number) 7. Int(number) 14. Sqr(number) 8. Log(number) 15. Tan(number) 单

2．Atn(number) 3．Cos(number) 4．Exp(number) 5．Fix(number) 6．Hex(number) 7．Int(number) 8．Log(number) 5 ．1 数学函数 1．Abs(number) 10．Rnd[(number)] 11．Round(expression [,numdecimalplaces]) 12．Sgn(number) 13．Sin(number) 14．Sqr(number) 15．Tan(number) 9．Oct(number)

5.2数组函数 1. Arrayarglist) 2. Filter(sourcesrray, match Include compare]) 3. LBound(arrayname, dimension) 4. Split(expressioni delimiter limitL, compareD 5. UBound(arraynamel, dimension]) 返国金单②

1．Array(arglist) 2．Filter(sourcesrray, match [,include[, compare]]) 3．LBound(arrayname[, dimension]) 4．Split(expression[, delimiter[, limit[, compare]]]) 5．UBound(arrayname[, dimension]) 5 . 2 数组函数

5.3字符串操作函数 1.InStr([start, ]string1 a10. Right(string, length) string2, compared) 11. RTrim(string) 2. InstrRevstringcheck, 12. Space(number) stringmatchL startt compare])13. StrComp(string1 3. Join(sourcearrayL delimiter]) string2[, compareD) 4. LCasel string) 14. StrConv(string 5. Left(string, length) conversion, LCID) 6. Len(string I varname) 15. StrReverse( expression) 7. LTrim(string) 16. String(number, 8. Mid(string, startI, length) I /r /7.character). 9. Replace(expression, find, 17. Trim(string replace, start, countY, compare]l 18. CAse(string

5 . 3 字符串操作函数 1．InStr([start, ]string1, string2[, compare]) 2．InstrRev(stringcheck, stringmatch[, start[, compare]]) 3．Join(sourcearray[, delimiter]) 4．LCase(string) 5．Left(string, length) 6．Len(string | varname) 7．LTrim(string) 8．Mid(string, start[, length]) 9．Replace(expression, find, replace[, start[, count[, compare]]]) 10．Right(string, length) 12．Space(number) 13．StrComp(string1, string2[, compare]) 14．StrConv(string, conversion, LCID) 15．StrReverse( expression) 11．RTrim(string) 16．String(number, character) 17．Trim(string) 18．UCase(string)

5.4日期和时间函数 1. Date 12. Now 2. DateAdd(interval, number, date)13. Second(time) 3. Date Diff(interval, date1, date2L, 14. Time firstdayofweekL, firstweekofyear]) 5. Timer 4. DatePart(interval, dateL firstday- 16. Time Serial(hour, ofweekl, firstweekofyear]) minute, second) 5. Date Serialyear, month, day) 17. Time Value(time) 6. Date Value(date) 18. Weekday(date, 7. Day(date) Ifirstdayofweekl) 8. Hour(time) 19. Weekday Name weekday, 9. Minute(time) abbreviate, firstdayofweek 10. Month(date) 20. Year(date) 11. MonthName(monthL abbreviated) 返国单②

5 . 4 日期和时间函数 1．Date 2．DateAdd(interval, number, date) 3．DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]]) 4．DatePart(interval,date[,firstday￾ofweek[, firstweekofyear]]) 5．DateSerial(year, month, day) 6．DateValue(date) 7．Day(date) 8．Hour(time) 9．Minute(time) 10．Month(date) 11．MonthName(month[, abbreviate]) 12．Now 13．Second(time) 14．Time 15．Timer 16．TimeSerial(hour, minute, second) 17．TimeValue(time) 18．Weekday(date, [firstdayofweek]) 19．WeekdayName(weekday, abbreviate, firstdayofweek) 20．Year(date)

5.5数据类型检查与 转换函数 1. Asc(string) 10. IsMissing(argname) 2.类型转换 11.IsNull(expression) 3. Choose(index, choice-1L 12. IsNumeric(expression) choice-2 L, choice-n]])13. IsObject(identifier) 4. Chr(charcode) 5. f(expr, truepart 14. Partition(number, start, stop, interval) falsepart) 15. Str(number) 6. IsArray (varname) 16. Switch(expr-1, value-1L, expr-2 7. IsDate(expression) value-2L, expr-n, value-nI 8. IsEmptylexpression) 17. Type Name(varname) 9. IsError(expression) 18. Val(string) 19. VarType(varname) 单

6．IsArray(varname) 5 . 5 数据类型检查与 转换函数 7．IsDate(expression) 8．IsEmpty(expression) 9．IsError(expression) 10．IsMissing(argname) 11．IsNull(expression) 1．Asc(string) 2．类型转换 3．Choose(index, choice-1[, choice-2, ... [, choice-n]]) 4．Chr(charcode) 5．IIf(expr, truepart, falsepart) 12．IsNumeric(expression) 13．IsObject(identifier) 14．Partition(number, start, stop, interval) 15．Str(number) 16．Switch(expr-1, value-1[, expr-2, value-2 _ [, expr-n,value-n]]) 17．TypeName(varname) 18．Val(string) 19．VarType(varname)

5.6文件操作函数 1. CurDir[(drive) 7. FreeFilet(rangenumber)] 2. DirI(pathname, attributes])] 8. GetAttr(pathname 3. EOF(filenumber) 9. Loc(filenumber) 4. FileAttr(filenumber, returntype) 5. File Date Time(pathname) 10. LOF(filenumber) 6. FileLen(pathname) 11. Seek(filenumber) 单②

5 . 6 文件操作函数 1．CurDir[(drive)] 2．Dir[(pathname[, attributes])] 3．EOF(filenumber) 4．FileAttr(filenumber, returntype) 5．FileDateTime(pathname) 6．FileLen(pathname) 7．FreeFile[(rangenumber)] 8．GetAttr(pathname) 9．Loc(filenumber) 11．Seek(filenumber) 10．LOF(filenumber)

5.7输入输出函数 1.Input(number, f]filenumber) 2. InputBox(promptl, title [, default L xpos]I, ypos]L, helpfile, context]) 3. MsgBox(promptL, buttons L, title][, helpfile, context]) 4. Spc(n) 5. Tab[(n)] 返国金单②

5 . 7 输入输出函数 1．Input(number, [#]filenumber) 2．InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context]) 3．MsgBox(prompt[, buttons] [, title] [, helpfile, context]) 4．Spc(n) 5．Tab[(n)]

5.8财会类函数 1.pB(cost, salvage, NPV(rate, values() life, period[, factorl) 8. Pmt(rate, nper, p v, fv, typel 2. FV(rate, nper, pmt, pvl, type]l) 3. Pmt(rate, per, nper, 9. PPmt(rate, per, nper, pvL, fvl, type]l pvI, fvt, type] 4. IRR(ValuesOl, guess])10. PV(rate, nper, pmt[, fvf, typel) 5. MIRR(values, finance rate 11. Rate(nper, pmt, pV reinvest rate) L, fvi, type, guess]] 6. NPer(rate, pmt, 12. SLN(cost, salvage, life) pvL, fv, typel) 13. SYD(cost, salvage, life, period 返国金单②

5 . 8 财会类函数 1．DDB(cost, salvage, life, period[, factor]) 2．FV(rate, nper, pmt[, pv[, type]]) 3．IPmt(rate, per, nper, pv[, fv[, type]]) 4．IRR(values()[, guess]) 5．MIRR(values(), finance_rate, reinvest_rate) 6．NPer(rate, pmt, pv[, fv[, type]]) 7．NPV(rate, values()) 8．Pmt(rate, nper, pv[, fv[, type]]) 9．PPmt(rate, per, nper, pv[, fv[, type]]) 10．PV(rate, nper, pmt[, fv[, type]]) 11．Rate(nper, pmt, pv [, fv[, type[, guess]]]) 12．SLN(cost, salvage, life) 13．SYD(cost, salvage, life, period)

5.9格式化数据 Format(expression, format, firstdayofweekl, firstweekofyear]]) 2. FormatCurrency( Expression LNumDigits AfterDecima GIncludeLeadingDigitL, UseParensForNegativeNumbers [ GroupDigits]] 3. FormatDateTime(DateL, NamedFormat]) 4. FormatNumber(Expression GNumDigits AfterDecimalL, IncludeLeadingDigit L UseParensForNegativeNumbers[ GroupDigits]1) 5. FormatPercent(ExpressionLN umDigitsAfterDecimal GIncludeLeadingDigit l, UseParensForNegative Numbers L GroupDigits]l1) 返国金单②

5 . 9 格式化数据 1．Format(expression[, format[, firstdayofweek[, firstweekofyear]]]) 2．FormatCurrency(Expression [,NumDigitsAfterDecimal [,IncludeLeadingDigit[, UseParensForNegativeNumbers [,GroupDigits]]]]) 3．FormatDateTime(Date[,NamedFormat]) 4．FormatNumber(Expression [,NumDigitsAfterDecimal[,IncludeLeadingDigit [, UseParensForNegativeNumbers[,GroupDigits]]]]) 5．FormatPercent(Expression[,N umDigitsAfterDecimal [,IncludeLeadingDigit [, UseParensForNegativeNumbers [,GroupDigits]]]])