problem:Structure with Object reference (TYPE...END TYPE)

[syswizard]

I keep getting the dreaded "Runtime Error '91'....
Object Variable or With-block variable not set"

Type RangeTestType
TestCount As Long ' Cell Count
TestRange As Object ' Range RefString variable stores a name.
End Type
Dim retval as RangeTestType
Dim dataRange As Range
Dim lCellcnt As Long
Dim x as Object

Set dataRange = Range("A1:" & "C" & ActiveSheet.Rows.Count)
x = dataRange
lCellcnt = dataRange.Cells.Count
retval.TestCount = lCellcnt
retval.TestRange = x <= THIS STATEMENT TRIGGERS ERROR

When I replace the Object with "Range", I get the exact same error.
Somehow, passing objects within a structure appear to be
problematic....
any help greatly appreciated. (Excel 2003)

 

[Jim Rech]

You must "Set" objects.

Type RangeTestType
TestCount As Long ' Cell Count
TestRange As Object ' Range RefString variable stores a name.
End Type


Sub a()
Dim retval As RangeTestType
Dim dataRange As Range
Dim lCellcnt As Long
Dim x As Object

Set dataRange = Range("A1:" & "C" & ActiveSheet.Rows.Count)
Set x = dataRange
lCellcnt = dataRange.Cells.Count
retval.TestCount = lCellcnt
Set retval.TestRange = x
End Sub


--
Jim
|I keep getting the dreaded "Runtime Error '91'....
| Object Variable or With-block variable not set"
|
| Type RangeTestType
| TestCount As Long ' Cell Count
| TestRange As Object ' Range RefString variable stores a name.
| End Type
| Dim retval as RangeTestType
| Dim dataRange As Range
| Dim lCellcnt As Long
| Dim x as Object
|
| Set dataRange = Range("A1:" & "C" & ActiveSheet.Rows.Count)
| x = dataRange
| lCellcnt = dataRange.Cells.Count
| retval.TestCount = lCellcnt
| retval.TestRange = x <= THIS STATEMENT TRIGGERS ERROR
|
| When I replace the Object with "Range", I get the exact same error.
| Somehow, passing objects within a structure appear to be
| problematic....
| any help greatly appreciated. (Excel 2003)
|

 

[Tom Ogilvy]

Type RangeTestType
TestCount As Long ' Cell Count
TestRange As Object ' Range RefString variable stores a name.
End Type

Sub ABC()
Dim retval As RangeTestType
Dim dataRange As Range
Dim lCellcnt As Long


Set dataRange = Range("A1:" & "C" & ActiveSheet.Rows.Count)
lCellcnt = dataRange.Cells.Count
retval.TestCount = lCellcnt
Set retval.TestRange = dataRange
MsgBox retval.TestCount & " address: " & retval.TestRange.Address
End Sub

worked fine for me.

 

[merjet]

Try: Set retval.TestRange = x

Hth,
Merjet

 

[Madhan]

Hi, I think you should move the set statement on retval before
"retval.TestCount = lCellcnt" statement.

 

[syswizard]

Thanks guys for the help...just when I thought I was getting good at
this.....NOT !!
Strangely, I scoured the entire net look for someone who had used an
object within a TYPE using Excel. None, nada, zip to be found.

As an interesting aside, when you have an object within a TYPE
structure,
you cannot reference it directly from the structure. It must be "set"
first.
Assuming TD is the structure reference.

Dim xRange As Range
Set xRange = TD.TestRange
dTotal = dTotal + TD.TestRange.Cells.Offset(ix).Value <= Fails !!!
dTotal = dTotal + xRange.Cells.Offset(ix).Value <= Works Fine !!!

I wonder if that would not be the case had I used a RANGE in the
structure instead of OBJECT ?

 

[Tom Ogilvy]

All objects of any type (including Range) need to be set first. (as I showed
you in my code).