Am I Range? ... I object!!!

  • Thread starter Thread starter John T Ingato
  • Start date Start date
J

John T Ingato

Look... I need some help understanding something. I know with your wisdom,
you will be able to get this through my thick skull.

What really is the difference between thse declarations:

Dim Var as Range
Dim Var as Object

When I think of a range... I think of a range object. The key word being
"object".

The reason I bring up this confusion, mainly, is that if I set a range
variable to a range, that " RangeValue", doesn't carry with it all the
properties of where it came from.

Condider this: sheet1 is active

Set MyRange = Range("A2").CurrentRegion

MyRange is no a "range" object which points to the curren region connected
to cell A1 on Sheet1

What if I want to set MyRange to a region on another sheet??? The following
is not valid code:

Set MyRange = sheets("Sheet3").Range("A1").CurrentRegion

Is this where an "Object" variable come into play?

I realize that I can use:

MyRange = MyOtherRange.address(external:=True)

to get the full reference, but this does return a string variable... not a
range

Can someone Plleeease help me to understand this.

John
 
? activesheet.name
Sheet1
set myrange = sheets("sheet2").Range("A1").CurrentRegion
? myrange.Address(external:=true)
[Book1]Sheet2!$A$1:$E$18

Your assertion that

Set MyRange = sheets("Sheet3").Range("A1").CurrentRegion

is not valid code is incorrect.

If you want to use a range, dim the variable as Range

Object is more generic.

If I want to work with the sheets collection

for each sh in ActiveWorkbook.Sheets

then I would

Dim sh as Object

because sh can hold different objects. Worksheets, MacroSheets, charts,
etc

A range is a range. The parent of a range object is a worksheet. Object
isn't a special form of range. It is just a much more general declaration.
 
Tom,

Perhaps I should show you were my problems lie.
With the following code, I get a run time error 424: object required :

Dim rCurrentRecord as Range
Set rCurrentRecord = Sheets("Stores").Cells(1,
ListOfRepsBox.ListIndex + 1).Address


Now I realize that the ".address" is the cause of the failure. If I remove
it, the code passes. But, when I now take this range and pass it to another
function:

Set rStoreRange = FindEndOfDataIn("Col", rCurrentRecord,
"range")

At get a run time error '1004': Selection method of range class failed at
the code Startcell.select in the below function

Any other comments or critisism is welcome

****************************************************************************
***************

Public Function FindEndOfDataIn(Selection As String, StartCell As range,
Optional ReturnType As String) As Variant


' Finds the end of Data in a Column or Row depending on your choice.
' The function is passed your selection as a string in quotes
' as well as the starting cell address.
' This function returns the address of the last data cell
' Return Types are "RefCell", "Number", "Range", "Count", or
"NextEmptyCell"


Dim NextCell As Integer
Dim Count As Integer

NextCell = 1
Count = 0
Selection = LCase(Selection)
ReturnType = LCase(ReturnType)


StartCell.Select


If Selection = "row" Then


Do While Not IsEmpty(ActiveCell)

ActiveCell.Offset(0, NextCell).Select
Count = Count + 1

Loop

If Count > 1 Then ActiveCell.Offset(0, -1).Select

ElseIf Selection = "col" Then

Do While Not IsEmpty(ActiveCell)

ActiveCell.Offset(NextCell, 0).Select
Count = Count + 1

Loop

If Count > 1 Then ActiveCell.Offset(-1, 0).Select

End If



If IsMissing(ReturnType) Then ReturnType = "cellref"



Select Case ReturnType

Case Is = "cellref": Set FindEndOfDataIn = ActiveCell

Case Is = "count": FindEndOfDataIn = Count

Case Is = "range": Set FindEndOfDataIn = range(StartCell,
ActiveCell)

Case Is = "number"
'FindEndOfDataIn = The number of the row or column

Case Is = "nextemptycell"

Select Case Selection

Case Is = "row"
If Not IsEmpty(ActiveCell) Then
Set FindEndOfDataIn = ActiveCell.Offset(0, 1)
Else: Set FindEndOfDataIn = ActiveCell
End If

Case Is = "col"
If Not IsEmpty(ActiveCell) Then
Set FindEndOfDataIn = ActiveCell.Offset(1, 0)
Else: Set FindEndOfDataIn = ActiveCell
End If

End Select


End Select

FindEndOfDataIn = FindEndOfDataIn.Address(External:=True)


End Function

Tom Ogilvy said:
? activesheet.name
Sheet1
set myrange = sheets("sheet2").Range("A1").CurrentRegion
? myrange.Address(external:=true)
[Book1]Sheet2!$A$1:$E$18

Your assertion that

Set MyRange = sheets("Sheet3").Range("A1").CurrentRegion

is not valid code is incorrect.

If you want to use a range, dim the variable as Range

Object is more generic.

If I want to work with the sheets collection

for each sh in ActiveWorkbook.Sheets

then I would

Dim sh as Object

because sh can hold different objects. Worksheets, MacroSheets, charts,
etc

A range is a range. The parent of a range object is a worksheet. Object
isn't a special form of range. It is just a much more general declaration.

--
Regards,
Tom Ogilvy



John T Ingato said:
Look... I need some help understanding something. I know with your wisdom,
you will be able to get this through my thick skull.

What really is the difference between thse declarations:

Dim Var as Range
Dim Var as Object

When I think of a range... I think of a range object. The key word being
"object".

The reason I bring up this confusion, mainly, is that if I set a range
variable to a range, that " RangeValue", doesn't carry with it all the
properties of where it came from.

Condider this: sheet1 is active

Set MyRange = Range("A2").CurrentRegion

MyRange is no a "range" object which points to the curren region connected
to cell A1 on Sheet1

What if I want to set MyRange to a region on another sheet??? The following
is not valid code:

Set MyRange = sheets("Sheet3").Range("A1").CurrentRegion

Is this where an "Object" variable come into play?

I realize that I can use:

MyRange = MyOtherRange.address(external:=True)

to get the full reference, but this does return a string variable... not a
range

Can someone Plleeease help me to understand this.

John
Click to expand...
Click to expand...
 
Referring to your main query <<What really is the difference between
thse declarations>> perhaps the following macro will help. (Copy/paste
into a module).

If you define a variable as Object then the Set statement can be used
to assign it to any type of object. This might be useful in a long
running macro where it is used often because it reserves memory space
at the beginning of your program where the system will find it faster.
Having it further down makes it slower to find.

'------------------------------------------------
Sub test()
Dim Var As Object
'---------------------------------
'- Var as Range
Set Var = ActiveSheet.Range("A1")
'- Var.Value works here
MsgBox ("Range A1 " & Var.Value)
'---------------------------------
'- reset Var as Worksheet
Set Var = ActiveSheet
'- correct usage of new Var
MsgBox ("Sheet.Range A1 " & Var.Range("A1").Value)
'- the following line crashes the macro because a
'- Worksheet does not have a .Value property
x = Var.Value
'---------------------------
End Sub
'--------------------------------------------------
 
<snip>

You definitely need to leave the .address off, as that returns a string
rather than a range.

Your problem is that the code is trying to select a cell on a sheet that is
not activated.

Solution is to insert
Sheets("Stores").activate

Put this in before
Set rStoreRange = FindEndOfDataIn("Col", rCurrentRecord,"range")



To help see the basics, I produced a simplified version of your code, which
all seems to work perfectly - see below

HTH

GB



Public Sub Testit()

Dim MyRange As Range
Set MyRange = Sheets("Sheet3").Range("A1")

Sheets("Sheet3").Activate 'change this to Sheet1 and it will generate your
particular error
Range("E1").Select

Set rStoreRange = FindEndOfDataIn("Col", MyRange, "Range")

Debug.Print rStoreRange.Address

End Sub


Public Function FindEndOfDataIn(Selection As String, StartCell As Range,
Optional ReturnType As String) As Variant

StartCell.Select

Range("F2").Activate

Set FindEndOfDataIn = Range(StartCell, ActiveCell)


End Function
 
Back
Top