vba Visual Basic ( Excel ) 上表格范围的大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24404584/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Size of Table Range on Visual Basic ( Excel )
提问by Hann
i'm a beginner on Visual studio for Excel. I have a prob with the size of Table
我是 Visual Studio for Excel 的初学者。我有一个表格大小的问题
I use this code :
我使用这个代码：
Sub Comparaison()
Dim sheet1 As Worksheet
Set sheet1 = Sheets("feuil1")
Dim Tableau_dataBase ()
Tableau_dataBase = Range("B3:D8")
MsgBox " Size of table" & Tableau_dataBase.Height // error
MsgBox " Size of table" & Tableau_dataBase.Length // error
End Sub
My table on Excel ( only string) :
我的 Excel 表格（仅字符串）：
7 data1 data2
7 数据1 数据2
43 data1 data2
43 数据1 数据2
8 data1 data2
8 数据1 数据2
But I have an error How can I know the size of this table ?
但是我有一个错误 我怎么知道这个表的大小？
thanks
谢谢
采纳答案by Dick Kusleika
When you assign a range to an array, you get a one-based, two-dimensional array. The functions for determining the size of the array are UBound and LBound. Each takes two arguments: the array and, optionally, the dimension.
当您为数组分配范围时，您将获得一个基于一维的二维数组。确定数组大小的函数是 UBound 和 LBound。每个都有两个参数：数组和可选的维度。
Sub Comparaison()
Dim sheet1 As Worksheet
Dim Tableau_dataBase()
Set sheet1 = Sheets("feuil1")
Tableau_dataBase = sheet1.Range("B3:D8")
MsgBox " Table Height: " & UBound(Tableau_dataBase, 1)
MsgBox " Table Width: " & UBound(Tableau_dataBase, 2)
End Sub
回答by Aiken
Heightand Lengtharen't properties of a Rangeobject which is probably the cause of your error.
Height并且Length不是Range对象的属性，这可能是导致错误的原因。
Make sure your variables are of the correct type, in this case Tableau_dataBaseshould be a Range. Make sure to always use Setwhen assigning a Rangeobject. It is also preferable to explicitly refer to the worksheet your data is on when creating a Rangeobject.
确保您的变量类型正确，在这种情况下Tableau_dataBase应该是Range. 确保Set在分配Range对象时始终使用。在创建Range对象时，最好明确引用数据所在的工作表。
Sub Comparaison()
'Declare Variables
Dim ws As Worksheet
Dim Tableau_dataBase As Range
'Set object references
Set ws = Sheets("feuil1")
Set Tableau_dataBase = ws.Range("B3:D8")
'Get Height(number of rows) and length(number of columns) of Tableau_dataBase
MsgBox " Height of table" & Tableau_dataBase.Rows.Count
MsgBox " Length of table" & Tableau_dataBase.Columns.Count
End Sub
Note: I've renamed thesheet1variable towsassheet1could potentially refer to an existing sheet by its codename.
注意：我已将sheet1变量重命名为wsassheet1可能通过其代号引用现有工作表。
