Solved: Set Range to a spreadsheet

[makako]

Hi, Currently im learning to use the spreadsheet inside a userform but when I try to set a range variable to the sheet I get an error.

dim rangex as range
set rangex = myspreadsheet.sheets(1).range("A1")

Currently Im working with dim rangex as variant (which i usually do when I give up) and works but i was hoping some one could give me the correct type. thanks

[Zack Barresse]

Hi there,

The correct type is Range, as you had it. The question is why doesn't it work. Is that workbook open? Is that the project name for the workbook? If not, you'll need to do it like this ..

[vba]Dim RangeX as Range
Set RangeX = Workbooks("myspreadsheet").Sheets(1).Range("A1")[/vba]

Does that help?

[makako]

No it does not, it pops the error (-2147352565 (8002000b)). Not sure if that is of any use.

[johnske]

Hi, Currently im learning to use the spreadsheet inside a userform but when I try to set a range variable to the sheet I get an error.

dim rangex as range
set rangex = myspreadsheet.sheets(1).range("A1")

Currently Im working with dim rangex as variant (which i usually do when I give up) and works but i was hoping some one could give me the correct type. thanks

Range is the correct type. This sounds like an OWC (Office Web Component) spreadsheet - is it? If so, AFAIK there can only ever be one sheet, so sheets(1) is redundant and will be causing an error. I assume you've renamed Spreadsheet1 (the default name) to myspreadsheet, so try
[vba]
dim rangex as range
set rangex = myspreadsheet.range("A1")
[/vba]there is a very extensive Help file available for OWC, click on the spreadsheet in the VBE window and press F1 to view it...

[Zack Barresse]

It it's not an OWC by chance, the only other explanation I would have is that either the file is not open or the first sheet is not a worksheet but maybe a chart sheet.

[makako]

I have more than 1 sheet, the control I added is microsoft office spreadsheet 11. But I would gladly change it to any other object if its easier to use.

[Zack Barresse]

So this is a standard workbook? Can you upload an example/copy of it?

[makako]

Example

[johnske]

After reading the OWC Help files it seems that the use of the 'Set' keyword is not supported for ranges on the OWC spreadsheet, so your code (below) will fail...[VBA]Private Sub Button_Click()
Dim RangeX As Range, RangeY As Range
Set RangeY = Range("A1")
Set RangeX = Spreadsheet1.Sheets(1).Range("A1")
RangeY.Copy RangeX
End Sub[/VBA]However, this will work...[VBA]Private Sub Button_Click()
Range("A1").Copy
Spreadsheet1.ActiveSheet.Range("A1").Paste
End Sub[/VBA]

[makako]

ok, this can work with the copy example but using a variable ( to simplify the handeling ) has to be declared as variant?

[johnske]

ok, this can work with the copy example but using a variable ( to simplify the handeling ) has to be declared as variant?

Personally, I'd avoid using variants unless absolutely essential because they're too memory-intensive and slow.

With the code you have, a variable isn't really necessary. I don't see any point in cluttering up a procedure by declaring a variable that's only going to be used once, in that case it's better to refer directly to whatever it is that you're handling (if the variable's only for you to clarify what you're doing it's better to use comments). However if you want to use variables you can use RangeX as type Object... [VBA]Private Sub Button_Click()

Dim RangeX As Object, RangeY As Range

Set RangeY = Range("A1")
Set RangeX = Spreadsheet1.ActiveSheet.Range("A1")

RangeY.Copy
RangeX.Paste

End Sub[/VBA]

[makako]

I get that but what I have posted here is just an example, the complete code is much longer and is part of a 3 MB zip addin that Im unable to post. But, is object a better way to decalre than variant? when can I use it. thanks

[johnske]

Variant is too broad and covers too many things, Object narrows down the field a bit more but I'd really only use it in instances such as the OWC spreadsheet where you 'have to' - Range is the preferred type

[makako]

Thanks, i used it and work fine.