vba 如何将范围从 excel 存储到范围变量中？

How store a range from excel into a Range variable?

提问by Priyank Thakkar

I am reading some cells of excel using VBA.

我正在使用 VBA 读取一些 excel 单元格。

Function getData(currentWorksheet as Worksheet, dataStartRow as Integer, _
dataEndRow as Integer, DataStartCol as Integer, dataEndCol as Integer)

    Dim dataTable as Range
    dataTable = currentWorksheet.Range(currentWorksheet.Cells(dataStartRow, _
    dataStartCol), currentWorksheet.Cells(dataEndRow, dataEndCol))

    getData = dataTable

EndFunction

It throws an error, object variable or with block variable not set. How take this range in a variable? Please guide me.

它会抛出错误、对象变量或未设置块变量。如何在变量中取这个范围？请指导我。

回答by enderland

When you use a Rangeobject, you cannot simply use the following syntax:

使用Range对象时，不能简单地使用以下语法：

Dim myRange as Range
myRange = Range("A1")  

You must use the setkeyword to assign Range objects:

您必须使用set关键字来分配 Range 对象：

Function getData(currentWorksheet As Worksheet, dataStartRow As Integer, dataEndRow As Integer, DataStartCol As Integer, dataEndCol As Integer)

    Dim dataTable As Range
    Set dataTable = currentWorksheet.Range(currentWorksheet.Cells(dataStartRow, DataStartCol), currentWorksheet.Cells(dataEndRow, dataEndCol))

    Set getData = dataTable

End Function

Sub main()
    Dim test As Range

    Set test = getData(ActiveSheet, 1, 3, 2, 5)
    test.select

End Sub

Note that every time a range is declared I use the Setkeyword.

请注意，每次声明范围时，我都会使用Set关键字。

---

---

You can also allow your getDatafunction to return a Rangeobject instead of a Variantalthough this is unrelated to the problem you are having.

您还可以允许您的getData函数返回一个Range对象而不是 a，Variant尽管这与您遇到的问题无关。

回答by Andrew Leach

What is currentWorksheet? It works if you use the built-in ActiveSheet.

什么是currentWorksheet？如果您使用内置的ActiveSheet.

dataStartRow=1
dataStartCol=1
dataEndRow=4
dataEndCol=4
Set currentWorksheet=ActiveSheet
dataTable = currentWorksheet.Range(currentWorksheet.Cells(dataStartRow, dataStartCol), currentWorksheet.Cells(dataEndRow, dataEndCol))

回答by The_Barman

Define what GetData is. At the moment it is not defined.

定义 GetData 是什么。目前还没有定义。

Function getData(currentWorksheet as Worksheet, dataStartRow as Integer, dataEndRow as Integer, DataStartCol as Integer, dataEndCol as Integer) as variant

回答by Josh

Declare your dim as a variant, and pull the data as you would from an array. i.e.

将您的 dim 声明为变体，并像从数组中一样提取数据。IE

Dim y As Variant
y = Range("A1:B2")

Now your excel range is all 1 variable (array), y

现在你的excel范围都是1个变量（数组），y

To pull the data, call the array position in the range "A1:B2" or whatever you choose. e.g.:

要提取数据，请调用“A1:B2”范围内的数组位置或您选择的任何位置。例如：

Msgbox y(1, 1)

This will return the top left box in the "A1:B2" range.

这将返回“A1:B2”范围内的左上角框。

回答by Dave Dave

here is an example that allows for performing code on each line of the desired areas (pick either from top & bottom of selection, of from selection

这是一个允许在所需区域的每一行上执行代码的示例（从选择的顶部和底部选择，从选择中选择

Sub doROWSb()           'WORKS    for do selected rows     SEE FIX ROWS ABOVE  (small ver)
Dim E7 As String    'note:  workcell E7 shows:  BG381
E7 = RANGE("E7")    'see eg below
Dim r As Long       'NOTE: this example has a paste formula(s) down a column(s).  WILL REDUCE 10 HOUR DAYS OF PASTING COLUMNS, DOWN TO 3 MINUTES?
Dim c As Long
Dim rCell As RANGE
'Dim LastRow As Long
r = ActiveCell.row
c = ActiveCell.Column   'might not matter if your code affects whole line anyways, still leave as is

Dim FirstRow As Long    'not in use, Delete if only want last row, note: this code already allows for selection as start
Dim LastRow As Long


If 1 Then     'if you are unable to delete rows not needed, just change 2 lines from: If 1, to if 0 (to go from selection last row, to all rows down from selection)
With Selection
    'FirstRow = .Rows(1).row                 'not used here, Delete if only want last row
    LastRow = .Rows(.Rows.Count).row        'find last row in selection
End With
application.CutCopyMode = False             'if not doing any paste op below
Else
    LastRow = Cells(Rows.Count, 1).End(xlUp).row  'find last row used in sheet
End If
application.EnableEvents = True             'EVENTS  need this?
application.ScreenUpdating = False          'offset-cells(row, col)
'RANGE(E7).Select  'TOP ROW SELECT
RANGE("A1") = vbNullString                  'simple macros on-off switch, vb not here:  If RANGE("A1").Value > 0 Then


For Each rCell In RANGE(Cells(r, c), Cells(LastRow, c)) 'new
    rCell.Select    'make 3 macros for each paste macro below
'your code here:

If 1 Then     'to if 0, if want to paste formulas/formats/all down a column
    Selection.EntireRow.Calculate     'calcs all selected rows, even if just selecting 1 cell in each row (might only be doing 1 row aat here, as part of loop)
Else
'dorows() DO ROWS()
'eg's for paste cells down a column, can make 3 separate macros for each: sub alte() altf & altp
      Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False    'make sub alte ()    add thisworkbook:  application.OnKey "%{e}", "alte"
      'Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False     'make sub altf ()    add thisworkbook:  application.OnKey "%{f}", "altf"
      'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False         'amke sub altp ()    add thisworkbook:  application.OnKey "%{p}", "altp"
End If
Next rCell

'application.CutCopyMode = False            'finished - stop copy mode
'RANGE("A2").Select
goBEEPS (2), (0.25)       'beeps secs
application.EnableEvents = True             'EVENTS

'note:  workcell E7 has: SUBSTITUTE(SUBSTITUTE(CELL("address",$BG9),"$",""),"","")
'other col eg (shows: BG:BG):  =SUBSTITUTE(SUBSTITUTE(CELL("address",$BG2),"$",""),ROW(),"")&":"& SUBSTITUTE(SUBSTITUTE(CELL("address",$BG2),"$",""),ROW(),"")
End Sub


'OTHER:
Sub goBEEPSx(b As Long, t As Double)   'beeps secs as:  goBEEPS (2), (0.25)  OR:  goBEEPS(2, 0.25)
  Dim dt  'as double    'worked wo as double
  Dim x
  For b = b To 1 Step -1
    Beep
    x = Timer
  Do
  DoEvents
  dt = Timer - x
  If dt < 0 Then dt = dt + 86400    '86400 no. seconds in a day, in case hit midnight & timer went down to 0
  Loop Until dt >= t
  Next
End Sub