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

 

 

 

Working With Circular references in Excel

Have you ever experienced the dreaded “Circular reference warning” popping up when you opened an Excel file or entered a formula?

Excel detects a circular reference when a chain of calculations visits the same cell more than once. Many users get (very) confused by this message and have no idea what it is about. I’ll tried to demystify that message in a new article:

Working with Circular references in Excel

The article contains these chapters:

Types of circular references

Simplistically speaking there are only a few types of circular references to discern:

Deliberate circular references

Accidental circular references

Calculation settings

If you want to work with circular references, the calculation settings of Excel are very important. This page gives you some pointers!

Properly setting up circular references

Whereas I am no fan of using circular references, they can be beneficial to your model and really solve the problem you are trying to solve. So here is some advice on how to properly work with them.

Reasons why circular references may not be detected

This page shows a couple of reasons why circles are not detected.

Enjoy!

Regards,

Jan Karel Pieterse

www.jkp-ads.com

Fitting curves to your data using least squares

Introduction

If you’re an engineer (like I used to be in a previous life), you have probably done your bit of experimenting. Usually, you then need a way to fit your measurement results with a curve. If you’re a proper engineer, you also have some idea what type of equation should theoretically fit your data.

Perhaps you did some measurements with results like this:

Data with fitted equation

I’ve created an Excel file with which you can fit curves to your data, check out the article on my website:

Fitting curves to your data using Least Squares

Enjoy!

Regards,

Jan Karel Pieterse

http://www.jkp-ads.com/

Embedding Excel Web App objects in your web page

Hi everyone!

As you may have seen on some blogs, Microsoft now enables you to include any Excel file -or parts thereof- in your web pages and blog posts.

For example (yes you can make changes to the cells, they are not retained):

I have written an article that explains how this is done.

Even more: I have also created a demo where you can enter information in a web form (a textbox), which in turn updates information in the embedded Excel web app file.

Enjoy the read: Embedding Excel files on your website

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Spot the 10 differences

Hi everyone!

Yesterday I experienced the tedious task of figuring out what the differences are between the VBA projects of two different versions of a customer project I built a long time ago. Luckily there are tools to compare differences in text files, such as ExamDiff.
But the VB editor doesn’t give you an easy way to export all of your code into a single textfile. So I figured I’d roll my own. At first I simply let the routine run through all VBComponents of the VBA project.

To my surprise, the order of the components in the generated textfiles wasn’t the same for both workbooks, even with the exact same components in there.
So I decided I’d better first make a list of all VBComponents, sort that list and then export the content to a textfile.

Download ExportVBAProject here.

Enjoy!

Jan Karel Pieterse

Name Manager Updated

Hi All,

I’ve just updated Charles Williams and my Name Manager utility.
New in this version is that when you delete a range name, you will be asked whether you want to unapply the range name in your formulas. This should effectively prevent any #NAME! errors from surfacing when you remove a range name.

I’m now off to remove one feature request from the list.

Any wishes? For Name Manager that is :-)

Regards,

Jan Karel Pieterse
www.jkp-ads.com