Formula Auditing by RefTreeAnalyser: Objects included

Hi all,

I’ve been working on my RefTreeAnalyser again. What I’ve been up to this time is building tools which help with the analysis of dependencies which are mostly hidden from view:

  • Charts (series formula)
  • Pivot table (source data)
  • Data Validation formulas
  • Conditional Formatting formulas
  • Form controls (linked cell, listfillrange)
  • ActiveX controls (linked cell, listfillrange)
  • Picture objects (linked cell)

A new dialog has been added that shows all sources of the objects in your file:

Objects analysed for cell dependencies

Moreover, when you analyse a particular cell for its dependencies, objects are taken into account too (well, to be perfectly honest, only if you purchase a license):

RTAObjectsInRefs

If you haven’t already done so, why don’t you head over to my website and download the tool. The demo is free and (almost!) fully functional.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Improving the Trace Precedents Experience

Hi all,

I’ve been working on my RefTreeAnalyser in the past weeks. One thing I’ve been working on is improving the not-so-intuitive way Excel displays Precedents using arrows, especially if a cell has mutliple off-sheet precedents:

Excel's way to show precedents

Notice that silly goto dialog (which you get when you double-click the off-sheet arrow with the tiny “table” icon next to it)?
Prize question: which worksheets and ranges are the entries in the Goto listbox pointing to?

I redesigned that “experience” to this (I manually added the red call-outs to this screenshot to explain what is what):

My way to show Precedents

Note how off-sheet precedents are represented by a picture of (part of) the range in question. A nice way to enable you to see what the precedent values are.

The boxes contain a hyperlink to that range so a simple click takes you there for further inspection. On hover with your mouse you’ll get the precedent’s source address in a tooltip.

If you like this idea, why not head over to my site and download the demo version so you can try it and shoot some comments at me?

There is one snag: the new feature only works for Excel 2007 and up.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

A new tool: Trusted Document Manager

Hi everyone!

I have just published a new tool today, Trusted Document Manager. This little tool enables you to manage your list of trusted documents. Currently, Excel only allows you to either leave the list intact, or delete the entire list. This means all of your currently trusted documents become untrusted again so you have to enable macro’s on all of them once again. The tool allows you to remove just one file, remove an entire folder or even an entire drive. Also it offers to possibility to remove files which no longer exist from the list.

This is what the tool looks like:

ScreenshotOfTrustedDocManager

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Queen’s day sale

As some of you may know, I’m Dutch and live in “The Kingdom Of The Netherlands”.
Well, tomorrow is an important day for the Dutch people: Queen’s Day.

More so, tomorrow is an extra special day.

Not because our Queen abdicates to make room for King Willem-Alexander, but because my 24 hour “Queen’s Day sale” is held. :-)

Anyone who places an order for my RefTreeAnalyser between April 30, 2013, 8:00 AM GMT and May 1st, 2013 8:00 AM GMT will receive a 50 % rebate on the list price.

### Update: Well, it’s been a great day yesterday and the Queen’s day sale has ended.###

I’m also going to give away some of the licenses: I’ll randomly select 1 out of every 10 orders placed in that period of time. These people will receive their license for free!

Note that you can head over to my website now and download and install the tool to try it, most of it is fully functional without the license code, you just get a nag screen now and then.

Have a nice Queen’s day (which was held in my home town in 2011) and enjoy a taste of our “orange fever“!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

An MSForms Treeview 2: Ready for beta testing

Hi everyone,

Some time ago I announced I was working on “An MSForms Treeview” replacing the Common Controls Treeview with an all-VBA counterpart.

This home-made treeview control will work on any Office version as of Office 2000, including 32 and 64 bit Office. I expect it will even work on MAC Office, but I’m still waiting for test results.

Peter Thornton (thank you Peter!) jumped in on the project enthusiastically and really made a difference adding all sorts of usefull stuff and optimising the code for performance.

Now we’re ready for beta testing.

Please visit this page of my website for a description of the control and a download file which includes a demo userform implementing the treeview classes we built:

An MSForms (all VBA) treeview

Tell us what you think of it (oh, and please report bugs too!).

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Excel 2013, SDI and modeless userforms

Hi Everyone,

With Excel 2013 we also got new document windowing in Excel; Microsoft decided to make Excel behave the same as Word:

from MDI:

Excel 2010 MDI interface showing two workbooks
The Excel 2010 MDI interface

to SDI:

Excel 2013 SDI interface showing two workbooks
The new SDI interface of Excel 2013

This causes havoc when one shows a modeless userform which should stay on top of all Excel windows:

Excel 2013 SDI can cause a userform to disappear
Excel 2013 SDI can cause a userform to disappear

I’ve devised a way to prevent this problem and written an article about how this was done.

Enjoy!

Regards,

Jan Karel Pieterse

www.jkp-ads.com

 

 

 

VBHelpers Update

If you’re using my VBHelpers add-in and haven’t made these fixes yourself, you may want to download the latest version at the end of this post.

Fix Public to Private Set

When I convert public variables to private and it’s an object, I would call the property Public Property Set, but I didn’t use the Set keyword in the actual variable assignment statement. So I fixed that.

Make FillFromRange use a Variant array

My old FillFromRange used a For Each rCell in rRng.Columns(1).Cells to loop through the cells. Now it read the range into a variant array and uses For i = LBound(vaValues,1) to UBound(vaValues,1). I never had a huge performance problem with it, but generated code should use best practices, don’t you think?

Insert Module Bug

Whenever I would insert a new module, it would put it in the project for the active workbook, not the active project. I finally decided to figure out the problem and fix it. The old code looks like this:

Sub InsertModule()

Dim sName As String
Dim vbc As VBComponent
Dim vbp As VBProject

sName = Application.InputBox("Enter Module Name")

On Error Resume Next

If Left$(sName, 1) = "M" Then
Set vbc = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_StdModule)
vbc.Name = sName
vbc.CodeModule.InsertLines vbc.CodeModule.CountOfLines + 1, "Private Const msMODULE As String = """ & sName & "()"""
ElseIf Left$(sName, 1) = "C" Then
Set vbc = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_ClassModule)
vbc.Name = sName
vbc.CodeModule.InsertLines vbc.CodeModule.CountOfLines + 1, "Public " & Mid$(sName, 2, Len(sName)) & "ID As Long"
ElseIf Left$(sName, 1) = "U" Then
Set vbc = Application.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_MSForm)
vbc.Name = sName
End If

End Sub

The InputBox call made the Excel Application active (rather than the VBE) which switches the active project to the active workbook. By storing the active project before I show the InputBox, it does the right thing.

Sub InsertModule()

Dim sName As String
Dim vbc As VBComponent
Dim vbp As VBProject

Set vbp = Application.VBE.ActiveVBProject

sName = Application.InputBox("Enter Module Name")

On Error Resume Next

If Left$(sName, 1) = "M" Then
Set vbc = vbp.VBComponents.Add(vbext_ct_StdModule)
vbc.Name = sName
vbc.CodeModule.InsertLines vbc.CodeModule.CountOfLines + 1, "Private Const msMODULE As String = """ & sName & "()"""
ElseIf Left$(sName, 1) = "C" Then
Set vbc = vbp.VBComponents.Add(vbext_ct_ClassModule)
vbc.Name = sName
vbc.CodeModule.InsertLines vbc.CodeModule.CountOfLines + 1, "Public " & Mid$(sName, 2, Len(sName)) & "ID As Long"
ElseIf Left$(sName, 1) = "U" Then
Set vbc = vbp.VBComponents.Add(vbext_ct_MSForm)
vbc.Name = sName
End If

End Sub

You can download VBHelpers.zip