FastExcel V3 Released – At Last!

Posted on June 12, 2014 by Charles Williams

As some of you may know I have been working on developing Version 3 of my FastExcel product for more years than I care to remember.
First there was the VB6 blind alley, then 64-bit and multi-threaded calculation and that ribbon thing, then I detoured into learning C++ and XLLs, then there was the inevitable scope creep, (and my consulting customers kept asking me to do things, and then there was the sailing) … you get the picture.

Anyway I finally pushed the release button on June 4 2014. Yippee!!!

The initial reaction has been great (no doubt helped by the introductory offer of 50% off).

So what is FastExcel V3?

There are 3 major products in the FastExcel V3 family, targeted at different types of use scenarios.

FastExcel V3 Profiler

The Profiler gives you a comprehensive set of tools focussed on finding and prioritising calculation bottlenecks. If your spreadsheet takes more than a few seconds to calculate you need FastExcel profiler to find out and prioritize the reasons for the slow calculation

Profiling Drill-Down Wizard
Profile Workbook
Profile Worksheet
Profile Formulas
Map Cross-References

FastExcel V3 Manager

FastExcel Manager contains tools to help you build, debug and maintain Excel workbooks.

Name Manager Pro – an invaluable tool for managing Defined Names and Tables
Formula Viewer/Editer – a better way of editing and debugging more complex formulas.
Sheet Manager – Easily manage and manipulate worksheets.
Workbook – Cleaner – Trim down any excess bloat in your workbooks
Where-Used Maps – See where your Defined Names, Number Formats and Styles are being used

FastExcel SpeedTools

SpeedTools provides you with a state-of-the-art tool-kit to help you speed up your Excel Calculations

Calculation timing tools for workbooks, worksheets, and ranges
Additional calculations modes to enhance control of calculation so that you only calculate what needs to be calculated.
90 superfast multi-threaded functions
Faster and more powerful Lookups and List comparisons
Multi-condition filtering and Distinct formulas to eliminate many slow SUMPRODUCT and Array formulas
Enhanced functions for Array handling, text, mathematics, sorting, information and logic

If you want to find out more just go to my website

or Download the 15-day full-featured trial of FastExcel V3

Formula Auditing by RefTreeAnalyser: Objects included

Posted on January 14, 2014 by jkpieterse

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:

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):

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

Posted on October 24, 2013October 24, 2013 by jkpieterse

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

Posted on October 10, 2013October 10, 2013 by jkpieterse

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:

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Queen’s day sale

Posted on April 29, 2013May 1, 2013 by jkpieterse

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

Posted on February 22, 2013 by jkpieterse

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

Posted on November 26, 2012 by jkpieterse

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

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

Posted on April 10, 2012February 12, 2019 by Dick Kusleika

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

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

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

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Category: Downloads

FastExcel V3 Released – At Last!

So what is FastExcel V3?

FastExcel V3 Profiler

FastExcel V3 Manager

FastExcel SpeedTools

If you want to find out more just go to my website

or Download the 15-day full-featured trial of FastExcel V3

Formula Auditing by RefTreeAnalyser: Objects included

Improving the Trace Precedents Experience

A new tool: Trusted Document Manager

Queen’s day sale

An MSForms Treeview 2: Ready for beta testing

Excel 2013, SDI and modeless userforms

VBHelpers Update