VBA使用总结（3）

 □VBA基本类型转化

Val("&HFF") => 255

hex(255) => "FF"

LCase(hex(255)) => "ff"

Asc("a") => 97

Chr(97) => a

CStr(97) => "97"

 

□InStr 函数 : 一字符串在另一字符串中最先出?的位置。

InStr([start, ]string1, string2[, compare])

如果       InStr返回

string1 为零长度              0

string1 为Null         Null

string2 为零长度              Start

string2 为Null         Null

string2 找不到             0

在 string1 中找到string2    找到的位置

start > string2        0

 

更多使用方法参考【Microsoft Visual Basic 帮助】

 

□遍历字符串的每个字符

Dim str As String

Dim i As Integer

str = "test"

Dim ch As String

For i = 1 To Len(str)

   ch = Mid(str, i, 1)

   'todo with ch

Next i

 

□数组的起始和结束索引

Dim A(1 To 100) As String

LBound(A) => 1

UBound(A) => 100

 

注：若定义数组时，参数是变量，则应使用ReDim来定义数组

Dim max As Integer

max = 9

ReDim arr(0 To max) As Integer  ‘定义一个长度为10的数组

 

□集合(Collection)与数组

集合的索引从1开始

数组的索引从0开始

Dim coll

Set coll = New Collection ‘定义一个集合，索引从1开始

Dim arr(10) As Integer ‘定义一个长度为11的数组，索引从0开始

 

□使用字典（类似Java的HashMap）

Dim m

Set m = CreateObject("Scripting.Dictionary")

m.Add “key” “value”

m. Exists(“key”) ‘指定的关键字存在，返回 True，若不存在，返回 False

 

□通过Shell启动进程，并等待其结束。

Public Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long

Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Public Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long

Public Const INFINITE = -1&

Public Const SYNCHRONIZE = &H100000

 

Dim i As Long

Dim p As Long

i = Shell("cmd /c ipconfig > c:\test.log", vbHide)

p = OpenProcess(SYNCHRONIZE, False, i)

WaitForSingleObject p, INFINITE

CloseHandle p

 

□异常的重新包装

Public Function doSth() As Boolean

On Error GoTo ErrorHandler

    …

    If condition_1 Then

        err.Raise 10001, , "error_message_1"

    End If

    …

    If condition_1 Then

        err.Raise 10002, , "error_message_2"

    End If

    …

    Exit Function

ErrorHandler:

    err.Raise err.Number, err.Source, "error_message"

End Function

 

 

□读文件

Dim file_name As String

file_name = "c:\test.log"

Dim fnum As Integer

fnum = FreeFile()

Open file_name For Input As #fnum

While Not EOF(fnum)

    Line Input #fnum, strLine

    'todo with strLine

Wend

Close #fnum

 

 

□创建建并写文件

Dim tmpFile As Object

Dim FSO As Object

 

Set FSO = CreateObject("Scripting.FileSystemObject")

Set tmpFile = FSO.CreateTextFile("c:\tmp.txt", True)

tmpFile.WriteLine ("line1" + vbCrLf + "line2")

 

Set tmpFile = Nothing

Set FSO = Nothing

 

□通过ODBC连接接数据及事务处理

       Dim conn As ADODB.Connection

       Set conn = New ADODB.Connection

       conn.ConnectionTimeout = 1 '1min

       conn.ConnectionString = "DSN=db_name;Uid=db_user;Pwd=db_pwd" ←DSN通过Windows ODBC数据源管理器配置。

       conn.Open

       conn.BeginTrans

On Error GoTo ErrorHandler

       conn.Execute insert_sql

       conn.Execute delete_sql

       conn.Execute update_sql

       conn.CommitTrans

       conn.Close

ErrorHandler:

       conn.RollbackTrans

       conn.Close

 

※ 使用Oracle DB_Link时，

若采用的驱动为Oracle in OraDb10g_home1时，连接字符串为

conn.ConnectionString = "DSN=db_name;Uid=db_user;Pwd=db_pwd;Omit Oracle Connection Name=True"

 

若采用的驱动为Microsoft ODBC for Oracle时，连接字符串为

conn.ConnectionString = "DSN=db_name;Uid=db_user;Pwd=db_pwd; "