Question

excel vba listview引用单元格问题

excel vba listview引用单元格问题单元格是个时间，引用到listview里就变数字了，怎么办？

Answer 1

Private Sub UserForm_Initialize()
Dim tb As Table, x%, y%
Set tb = ThisDocument.Tables(1)
x = tb.Rows.Count
y = tb.Columns.Count
ListView1.ColumnHeaders.Add , , "序号", 40
For n = 1 To y
ListView1.ColumnHeaders.Add , , tb.Rows(1).Cells(n).Range.Text, 40
Next

For i = 1 To x
ListView1.ListItems.Add , , i
For j = 1 To y
ListView1.ListItems(i).SubItems(j) = Left(tb.Cell(i + 1, j).Range.Text, Len(tb.Cell(i + 1, j).Range.Text) - 1)
Next j
Next i
ListView1.View = lvwReport
ListView1.FullRowSelect = True
ListView1.Gridlines = True
End Sub

Answer 2

使用VBA的格式转换函数：

日期型转换：

=Format(单元格引用, "YYYY-M-D")

时间型转换：

=Format(单元格引用, "hh:mm:ss")

时间日期型转换：

=Format(单元格引用, "YYYY-M-D hh:mm:ss")