Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem:Structure with Object reference (TYPE...END TYPE)
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem:Structure with Object reference (TYPE...END TYPE)
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 "syswizard" wrote in message ups.com... |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) | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem:Structure with Object reference (TYPE...END TYPE)
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. -- Regards, Tom Ogilvy "syswizard" wrote in message ups.com... 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem:Structure with Object reference (TYPE...END TYPE)
Try: Set retval.TestRange = x
Hth, Merjet |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem:Structure with Object reference (TYPE...END TYPE)
Hi, I think you should move the set statement on retval before
"retval.TestCount = lCellcnt" statement. "Jim Rech" wrote: 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 "syswizard" wrote in message ups.com... |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) | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem:Structure with Object reference (TYPE...END TYPE)
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 ? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem:Structure with Object reference (TYPE...END TYPE)
All objects of any type (including Range) need to be set first. (as I showed
you in my code). -- Regards, Tom Ogilvy "syswizard" wrote: 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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
object on RHS when there is a type-mismatch | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
On-the-fly determination of object type? | Excel Programming | |||
automation object type library reference | Excel Programming | |||
User Defined Type Structure - want it to be variable not numeric literal | Excel Programming |