Excel Macro -- Group Sheets and Trace Dependents?

vik1109
vik1109 used Ask the Experts™
on
I have a request to do two tasks in excel:

1.  Working with large financial models often creates 30-50 sheets in a workbook. Therefore, navigation becomes very tough.  What i would like to do is create a macro that would be identicle to the "Group" function that is available for rows and columns.  However, i would like to create it for the sheets in the macro.  Is that possible?

2.  Also, I would like to be able to go through the active sheet, and see if any cells that are null or empty have dependents.  This will help trace errors.  However, a cell having a value of zero does not qualify as null or empty.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
cri

Commented:
2 questions rolled into one...

1) Can not yet see a direct solution for grouping worksheets. Some work-arounds:

a) Right click on the left bottom sheet 'spinners' (i.e. |< < > >| . Does the pop-up list help the navigation ?

b) Define a worksheet with hyperlinks to your sheets and use the group/ungroup feature to filter them. If you constantly expand the workbook, keeping the sheets and the list in sync will be bothersome.

2) Only with a macro. Ever done something in VBA ?

Author

Commented:
Thanks for the comments cri.  

1.  The feature to group sheets would still be very helpful in organizing the model.  I am aware of the spinner navigation.  I have done the hyperlinking method, but grouping will still be a good addition to help navigation.  Any ideas on how a grouping feature could be built?

2.  I am not familiar w/ VBA at all.  Any help or guidance is appreciated.

Author

Commented:
Thanks for the comments cri.  

1.  The feature to group sheets would still be very helpful in organizing the model.  I am aware of the spinner navigation.  I have done the hyperlinking method, but grouping will still be a good addition to help navigation.  Any ideas on how a grouping feature could be built?

2.  I am not familiar w/ VBA at all.  Any help or guidance is appreciated.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

cri

Commented:
vik1109, there might be a misunderstanding. I was refering to group/ungroup the _hyperlinks_, not the  worksheets. What could be done is to hide the worksheets according whether they are visible, but this is hardly what you are after.

Regarding VBA: I could give you tons of links, but IMO the best way to start is to record a macro, compact it (i.e. almost always 'Select' is superfluous) and add  whistles and bells. A macro to determine void precedents however is a bit steep to start with. Will see what I can come up with, but I do not promise anything.  

BTW: You will attract more experts if you assign points according the difficulty. I do (almost) not mind as long as I meet the quota to stay in KPro, which is already the case, but others might.

Author

Commented:
cri, i appreciate your help.  I would add more points, however, I just joined and am not able to add more points at this time.  I know this is not an easy task.

Thanks.
cri

Commented:
Several questions:

a) Do you want to check a whole worksheet or a selected range ? Is the 'Null Precedent' check all you want ? I have a macro which sort of applies the trace dependents/precedents for a whole range, but, depending on the complexity of the workbook and the choosen tracing depth you get a blue screen <g>.

b) Do you already have some macros or functions which you would like to use across workbooks ?

c) Do you have _any_ programming experience ?

Author

Commented:
A.  checking a whole worksheet would be good.  And then have some sort of ouput telling which null cells are precedents.  

B.  I dont' have any other macros or functions that I want to use across workbooks besides this one.

C.  I have very little programming experience.

cri

Commented:
Ok, will see, but not before in a week time.

In the meantime, follow http://www.experts-exchange.com/Applications/MS_Office/Q_20400353.html . If you post a cross reference to this one or a comment (can be 'listening') you will be able to access the answer w/o paying 10%
cri

Commented:
Here two alternatives. If you can exclude for sure links to other worksheets, take #1, else #2 is the better solution.

a) In Excel: Tools|Macros|VisualBasicEditor (or Alt+F11)
b) In VBE: In View|ProjectWindows: Highlight your workbook -> Insert|Module -> paste the following Code


Option Explicit  'to prevent erros arising from typos
Option Compare Text 'in order to make string comparisions case insensitive

Sub VoidCheck1()
  'Check whether a formula is dependent from an empty cell
  'Attention: Only within the _active_ worksheet !
 
  Dim rngCell As Range
  Dim WsIsProtected As Boolean
   
  'Attention: If a password is required a prompt will issued automatically,
  '           the macro will run, BUT will not re-apply the _password_.
  With ActiveSheet
    'Remember whether worksheet is protected
    WsIsProtected = .ProtectContents
    'Unprotect worksheet, if there is a password the standard dialog will run
    .Unprotect
    'Remove all auditing arrows
    .ClearArrows
    For Each rngCell In .UsedRange
      'Check all cells which are empty or contain spaces
      If IsEmpty(rngCell.Value) Or Trim(rngCell.Text) = "" Then
        rngCell.Activate
        'Work around as "rngCell.ShowDependents" does not work, it is a bug in 97
        Application.ExecuteExcel4Macro "TRACER.DISPLAY(FALSE,TRUE)"
      End If
    Next rngCell
    If WsIsProtected = True Then .Protect
  End With
End Sub


Sub VoidCheck2()
  'Displays _all_ precedents of a cell if at least one is empty or contains only spaces
 
  Dim rngCell As Range, rngCellPrec As Range
  Dim WsIsProtected As Boolean
   
  'Attention: If a password is required a prompt will issued automatically,
  '           the macro will run, BUT it will not re-apply the _password_.
  With ActiveSheet
    'Remember whether worksheet is protected
    WsIsProtected = .ProtectContents
    'Unprotect worksheet, if there is a password the standard dialog will run
    .Unprotect
    'Remove all auditing arrows
    .ClearArrows
    On Error Resume Next
    For Each rngCell In .UsedRange
      'Check whether preceding cells are empty or contain spaces
      For Each rngCellPrec In rngCell.Precedents
        If IsEmpty(rngCellPrec.Value) Or Trim(rngCellPrec.Value) = "" Then
          rngCell.ShowPrecedents
          Exit For
        End If
      Next rngCellPrec
    Next rngCell
    On Error GoTo 0
    If WsIsProtected = True Then .Protect
  End With
End Sub



c) Save and return to workbook (view)

d) Tools|Macros: Select Macro and click 'Run'.

e) If you like the functionality and if you use it alot, ask back regarding how to make the macro easier/faster to start. If not: Alter/Update the code, best way to learn VBA. Case it does not run anymore: Paste code here.

Author

Commented:
cri..the macro works pretty well, however there is still one flaw in it. here's an example:

on sheet 1 enter the following date:

in column A number the cells 1 to 10 for the first 10 rows.

then assign 3 different cells the following formulas:

=A1*B5

=Sheet2!B5

=A1*Sheet2!B5

the macro will catch the first two examples as having a null precedents.  However, it won't catch the second formula despite Sheet2!B5 being a null cell.  I am referring to the 2nd macro you suggested where it links to other sheets.  

Can this be fixed?
cri

Commented:
Strange, it works on my Excel 97 SR-2.

Perhaps, there is a misunderstanding: The auditing arrows for references outside the active worksheet are shown differently, i.e. _not_ as blue arrows. Click on the little icon representing a worksheet and then on each of the shown references. This will zap you to the precendent.

If this does not help: Which Excel version and service release do you have ? If unsure: Help|About.

BTW: Did you know you can navigate through the dependents/precendents by clicking on the arrow shaft ? It takes a bit of aim, but it is very handy.
Commented:
Further, #1 shows all empty cells which have dependents and #2 shows _all_ precedents for all cells which have at least one empty precedent. With other words: With #2, you will see no auditing arrows on your 'Sheet2', only in Sheet which was active upon macro call.
cri

Commented:
Graded as B, oh well...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial