Visual Basic for Applications (VBA) gives experienced Excel users a wide range of options for creating Excel spreadsheets and customizing how they look and function. Speaking of functions, the table following shows Excel VBA functions and what they accomplish.
| Function | What It Does |
|---|---|
| Abs | Returns the absolute value of a number |
| Array | Returns a variant that contains an array |
| Asc | Converts the first character of string to its ASCII value |
| AscB | Converts the first byte of the first character of a string to its character code value |
| AscW | Converts the first character of a string to its Unicode character code |
| Atn | Returns the arctangent of a number |
| CallByName | Get or set a property or invoke a method |
| CBool | Converts an expression to boolean |
| CByte | Converts an expression to byte data type |
| CCur | Converts an expression to currency data type |
| CDate | Converts an expression to date data type |
| CDbl | Converts an expression to double data type |
| CDec | Converts an expression to decimal data type |
| Choose | Selects and returns a value from a list of arguments |
| Chr | Converts an ANSI value to a string |
| ChrB | Returns a single-byte character associated with a specific character code |
| ChrW | Returns a Unicode character string associated with a specific character code |
| CInt | Converts an expression to integer data type |
| CLng | Converts an expression to long data type |
| Command | Returns the argument portion of the command line used to launch an application |
| Cos | Returns the cosine of a number |
| CreateObject | Creates an OLE Automation object |
| CSng | Converts an expression to single data type |
| CStr | Converts an expression to string data type |
| CurDir | Returns the current directory path |
| CVar | Converts an expression to variant data type |
| CVErr | Returns a user-defined error number |
| Date | Returns the current system date |
| DateAdd | Returns a date with a specific date interval added to it |
| DateDiff | Returns a date with a specific date interval subtracted from it |
| DatePart | Returns an integer containing a specific part of a date |
| DateSerial | Converts a date to a serial number |
| DateValue | Converts a string to date |
| Day | Returns the day of the month of a date |
| DDB | Returns the depreciation of an asset for a specific time period using the double-declining balance method |
| 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 a string associated with an operating system environment variable |
| EOF | Returns True if the end of a text file has been reached |
| Error | Returns the error message the corresponds to an error number |
| Exp | Returns the base of the 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 larger array based on filtering criteria |
| Fix | Returns the integer portion of a number |
| Format | Displays an expression in a particular format |
| FormatCurrency | Returns a number as a string, formatted as currency |
| FormatDateTime | Returns a number as a string, formatted as a date and/or time |
| FormatNumber | Returns a number as a formatted string |
| FormatPercent | Returns a number as a string, formatted as a percentage |
| FreeFile | Returns the next file number available for use by the Open statement |
| FV | Returns the future value of an annuity based on periodic, fixed payments and a fixed interest rate |
| GetAll | Returns a list of key settings and their values (originally Settings created with SaveSetting) from an application’s entry in the Windows registry |
| GetAttr | Returns a code representing a file attribute |
| GetObject | Retrieves an OLE Automation object from a file |
| GetSetting | Returns a key setting value from an application’s entry in the Windows registry |
| Hex | Converts from decimal to hexadecimal |
| Hour | Returns the hour of a time |
| Iif | Returns one of two parts, depending on the evaluation of an expression |
| Input | Returns a specific number of characters from an open text file |
| InputB | Returns a specific number of bytes from an open text file |
| InputBox | Displays a box to prompt a user for input |
| InStr | Returns the position of a string within another string |
| InStrB | Returns the byte position of a string within another string |
| InStrRev | Returns the position of a string within another string, beginning at the back end of the string |
| Int | Returns the integer portion of a number |
| Ipmt | Returns the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate |
| IRR | Returns the internal rate of return for a series of periodic 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 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 no valid data |
| IsNumeric | Returns True if an expression can be evaluated as a number |
| IsObject | Returns True if an expression references an OLE Automation object |
| Join | Returns a string created by joining a number of substrings contained in an array |
| LBound | Returns the lower bound of an array |
| LCase | Returns a string converted to lowercase |
| Left | Returns a specified number of characters from the left of a string |
| LeftB | Returns a specified number of bytes from the left of a string |
| Len | Returns the length of a string, in characters |
| LenB | Returns the length of a string, in bytes |
| 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 |
| MidB | Returns a specified number of bytes from a string |
| Minute | Returns the minute of a time |
| MIRR | Returns the internal rate of return for a series of periodic cash flows (using different rates) |
| Month | Returns the month of a date |
| MonthName | Returns a string indicating the specified month |
| MsgBox | Displays a modal message box |
| Now | Returns the current system date and time |
| NPer | Returns the number of periods for an annuity based on periodic, fixed payments and a fixed interest rate |
| NPV | Returns the net present value of an investment based on a series of periodic cash flows and a discount rate |
| Oct | Converts from decimal to octal |
| Partition | Returns a string variant indicating where a number occurs in a calculated series of ranges |
| Pmt | Returns the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate |
| PPmt | Returns the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate |
| PV | Returns the present value of an annuity based on periodic, fixed payments to be paid in the future and a fixed interest rate |
| QBColor | Returns the RGB color code corresponding to the specified color number (used for compatibility with Quick Basic) |
| Rate | Returns the interest rate per period for an annuity |
| Replace | Returns a string where one substring has been replaced with another |
| RGB | Returns a number representing an RGB color value |
| SLN | Returns the straight-line depreciation of an asset for a single period |
| Space | Returns a string with a specified number of spaces |
| Spc | Position output in an output stream |
| Split | Returns an array consisting of a number of substrings |
| Sqr | Returns the square root of a number |
| Str | Returns a string representation of a number |
| Right | Returns a specified number of characters from the right of a string |
| RightB | Returns a specified number of bytes from the right of a string |
| Rnd | Returns a random number between 0 and 1 |
| Round | Rounds a number to a specific number of decimal places |
| RTrim | Returns a copy of a string with no trailing spaces |
| Second | Returns the second of a time |
| 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 |
| StrComp | Returns a value indicating the result of a string comparison |
| StrConv | Returns a string variant converted as specified |
| String | Returns a repeating character or string |
| StrReverse | Reverses the character order of a string |
| Switch | Evaluates a list of expressions and returns a value associated with the first expression in the list that is True |
| SYD | Returns the sum-of-years’ digits depreciation of an asset for a specified |
| Tab | period |
| 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 containing a copy of a specified string without leading spaces and trailing spaces |
| TypeName | Returns a string that describes the data type of a variable |
| UBound | Returns the upper bound of an array |
| UCase | Converts a string to uppercase |
| Val | Returns the numbers contained in a string |
| VarType | Returns a value indicating the subtype of a variable |
| Weekday | Returns a number representing a day of the week |
| Year | Returns the year of a date |
Additional
Replace(string,find,replacewith[,start[,count[,compare]]])
- string, 必需的参数。要搜索替换输入字符串。
- find, 必需的参数。字符串的一部分将被替换。
- replacewith, 必需的参数。替换串，这将被替换针对查找的参数。
- start, 一个可选的参数。指定从其中所述串具有要被搜索和替换的开始位置。默认值是1。
- count, 一个可选的参数。指定次数的置换设有要执行的次数。
- compare, 一个可选的参数。指定要使用的比较方法。默认值为0。
- 0 = vbBinaryCompare – 执行二进制比较
- 1 = vbTextCompare – 执行文本比较
REDIM
Sub test()
Dim arr()
Dim i, j As Integer
j = Range("A65536").End(xlUp).Row - 1
ReDim arr(1 To j)
For i = 1 To j
arr(i) = Range("D"&i+1)+Range("E"&i+1)+Range("F"&i+1)
Next
Range("I3")=Application.WorksheetFunction.Max(arr)
Range("I2")=Range("C"&Application.WorksheetFunction.Match(Range("I3"),arr,0)+1)
End SubLBound, UBound
分别返回 数 组 可用的最小下 标 和最大下标。也可以返回第二维，第三维的下标情况。默认为 LBound( array , 1 ) 。
Join(List[,delimiter])
返回一个包含数组中指定数量的子串的字符串。这是Split方法的一个完全相反的功能。
- List – 必需的参数。包含要连接的子字符串的数组。
- Delimiter – 一个可选参数。该参数用于根据分隔符转换为数组。
Private Sub Constant_demo_Click()
' Join using spaces
a = array("Red","Blue","Yellow")
b = join(a)
msgbox("The value of b " & " is :" & b)
' Join using $
b = join(a,"$")
msgbox("The Join result after using delimiter is : " & b)
End Sub
当执行上面的函数时，它会产生下面的输出。
The value of b is :Red Blue Yellow
The Join result after using delimiter is : Red$Blue$Yellow