VB offset error from Jack Dahgren's Code

[scott_hanebutt]

I am trying to use the "Export hierarchy to Excel" code that Jack Dahgren
wrote found at http://masamiki.com/project/exporthierarchytoexcel.bas. When
I run it I get an error on the following line of code:

Set xlRow = xlRow.Offset(I, 0)

I have added the Excel library refrence. Is it possible that the library
has changed and that the offset feature no longer exists or works differently?

Thanks,
Scott Hanebutt

 

[JackD]

Do you have an appropriate value for i?
Or is that supposed to be a 1? I can't recall using a capital I as a
variable in any code, so your problem may be there.

 

[scott_hanebutt]

The line I posted was coppied directly from your website. An interger is
passed into a function. The function calls it "I". "I" is set to 1 from the
line that calls the function.

Thanks,
Scott Hanebutt

 

[JackD]

Hmm... you are correct. I thought I didn't do things like that
<excuse>but at least it was almost 4 years ago</excuse>.
I can't make it get an error the way you are getting an error so I'm not
sure where we go from here.
A couple things to be careful of.
1) If you are cutting and pasting, make sure you do not include the first
line. The one reading "Attribute VB_Name = "ExportToExcel"". I'm going to
modify the file so that is no longer there.
2) A good way to figure out what is going on is to step through the code
line by line. You do this in the visual basic editor. Position the cursor at
the beginning of the first line and hit the F8 key to walk through step by
step.
Setting a watch on particular variables and the like is also a good idea. If
you know that there are parts of the code which are not causing a problem
you can set a breakpoint and then run. It will go to the breakpoint and then
you can start stepping through line by line from there.

Let me know what you find and I'll try and update the example so others
won't run into this issue.

 

[scott_hanebutt]

I already had commented out "Attribute VB_Name = "ExportToExcel"" I have
stepped through the code and confirmed that I = 1 when it hits that line.
The error I am getting says "Object required".

I am using the Excel library version 9.0 (the only excel library I have and
I have read it is the correct one for Excel 2000), Project Professional 2003,
and Excel 2000. What did you run it with?

I have done some research on the offset command and it seems to require a
reference in it like: Set xlRow = xlRow.Offset("$A$1", I, 0)

I have tried it this way and still get the same error. Refer to:
http://www.ozgrid.com/Excel/DynamicRanges.htm for info regarding using a
reference.

Thanks,
Scott Hanebutt

 

[JackD]

What does xlRow = at that time?
I just tried it with the Excel 11 (2003) object library and it works fine.
I developed it on excel 2000.

I suspect you are missing some of the code. Copy and paste the whole thing
again. The first 4 lines should be:

Option Explicit
Dim xlRow As Excel.Range
Dim xlCol As Excel.Range
Sub TaskHierarchy()

If you haven't defined xlRow then you will get an Object required error.

You can also get rid of the dwn and rgt functions if you like and just use
plain old offset within the code. This example is supposed to demonstrate
passing parameters (very simple ones) so that is the reason they are there
in the first place.

 

[scott_hanebutt]

Problem Fixed!!!

I had moved a few lines of code due to another macro. Now I just need to
modify it to do exactly what I want.

Thanks,
Scott Hanebutt