On-the-fly data entry form

Hi all,

So after Dicks (nice to read) Alive and Well , let’s do some Excel/VBA stuff again.

I intend to get a bit of discussion on this one, so bear with me.

I’m currently developing an Excel workbook for a customer. One of the interesting things with this project is that it consists of multiple data tables, each in its own worksheet. The customer needs to be able to edit the data in these tables.

One of these might contain these fields:

empId
empCompanyId
empRegNo
empFirstName
empLastName
empDeptId
empFunctionId
empDOB
empGender
empTitle
empFunctionGroup
….

The way I would normally have done this is by creating a userform with a control for each field and all the coding that is needed to handle record selection and stuff. (and yes, I do know there is MS-Access :-))

But since I have an odd 5 worksheets to handle I decided it would be nice to have a generic data entry form that would build itself using a companion worksheet for each data sheet.

For each worksheet that requires data entry I inserted a companion sheet with this information:

dataentrysettings1.gif

My VBA code inside the userform’s code module reads this sheet and builds the controls accordingly.
The form has a couple of properties I can set to control appearance. All it now takes to show the data entry form for worksheet “oSh” is this bit of code:

Set frmDataEntry = New ufDataEntry
With frmDataEntry
Set .Source = oSh
Set .SourceSettings = ThisWorkbook.Worksheets(oSh.Name & “_Fields”)
.Title = sTitle
.RowCount = 14
.FieldWidth = 120
.LabelWidth = 150
.Label2FieldMargin = 12
.VertMargin = 3
.HorMargin = 6
.Initialise
.CurrentDataRow = 1
.Show

I’ve got this all up and running, including Validation and all (and yes: I’ll be writing this all up in detail some day).

Now to the questions of the day:

What do you think about the method I chose?
What alternative solutions have you come up with in the past?

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Fixing Links To UDF’s in Addins

Hi All,

Excel has a host of built-in functions to help you do your computations. But sometimes you need to write your own functions using VBA. These functions are called “User Defined Functions” (UDF).

UDF’s typically are placed in addins. As long as the addin is installed, the UDF’s work as expected. You get into trouble when the location of the addin changes, for example because you have distributed the Addin to your co-workers and they have installed it to their local user addin folder (which is different for each user!).

As soon as a file which uses the UDF is opened on a system that uses a different addin path, all cells that use your UDF show #NAME! errors. Also, the complete path to the original location of the add-in is shown in each formula:
=’C:dataFixLink2UDF.xla’!UDFDemo(A1,A2).

There are a couple of ways to avoid/resolve this situation:

Use fixed location

Of course the simplest way to avoid the problem is by fixing the location of
your addin. Tell all your users where the addin should be installed (or even
better: create a setup tool that doesn’t allow it to be installed elsewhere).
Your #Name! errors will not resurface.

Don’t use an addin

Well, not exactly so, you could still have an addin. But instead of keeping your
UDF code inside the addin, you create a facility that copies the UDF routine
into each workbook that uses it.

This is a neat solution, but it requires that your user has the security option
“Trust Access to Visual Basic Project” set.
John
Walkenbach’s Power Utility Pack
uses this trick.

Redirect the UDF’s to the new location

This is the technique I’ll describe extensively in this article. The addin
checks each workbook the user opens whether this new workbook contains a link to
the addin. If so, it ensures the link points to the proper location.

Read on here!

Enjoy!

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Showing An Array On A Form; Autosizing ColumnWidths Of A ListBox

Hi all,

For a project I needed a quick way to display the content of an array to the user. I didn’t want to use a worksheet, but opted for a userform. The data I wanted to show was contained in an array. The array contained something like:

Description Before After
Cell Errors 100 10
Corrupt Names 1000 0
Unused styles 232 0

So I figured I’d put a listbox on a userform and make sure the column widths of the listbox resize with the data I want shown.
I wanted it to look like this:
autosizelistbox02.gif
That proved far from easy…

Read on…

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Two popular tools updated.

Hi All,

As many of you know I give away a number of tools for Excel through my site www.jkp-ads.com. Today I have updated the two most popular downloads:

Name Manager (which I created together with Charles Williams, www.decisionmodels.com):

Most important change: the unused names filter now includes objects in its search, as well as VBA code. It makes the filter much slower, but way more useful in my opinion. Also, I have added the Greek character set so Name Manager doesn’t (wrongfully) think range names with Greek characters are corrupt.

So far, Name Manager has been downloaded about 50,000 times since I posted it on my site.

Flexfind

I have updated the user interface of Flexfind so (in my opinion) it is easier to use. Also, I have mimicked the find all behaviour of Excel: if you select multiple items in the found items list, Flexfind will create a (multiple) selection of areas of the found cells.

Flexfind is less popular than Name Manager, the download count is at about 23,000.

Enjoy!

Of course I am open to any comments, suggestions and -most importantly- lots of praise :-)

Regards,
Jan Karel Pieterse
www.jkp-ads.com

Bogus Compile Errors

Hi all,

Let me start this post with a shameless plug: Charles Williams and I developed Name Manager. From my website alone this tool is good for about 60 downloads a day.

Just a couple of weeks ago, all of a sudden Charles and I started receiving complaints about compile errors, which neither of us could reproduce. Because the version we had available back then used the treeview control from the Windows common controls library, we blamed that control. And indeed, removing the control from the form that housed it fixed the problem for some of our users. But not for all.

Since this kind of errors is extremely hard to troubleshoot, I thought it might be useful to share our experiences.

Luckily we found a user who was willing to help us trouble-shoot the matter. We asked him to do all sorts of things: try on a different client, try logging on as administrator, removing accounts from client, you name it.
After exchanging a host of emails and screenshots and trying all sorts of variations we discovered the culprit: problematic .EXD files in one of the system folders of the client computer (these are just an example, your system may show others too or even none at all):

exd-files.gif

These files are typically stored in this location:

C:\Documents and Settings\[UserName]\Application Data\Microsoft\Forms

So far, removing these files from that folder has resolved the issue for the people that experienced trouble with Name Manager.

So, if you distribute an addin to other users and you get a complaint about compile errors in your work: start off by asking the user to weed out the Forms folder I showed above. If it doesn’t help, it certainly wont hurt!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Catching Paste Operations

You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.

What to do? The only way I find to be reliable is to catch all possible paste operations. But this isn’t very easy, since there are a zilion ways to paste.

I have put together a sample workbook and an explanatory article on how one could go about protecting a workbook by intercepting paste operations.

Let me know what you think!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

VBA Code Documenting Tools: Project Analyzer and Visustin

Every VBA developer (which is anyone who knows how to get into the VBE I guess) develops his or her own programming habits: sparse commenting or elaborate commenting, naming convention (or no declaration at all), code indenting, preference for certain structures and methods over others. You name it. Many books have been written about this, of which I find “Code Complete” is a very good one.

Although I think I write pretty readable code, I do have one bad habit: I don’t really document what I have done and I tend to “forget” to write comments.

Sometimes a customer wants elaborate documentation of the code. But of course they forgot to ask up front…
So here I am looking at this 10.000 lines-of-code VBA project and a request to produce tech documents on what the code does and how it is structured. Including flow diagrams (preferably in Visio), call trees, the works.

At first I estimated I’d need as much as maybe half the amount of time I originally used up to write the code itself. Which was significant of course. Let’s say over a full week.

Like with any task that I find tedious: I look for a way to automate, so I dive into a search quest with Google.

Typical search strings: VBA code documenting, Document Code, Create Dependency tree, Show call stack,….

Well, I found this site.

Both their Project analyzer tool and Visustin looked like they might be a solution to my problem. But they’re both rather expensive (I’m Dutch, remember?). I calculated I’d have to invest about € 1000 to cover for these two tools in the versions I think I’d need.

Luckily Aivosto granted me a time-limited full version of the Pro version of both tools so I could thoroughly test them (and to be frank I also promised to write up my experiences).

Here they are then.

I opened Project Analyzer and since I also installed the Office VBA plug, the File menu shows “Analyse Office VBA…” as one of the options. You point it to your file and it happily imports the entire VBA Project (if you have “Allow access to VBA project” set, of course). So far so good!

Take a look at the Report Menu. It shows a myriad of reporting tools. Very impressive!

projanal01.gif

I pick the “Problem Report” and it shows me lots of useful (and maybe even embarrassing) stuff, indicating line numbers and of course the routines and module, like:

Too many parameters: WriteName2sheet
194 Consider short circuit with nested Ifs
432 Unicode function is faster: ChrW$

Function without type specification
692 Too many uncommented lines: 81 (ouch)
Dead procedure

And lots of other useful stuff. Didn’t know I produced such a load of rubbish :-).

OK, let’s try something else:Procedure call tree. Wow. Everything’s there:

projanal02.gif

Then I tried the graphical version of the call tree:

projanal03.gif

(Yes I blurred this one on purpose).

So far so good. I won’t bother you all with the dozens of other reports I tried and used. I got more impressed every minute I can tell you!

…Lots of copying and pasting from Project Analyzer to Word followed…

Now let’s have a look at Visustin. Ever needed to create a flow diagram? Well I haven’t, because I tend to just dive in (I know, bad habit…).

Have a look at this procedure:

Option Explicit

Sub GetFilesInDirectory(ByVal sDirToSearch As String, colFoundFiles As Collection)
‘————————————————————————-
‘ Procedure : GetFilesInDirectory Created by Jan Karel Pieterse
‘ Company   : JKP Application Development Services (c) 2006
‘ Author    : Jan Karel Pieterse
‘ Created   : 04-10-2007
‘ Purpose   : Retrieves all files in sDirToSearch, stacks matches into cLookForFIles
‘————————————————————————-
   Dim NextFile As String
    Dim lCount As Long
    Dim sFileName As String
    Dim sFileSpec As String
    Dim lFoundMatches As Long
    Dim oCtlNew As CommandBarButton
    Application.EnableCancelKey = xlErrorHandler
    If Right(sDirToSearch, 1) <> “” Then
        sDirToSearch = sDirToSearch & “”
    End If
    NextFile = Dir(sDirToSearch & “*.xls”)
    Do Until NextFile = “”
        If Err.Number = 0 Then
            If TypeName(oObj2Add2) Like “Command*” Then
                Set oCtlNew = oObj2Add2.Controls.Add(msoControlButton, , , , True)
                oCtlNew.Caption = NextFile
                oCtlNew.OnAction = “OpenFileFromMenu”
                oCtlNew.Tag = sDirToSearch & NextFile
            Else
                AddFile2Wizard oObj2Add2, NextFile, sDirToSearch
            End If
        End If

        NextFile = Dir()
    Loop
    On Error GoTo 0
TidyUp:
    Exit Sub
End Sub

So now what? Well, copy, paste and hit F5. You get this:

projanal04.gif

WOW! (also proves commenting is useful…)

So next I found myself in the process of alt-tab to the VBE, select code, control-c, alt-tab to Word, paste code, alt-tab to Visustin, control-v, F5 (builds this chart), control-c, alt-tab back to Word, paste the diagram, …..

And the fun part was creating the Visio diagrams. They didn’t turn off screenupdating and I can tell you it is great fun seeing this program spitting out these (for me) complex diagrams in seconds, which would have taken me hours and hours…

All in all, producing the entire document set took me about half a day. Man, this tool cost me money! (but I gained quite a happy customer).

And to think that the enterprise version of Project Analyzer comes with macros…

Tell me what you think and what your experiences are! Have you got similar experiences, or completely different,…
Share them here!

Regards,

Jan Karel Pieterse
www.jkp-ads.com