VBA中如何获取ListView水平滚动条位置及点击列？

我希望这个问题有简单的解决方案，但搜索后只找到C#和VB.NET的答案，无法转换为纯VBA代码。我在用户窗体中使用报表格式（类似Excel表格）的ListView，数据超出窗口范围，需要用到水平和垂直滚动条。我想对ListView中的特定单元格执行操作，通过鼠标按下事件检测点击位置并返回单元格值，用Listview1.HitTest可以获取选中行，但获取选中列比较棘手。以下代码在水平滚动条处于最左侧时能捕获选中列：

Private Function GetSelectedCol(listview As Object, x As stdole.OLE_XPOS_PIXELS, lngXPixelsPerInch As Long)
    Dim col As Variant
    Dim colX As Long
    colX = 0
    Dim offset As Long
    offset = GotHorizontalScrollPosition()
    For Each col In listview.columnHeaders
        colX = colX + col.Width * 2
        If x + offset <= colX Then
            GetSelectedCol = col.index - 1
            Exit Function
        End If
    Next col
End Function

问题在于当前GotHorizontalScrollPosition()是返回0的虚拟函数，试过相关方案但没用，现提供两种纯VBA解决方案：

方法一：通过Windows API获取水平滚动位置并计算列索引 #

1. 声明API及常量 #

在用户窗体代码模块顶部添加以下内容（32位Office将LongPtr替换为Long）：

Private Declare PtrSafe Function SendMessage Lib "user32.dll" Alias "SendMessageA" ( _
    ByVal hwnd As LongPtr, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    lParam As Any _
) As LongPtr

Private Const LVM_FIRST As Long = &H1000
Private Const LVM_GETSCROLLPOS As Long = LVM_FIRST + 22

Private Type POINTAPI
    x As Long
    y As Long
End Type

2. 实现滚动位置获取函数 #

替换原虚拟函数：

Private Function GotHorizontalScrollPosition(listview As ListView) As Long
    Dim scrollPos As POINTAPI
    SendMessage listview.hwnd, LVM_GETSCROLLPOS, 0, scrollPos
    GotHorizontalScrollPosition = scrollPos.x
End Function

3. 修正列索引计算函数 #

Private Function GetSelectedCol(listview As ListView, x As stdole.OLE_XPOS_PIXELS) As Long
    Dim col As ColumnHeader
    Dim colX As Long
    Dim offset As Long
    offset = GotHorizontalScrollPosition(listview)
    
    colX = 0
    For Each col In listview.ColumnHeaders
        ' 将缇转换为像素（适配屏幕DPI）
        colX = colX + (col.Width * 96) / 1440
        If (x + offset) <= colX Then
            GetSelectedCol = col.Index - 1
            Exit Function
        End If
    Next col
    GetSelectedCol = listview.ColumnHeaders.Count - 1
End Function

4. 鼠标点击事件调用 #

Private Sub ListView1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
    Dim hitInfo As ListItem
    Dim colIndex As Long
    
    Set hitInfo = ListView1.HitTest(x, y)
    If Not hitInfo Is Nothing Then
        colIndex = GetSelectedCol(ListView1, x)
        MsgBox "点击单元格值：" & hitInfo.SubItems(colIndex) & vbCrLf & "行索引：" & hitInfo.Index - 1 & " 列索引：" & colIndex
    End If
End Sub

方法二：直接用API获取点击的行和列（更可靠） #

此方法无需手动计算滚动偏移，API会自动处理滚动后的位置：

1. 添加额外API声明和结构体 #

Private Const LVM_SUBITEMHITTEST As Long = LVM_FIRST + 57

Private Type LVHITTESTINFO
    pt As POINTAPI
    flags As Long
    iItem As Long
    iSubItem As Long
End Type

2. 实现单元格点击信息获取函数 #

Private Sub GetClickedCell(listview As ListView, x As Long, y As Long, ByRef itemIndex As Long, ByRef subItemIndex As Long)
    Dim hitInfo As LVHITTESTINFO
    
    hitInfo.pt.x = x
    hitInfo.pt.y = y
    SendMessage listview.hwnd, LVM_SUBITEMHITTEST, 0, hitInfo
    
    itemIndex = hitInfo.iItem
    subItemIndex = hitInfo.iSubItem
End Sub

3. 鼠标点击事件调用 #

Private Sub ListView1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
    Dim itemIdx As Long, subItemIdx As Long
    
    GetClickedCell ListView1, x, y, itemIdx, subItemIdx
    If itemIdx <> -1 Then
        If subItemIdx = 0 Then
            MsgBox "点击单元格值：" & ListView1.ListItems(itemIdx + 1).Text & vbCrLf & "行：" & itemIdx & " 列：0"
        Else
            MsgBox "点击单元格值：" & ListView1.ListItems(itemIdx + 1).SubItems(subItemIdx) & vbCrLf & "行：" & itemIdx & " 列：" & subItemIdx
        End If
    End If
End Sub

内容的提问来源于stack exchange，提问作者user1357607