DataGrid 非繫結模式下任意捲動列存取任意儲存格值

問題

DataGrid 繫結 Recordset 時，Row 屬性限制在 [1, VisibleRows] (預設 18)。捲動後欲存取不可見列會報錯「列號無效」。

解決方案：利用 Scroll 事件程式化定位

Private Sub Command1_Click()
    Dim r As Integer, temp As String
    Randomize
    r = Int(Rnd * DataGrid1.ApproxCount + 1)  ' 隨機目標列
    
    ' 捲動至目標列第一欄
    DataGrid1.Scroll -DataGrid1.LeftCol, r - DataGrid1.FirstRow
    
    temp = InputBox("目前定位第 " & r & " 列第 1 欄，想移動到哪列、哪欄？", "提示", _
        Int(Rnd * 1000 + 1) & "," & Int(Rnd * DataGrid1.Columns.Count + 1))
    
    Dim targetRow As Integer, targetCol As Integer
    targetRow = Split(temp, ",")(0)
    targetCol = Split(temp, ",")(1)
    
    MsgBox "第 " & targetRow & " 列第 " & targetCol & " 欄的值為 " & _
        GetText(DataGrid1, targetRow, targetCol)
End Sub

Function GetText(ByVal dgrid As DataGrid, ByVal r As Integer, ByVal c As Integer) As String
    ' 從 FirstRow 開始捲動 r - FirstRow 列
    dgrid.Scroll 0, r - dgrid.FirstRow
    dgrid.Col = c - 1      ' 目標欄 (0-based)
    dgrid.Row = 0          ' 可見區域第一列 (0-based)
    GetText = dgrid.Text   ' 取值
End Function

Form_Load 完整測試資料建立

Private Sub Form_Load()
    Dim adoRecordset As New ADODB.Recordset
    Dim i As Integer, j As Integer
    
    ' 建立 6 個欄位
    For i = 0 To 5
        adoRecordset.Fields.Append "Col" & i + 1, adVariant, , _
            adFldMayBeNull Or adFldIsNullable Or adFldUpdatable
    Next
    
    With adoRecordset
        .Open
        For j = 1 To 1000
            .AddNew
            .Fields(0) = j
            .Fields(1) = 2 * j
            .Fields(2) = 3 * j
            .Fields(3) = 4 * j
            .Fields(4) = 5 * j
            .Fields(5) = 6 * j
        Next
        .MoveFirst
    End With
    
    Set DataGrid1.DataSource = adoRecordset
End Sub

關鍵技術點

• Scroll(ColOffset, RowOffset)：相對目前位置捲動
• FirstRow：目前可見區域第一列的實際資料列號 (1-based)
• ApproxCount：資料列總數估計值
• 捲動後 Row = 0 固定對應可見區域第一列，配合 Col 定位任意儲存格

適用場景

• 大量資料繫結 DataGrid 需隨機存取特定儲存格
• 不想維護額外 Recordset 克隆/書籤同步
• VB6 / VBA / Access 表單開發