Runtime Error 91 Object variable or With block variable not set.

[Tim]

Using VBA 6.5, I receive the runtime error when running FormatQtrAnalysis.
I know it has to do something with the way I have set up the class
CSTIAnalysis.
Problem is I do not know what that something is.
Is there any suggestions as to what I am overlooking?
Any suggestions as to where to look at will be appreciated.
(Of course, an answer will Greatly Appreciated!)

From STIOptions Module

Sub FormatQtrAnalysis()
Dim csaQtrAnalysis As CSTIAnalysis
Set csaQtrAnalysis = New CSTIAnalysis
csaQtrAnalysis.Worksheet = ActiveSheet
csaQtrAnalysis.FormatHeader 'When pressing Debug, this line is
highlighted
End Sub

From CSTIAnalysis Class

Private pWorksheet As Worksheet

Public Property Get Worksheet() As Worksheet
Worksheet = pWorksheet
End Property
Public Property Let Worksheet(vNewValue As Worksheet)
Set pWorksheet = vNewValue
End Property

Public Sub FormatHeader()
Dim rHeader As Range
Dim iColCount As Integer
iColCount = pWorksheet.UsedRange.Columns.Count
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))
rHeader.MergeCells = False
With rHeader
.Merge
.EntireRow.AutoFit
.BorderAround Weight:=xlThick, ColorIndex:=1
End With
End Sub

 

[merjet]

I suspect your problem is this line:
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

Try this:
Set rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

since rHeader is a Range object.

Hth,
Merjet

 

[Bob Phillips]

or even

Set rHeader = Range(pWorksheet.Cells(1, 1), pWorksheet.Cells(1,
iColCount))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tim]

That worked, thank you.
2 questions: (If need a separate thread please let me know.)
1) Why is Set used in this situation?
(Unless you know a good book or website to read, then I will take the time
to research instead of leaching.)
2) I tried subbing Me.Worksheet for pWorksheet and it does not work.
(Same as above)

Thank you

 

[Dave Peterson]

I'd qualify all the range objects:

with pworksheet
set rHeader = .Range(.Cells(1, 1), .Cells(1, iColCount))
end with

 

[Bob Phillips]

I don't think you have to qualify the Range, because the qualified Cells is
linking it to the worksheet, the Range is the Range object not the Range
property.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

You are using Set because it is an object variable you are dealing with.
With normal variable you load them with Let, but VBA defaults that so you
can get away without using it, but you must use Set with objects.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tim]

Thanks, that makes sense.

You are using Set because it is an object variable you are dealing with.
With normal variable you load them with Let, but VBA defaults that so you
can get away without using it, but you must use Set with objects.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

It depends on where the code is. If the code is behind a worksheet (not
pworksheet), then not qualifying it would cause trouble.

 

[Dave Peterson]

I didn't recall that for this questioner, the code was in a class module.

I'd still qualify my ranges, though.