Compile Error. Variable not defined.

[Giordano Bruno]

I have the following code which lives in a UserForm in Workbook 1 and is called from button in Workbook2. I have redirected the button that launches the UserForm to code on Workbook 3. When I try to run it now, it fails on the line indicated and highlights the word tvwChild. The message is Compile Error: Variable not defined. I have searched the code in the working version and there is no other reference to this word.

If anyone can offer an explanation or workaround, I would be most grateful

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> LoadTreeView()

<SPAN style="color:#00007F">Dim</SPAN> rNodeNames <SPAN style="color:#00007F">As</SPAN> Range, rNodeName <SPAN style="color:#00007F">As</SPAN> Range, nodX <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> bNodeNames <SPAN style="color:#00007F">As</SPAN> Range <SPAN style="color:#007F00">'extra</SPAN>
        <SPAN style="color:#00007F">With</SPAN> TreeView1.Nodes
        .Clear
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    cS1 = Sheet1.Range("Codes").Column <SPAN style="color:#007F00">'sets Codes column number</SPAN>
    rR1 = Sheet1.Range("Codes").Row + 1 <SPAN style="color:#007F00">'sets top row</SPAN>

    <SPAN style="color:#00007F">Set</SPAN> bNodeNames = Sheet1.Cells(rR1, cS1) <SPAN style="color:#007F00">'first node name</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rNodeNames = Range("CodeList") <SPAN style="color:#007F00">'subsequent node names</SPAN>

    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rNodeName <SPAN style="color:#00007F">In</SPAN> bNodeNames
        <SPAN style="color:#00007F">With</SPAN> TreeView1.Nodes
        <SPAN style="color:#00007F">Set</SPAN> nodX = .Add(, , "R", rNodeName.Offset(, -cS1 + 1).Value) <SPAN style="color:#007F00">'adds nodes to the tree</SPAN>
        nodX.Expanded = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> rNodeName

    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rNodeName <SPAN style="color:#00007F">In</SPAN> rNodeNames
        <SPAN style="color:#00007F">With</SPAN> TreeView1.Nodes
<SPAN style="color:#007F00">'Fails at this line</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> nodX = .Add(Left(rNodeName.Value, -1 + InStrRev(rNodeName.Value, "_")), _
        tvwChild, rNodeName, rNodeName.Offset(, -cS1 + 1).Value) <SPAN style="color:#007F00">' adds terminals</SPAN>
        nodX.Expanded = <SPAN style="color:#00007F">False</SPAN>
        
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> rNodeName

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Damon Ostrander]

Hi Giordano,

My guess would be that your code module has "Option Explicit" which requires that all variables be declared (i.e., in a Dim statement). Since it is not obvious from your code what data type tvwChild is (I'm unfamiliar with the treeview control, so don't know what the various arguments of the Add method are), and I don't know for sure whether you can safely eliminate this variable, I suggest you simply declare it as Variant, e.g.,

Dim tvwChild As Variant

at the top of your LoadTreeView procedure. This should satisfy the Option Explicit requirement, and correctly handle any data type that is necessary.

Damon

 

[Norie]

A userform doesn't 'live in' a worksheet.

 

[Giordano Bruno]

Hi Damon,

I tried using Variant, but it produced peculiar results.

Thanks for your interest, Norie. I actually said that the code lived in a UserForm. The UserForm is "attached" to Workbook 1. Does that make it more clear?

 

[Ivan F Moala]

tvwChild is a constant = 4 for the TreeView controls 2nd parameter = relationship.
With this constant the Node becomes a child node of the node named in relative. So you shouldn't Dim it.

I cannot guess what your code is doing as I do not have all the info
eg Is this code in the UF ?

 

[Giordano Bruno]

Hello Ivan,

Yes, the code is in a user form and reads data from a spreadsheet that generates the codes necessary for the tree. I am sure that you are correct to say that it shouldn't be dimmed as it is not necessary when the code which launces it is in the same workbook. It is only a problem now that I want to launch it from a different workbook.

 

[Ivan F Moala]

without see the workbook involved my long shot guess would be that the control is not referenced properly ??

As a hack fix try substituting 4 for tvwChild

 

[Giordano Bruno]

Hi Ivan,

I tried your fix, but without any success.

Yesterday, I made an attempt to change the workbook as I was suspicious of the one which currently contained the TreeForm. When transferred to a new workbook, it instantly failed at the same spot. I tried your 'hack fix' and hey presto! it worked.

I've no idea what the problem has been, but a thousand thanks for that help. Everything is working perfectly the way it is now configured.