Question regarding variable scope

[jerH]

I have a hierarchy chart (SmartArt) that represents a work breakdown structure, and I'm using VBA to manipulate the colors and text in the blocks as the user enters data about the project status. I implemented this by using the worksheet_change event to re-color the chart whenever the data on the sheet changed.

This worked well in an early demo to my user, so they asked me to expand it to the next level down in the breakdown structure. Unfortunately this expanded the hierarchy from ~dozen elements (nodes) to almost 80. Now when there is a change on the sheet, the worksheet_change handler takes 20-30 seconds to run. Not good....

I'm experimenting with different ways to speed this up, though it may be that this just isn't going to happen. One thing I thought I might do is to declare and set the object variable pointing to the chart only once instead of having it, as a local variable in the event handler, declared and set every time. Don't know if this would make a difference, but it's something to play with.

Anyway, I know how to declare global variables within modules, but since event handlers go on the sheet (or in the case of the workbook_open handler, with the workbook object) is there any way to do it? I tried putting:

Option Explicit
Public objOKCShape As Shape

Private Sub workbook_open()

        
    Set objOKCShape = Sheets("OKC").Shapes(1)


End Sub

on the workbook in the VBE and then

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    'Dim objShape As Shape
    Dim i As Integer

    Application.ScreenUpdating = False
        
    With Sheets("OKC")
    
        'Overall Rollup
            'color
            Call setColor("OKC", "O91", objOKCShape, 1)

but I get an undefined variable error on objOKCShape.

Am I doing something wrong, or is this not doable?

Thanks!

 

[John_w]

Move the declaration of objOKCShape from ThisWorkbook to a standard module and then it is visible to code in any module, including ThisWorkbook and the sheet module.

 

[jerH]

Thanks! That worked....didn't speed things up any but it worked.

 

[RoryA]

FYI if you declare it as Public in an object module like ThisWorkbook, it becomes a member of that object and has to be accessed as such:

Call setColor("OKC", "O91", ThisWorkbook.objOKCShape, 1)

 

[mikerickson]

About the speed, I notice that setColor is called regardless of which range has been changed.
You might want to put the cell detection code outside of that so that the user changing an irrelevant cell doesn't call setColor.

I'm also a bit curious why the call to setColor is inside a With block.

 

[jerH]

Yeah, there's a lot to work on here...

Originally there was no setColor. When there were only a dozen nodes I just dealt with each one individually, so the With block kept me from having to reference the sheet name each time. When it exploded to 80ish nodes, I originally set up a loop to go through them all, and that was the code where I first encountered the very slow run time. On a lark, I unspooled the loop and just went back to individually dealing with each node, which just meant there was a lot of duplicated code. But that resulted in a subroutine that was too large...first time I'd ever encountered that problem. So the setColor sub was just to eliminate the duplication...

What I'm working on now is just what you recommended, checking the address of the change to limit the amount of updating. There's one column that, if there's a change, causes the whole model to have to be recalculated since the values in that column are normalized. But other changes can restrict the update to just a single node in the hierarchy, which should be quick.