VBA 开发中遇到的一些问题记录

1. 当前sheet中定义一个数组：Dim DataSource() As String
2. 判断动态数组是否初始化：Private Declare Function SafeArrayGetDim Lib "oleaut32.dll" (ByRef saArray() As Any) As Long

使用：If SafeArrayGetDim(SelectedItems) <> 0 Then

1. Excel初始化时执行函数：Private Sub Workbook_Open()
2. 链接数据库读取数据：

Dim conn As New ADODB.Connection

    Dim rs As New ADODB.Recordset

    Dim cmd As New ADODB.Command

Dim sqlText As String

 

conn.Open "Connection String"

cmd.ActiveConnection = conn

cmd.CommandType = adCmdText

sqlText = "SELECT DISTINCT ult_parent_name FROM pm_own.hy_mastesure_sdb e, WHERE e.comp_sec_type_code NOT IN ('ABS','TSY') ORDER BY ult_parent_name "

cmd.CommandText = sqlText

   

Set rs = cmd.Execute

   

Do While Not rs.EOF

    ReDim Preserve DataSource(0 To Row)   ‘动态数组重定义

    DataSource(Row) = rs.Fields(0).Value

    Row = Row + 1

rs.MoveNext

Loop

conn.Close

 

1. 创建带参数带返回值方法：

 

Private Function FilterDataSource(ByVal theText As String) As Variant

Dim dataArr As Variant

    Dim searchText As String

    Dim searchArr As Variant

    dataArr = DataSource

   

    If theText <> "" Then

        searchText = StrConv(theText, vbUpperCase)

        searchArr = Split(searchText, " ")

        For Each searchItem In searchArr

            If searchItem <> "" Then

               dataArr = VBA.Filter(dataArr, searchItem, True)

            End If

        Next searchItem

    End If

    FilterDataSource = dataArr

End Function

 

1. KeyDown事件定义：

Private Sub tbxUltPatent_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

KeyCode=38     ‘up

KeyCode=40     ‘down

KeyCode=13      ‘Enter

1. .ForeColor = &HFF&
2. ItemClick事件：Private Sub ListView41_ItemClick(ByVal Item As MSComctlLib.ListItem)
3. 清除sheet所有表单内容：ActiveSheet.Range("1:65536").ClearContents
4. Range内容转换为数组：

Dim data As Worksheet

Set data = Sheets("Sheet2")

Dim dataArr As Variant

dataArr = WorksheetFunction.Transpose(data.Range("A1:A3432"))

1. 判断数组是否越界：If LBound(initData) <= UBound(initData) Then
2. 截取字符串中某一段：sqlTextName = Right(sqlTextName, Len(sqlTextName) - 3)