VBA 开发中遇到的一些问题记录
- 当前sheet中定义一个数组:Dim DataSource() As String
- 判断动态数组是否初始化:Private Declare Function SafeArrayGetDim Lib "oleaut32.dll" (ByRef saArray() As Any) As Long
使用:If SafeArrayGetDim(SelectedItems) <> 0 Then
- Excel初始化时执行函数:Private Sub Workbook_Open()
- 链接数据库读取数据:
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
- 创建带参数带返回值方法:
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
- KeyDown事件定义:
Private Sub tbxUltPatent_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
KeyCode=38 ‘up
KeyCode=40 ‘down
KeyCode=13 ‘Enter
- .ForeColor = &HFF&
- ItemClick事件:Private Sub ListView41_ItemClick(ByVal Item As MSComctlLib.ListItem)
- 清除sheet所有表单内容:ActiveSheet.Range("1:65536").ClearContents
- Range内容转换为数组:
Dim data As Worksheet
Set data = Sheets("Sheet2")
Dim dataArr As Variant
dataArr = WorksheetFunction.Transpose(data.Range("A1:A3432"))
- 判断数组是否越界:If LBound(initData) <= UBound(initData) Then
- 截取字符串中某一段:sqlTextName = Right(sqlTextName, Len(sqlTextName) - 3)