Novice question: I cannot get the immediate window to work

[Rodby]

I am trying to learn VBA from a STEP BY STEP written by Reed Jacobson.
He encourages using the Immediate Window because he claims you can see the
progress as you code.
I am copying his code verbatim. For example, in the current practice, I have
the following Macro shell;

Sub PivotSet Style()
Dim pt as Pivottable
Dim ts as Tablestyle
Set pt=ActiveCell.Pivottable
End sub

I initialize this by pressing F8 three times, and then I type the following
in the immediate window:
Set
ts=ActiveWorkbook.Tablestyles("PivotStyleDark2").Duplicate("NewPivotStyle")
pt.Tablestyle2=ts.Name

(Note: the "Set ts= ActiveWorkbook..." statement above, is all on one line
in the immediate window)

When I get to the last line I get an "Object Required" error message.

I've checked the code and the instructions, and I cannot see anything
different from what I have copied.

What am I doing wrong?

 

[joel]

the code is not written very well. First, don't use "activecell". for
this code to work you need 3 things to happen

1) You need to have a pivot table
2) You need to have the sheet with the pivot table as the active sheet
3) You need to have the active cell on the worksheet select a cell
inside the pivot table.

The line should be like this

from
Set pt=ActiveCell.Pivottable
to

Set pt = sheets("sheet1").Range("A1").Pivottable

 

[Rodby]

Joel, thank you!

to be fair to the author I copied this from, there was a pivot table on an
active sheet. I do not know if I had actually was in a cell in the table.

Is there something about the immediate window that I am missing? I rarely
get it to work, and it often gives me "undefined" type messages.

 

[joel]

I don't use the immediate window often. I prefer to add watch item
when I debug. the few times I have used it I didn't have any problems
but I know the correct syntax of the the statments.

Most problems with beginners is they don't which object has the focu
or is the active object. Using Activecell and active sheet cause
problem because excel changes the active object when you aren't awar
that it is being changed.

I avoid using ActiveCell, Selection, and Activesheet. ther are bugs i
VBA where you must use these features and know when I must use them fro
experience. I don't need to use the immediate window because I can rea
and write obejct using the watch window and I don't have to type th
name of the object al I have to do is highlight the object name in th
code and then right click the mouse. If I have to read or write to th
worksheet I put a break point in the code and then go to the workshee
and look at the cells or change the cells as needed.

I only use the immediate window when I having problems and to verif
the results I'm getting using my other debugging techniques. Or whe
I'm helping other people who don't want to change their code and hav
used ActiveCell, and Activehsheet.

 

[Rodby]

Joel, thanks again!

I think I will be back for more as I get further along.

I find the help routines in Excel VBA almost worthless.

It is though you go to a restaurant and you see the menu, but you don't know
what "medium rare" means, or what "side items" mean, and there is
nothing/nobody to help you find out.

Anyway, thanks for the tips!

 

[joel]

I find all of the microsoft documentation very poor, although some
things I've seen recently is improving very slightly. For any windows
product to get certified it must meet 8th grade reading level. This
means the help cannot be more than 3 paragraphs long, a paragrapoh
cannot contain more than 4 senetences, a ssentence cannot be more than
40 characters, the words must be in an 8th grade vocabulary dictonary.
How can you explain something technical with these requirements.


If you ever watch the olld Beverly Hillbillies TV show Jethrow only
attended up to 6th grade and wanted to be a brain surgeon. That is the
same logic that microsoft is using in documenting their products.