如何将范围从excel存储到Range变量中？ [英] How store a range from excel into a Range variable?

问题描述

 函数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 
  

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

解决方案

当您使用 Range 不能简单地使用以下语法：

  Dim myRange as Range 
 myRange = Range（A1）
  

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

 函数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 
 
结束函数
 
 Sub main（）
 Dim test As Range 
 
设置test = getData（ActiveSheet，1，3，2 ，5）
 test.select 
 
 End Sub 
  

注意每次宣布一个范围时，我使用 Set 关键字。

---

您还可以允许您的 getData 函数返回范围对象而不是变式虽然这与您遇到的问题无关。

I am reading some cells of excel using VBA.

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.

解决方案

When you use a Range object, you cannot simply use the following syntax:

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

You must use the set keyword to assign Range objects:

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 Set keyword.

---

You can also allow your getData function to return a Range object instead of a Variant although this is unrelated to the problem you are having.

这篇关于如何将范围从excel存储到Range变量中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！