|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 sonicsnip 于 2023-3-28 17:13 编辑
最近刚开始学vba,经常用到ListView来展示数据,老是写一大堆代码来载入数据有些麻烦,于是写了一个通用的数据载入模块,分享给大家,并请大神批评指正。
'示例:
'把【客户】表中【类型】类为【经销商】的记录加载到【UserForm1.ListView1】中,并按关键字"张"进行模糊过滤
'需载入【客户名称,类型,地区,联系人,联系电话】五列,其中【客户名称】列宽+30,【联系人】列宽-20,
Sub test()
Head = "客户名称,类型,地区,联系人,联系电话" '用英文逗号分割
ColAd = "30,0,0,-20,0" '用英文逗号分割
Set SHT = Sheets("客户")
Set LVW = UserForm1.ListView1
FK = "张"
PK = "类型"
PVV = "经销商"
Call RefreshLVW(Head, ColAd, SHT, LVW, FK, PK, PVV)
End Sub
'========================================================================
'根据表头动态定位列号,仅支持表头在第一列
Function ColNo(SHT, HeadName) As Integer
ColNo = SHT.Range("1:1").Find(HeadName, lookat:=xlWhole).Column
End Function
'========================================================================
'ListView数据载入,支持精确匹配和模糊匹配,支持列宽调整(默认为等分)
Function RefreshLVW(HeadName, ColumnWidthAdjust, TargetSHT, TargetLVW, Optional FuzzyFilterKey = "", Optional ForceFiltColumnHead = "*", Optional ForceFilterKey = "*")
On Error Resume Next
TargetLVW.ColumnHeaders.Clear
TargetLVW.ListItems.Clear
HeadArr = Split(HeadName, ",")
ColAdArr = Split(ColumnWidthAdjust, ",")
For i = 0 To UBound(HeadArr) Step 1
TargetLVW.ColumnHeaders.Add i + 1, HeadArr(i), HeadArr(i), (TargetLVW.Width - 5) / (UBound(HeadArr) + 1) + ColAdArr(i), vwcolumnleft
Next i
MaxRow = TargetSHT.UsedRange.Rows.Count
MaxCol = TargetSHT.UsedRange.Columns.Count
For i = 2 To MaxRow Step 1
If TargetSHT.Cells(i, ColNo(TargetSHT, ForceFiltColumnHead)).Value Like ForceFilterKey Then
For j = 1 To MaxCol Step 1
If TargetSHT.Cells(i, j) Like "*" & FuzzyFilterKey & "*" Then
Set List = TargetLVW.ListItems.Add(Text:=TargetSHT.Cells(i, ColNo(TargetSHT, HeadArr(0))).Value)
For k = 1 To UBound(HeadArr) Step 1
Set li = List.ListSubItems.Add(Text:=TargetSHT.Cells(i, ColNo(TargetSHT, HeadArr(k))).Value)
Next k
Exit For
End If
Next j
End If
Next i
End Function
|
|