Storing a Range in a Public Variable

[SaeOngJeeMa]

Hi, I have two questions:
1) Is there a way to store a Range in public variable an have it's scope and
lifetime last until the workbook is closed? I'm trying to use the code below
but the Range information is being lost.

2) Is there a way to set the range with having to activate the worksheet and
select the cells?

Thanks,
Dean

****************************
In the General Declarations section of one of my code modules:
Public grngFSF As Range

Sub in one of my code modules that runs on the ThisWorkbook Open event and
also gets run again when rows of the worksheet are added or removed:

Public Sub setWorksheetDataRanges()
On Error GoTo Err_setWorksheetDataRanges

With Worksheets("FSF")
.Activate
.Range(Cells(5, 1), ActiveCell.SpecialCells(xlLastCell)).Select
Set grngFSF = Selection
Cells(5, 1).Select
End With

Exit_setWorksheetDataRanges:
Exit Sub
Err_setWorksheetDataRanges:
MsgBox "sub setWorksheetDataRanges " & Err.Description
Resume Exit_setWorksheetDataRanges
End Sub

 

[Helmut Weber]

Hi,

at least you could store the arguments
that define the range in one or more customproperties,
like X1, Y1, X2, Y2,
meaning the range spreads from cell(X1, Y1) to cell(X2, Y2).
Whereby a customproperty applies to a worksheet-object.

Or use CustomDocumentProperties
which apply to the workbook-object.

Hi, I have two questions:
1) Is there a way to store a Range in public variable an have it's scope and
lifetime last until the workbook is closed? I'm trying to use the code below
but the Range information is being lost.

2) Is there a way to set the range with having to activate the worksheet and
select the cells?

Not for me, not yet.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

on strange territory ;-)

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Bob Phillips]

Hi, I have two questions:
1) Is there a way to store a Range in public variable an have it's scope and
lifetime last until the workbook is closed? I'm trying to use the code below
but the Range information is being lost.

Create a Public Range variable in a general code module, not ThisWorkbook or
a worksheet code module, declaring it before any procedures.

2) Is there a way to set the range with having to activate the worksheet and
select the cells?

Not if you try to use activecell as part of the code, you get a problem. But
if you use a base cell you can

With Worksheets("FSF")
Set grngFSF = .Range(.Cells(5, 1), .Cells(5,
1).SpecialCells(xlLastCell))
End With

Note that you still cannot select that range if the sheet is not active, but
you shouldn't need to.

 

[Helmut Weber]

Hi Bob,

hmm...

and why does this fail, with runtime error 1004,
if the active worksheet is worksheets(1)?

Sub MyRange()
Dim oRng As Range
Dim oWrk As Workbook
Dim oSht As Worksheet
Set oWrk = ActiveWorkbook
Set oSht = oWrk.Worksheets(2)
Set oRng = oSht.Range(Cells(1, 1), Cells(2, 2))
' error 1004
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

on strange territory ;-)

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Tushar Mehta]

Set oRng = oSht.Range(Cells(1, 1), Cells(2, 2))

oSht refers to worksheets(2).

Cells(x,y) without a qualifier object defaults to the current sheet,
worksheets(1) in your example.

What you are trying to do is specify a range on worksheets(2) with cells
from worksheet(1) and that's not possible.

One way that will work:
with oSht
set oRng = .Range(.Cells(1,1), .Cells(2,2))
end with


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Helmut Weber]

Hi Tushar,

yes!

Thank you.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Tushar Mehta]

Hi Tushar,

yes!

Thank you.

You are welcome.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[SaeOngJeeMa]

Bob, Helmut, Tushar,
I took your advice and it worked! Thanks. Here's the final code that I'm
using now.

With Worksheets("FSF")
Set rngTemp = .Range(.Cells(5, 1), .Cells(5, 1).SpecialCells(xlLastCell))
Worksheets("VariableTables").Cells(8, 5).Value =
rngTemp.SpecialCells(xlLastCell).Row
Worksheets("VariableTables").Cells(9, 5).Value =
rngTemp.SpecialCells(xlLastCell).Column
End With


Dean

 

[Bob Phillips]

Why not just use

With Worksheets("FSF")
With .Range(.Cells(5, 1), .Cells(5, 1).SpecialCells(xlLastCell))
Worksheets("VariableTables").Cells(8, 5).Value = .Row
Worksheets("VariableTables").Cells(9, 5).Value = .Column
End With
End With


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)