#1 File management in MS Windows
In my first post in this forum I mentioned the poor file management in MS Windows and up to now, I have tested different software to overcome the limitations we face.
I honestly admit that I was surprised to see the number of available software for it and apparently, there exist a larger need for it. It also seems quite common to search for files across networks, which of course put an even higher demand to have professional tools for it.
I finally ended up by using ISYS Desktop which is indeed a professional tool and meets my demand as a powerful, advanced and reliable search tool. Be prepared to invest some time to really learn the tool, the payoff will then be higher and you become more efficient in the search-processes.
For more information about the search tool please see:
The vendor ISYS offer also a very good support.
A big thank to OzGrid’s highly respected member Bob Stanley for pointing me to ISYS Desktop.
# 2 New kid in town
Guess what? During the last couple of years I had received lot of questions about translating my Swedish site to English. My (only) excuse for not been doing it in the past was the fact that there exists many good Excel-related sites in English, therefore there was no need for another.
But as time goes on and there is a rapid development in how we use Excel, especially when we nowadays tend to use Excel more as a part of a corporate system then as a stand-alone tool. I thought it was time to do something about it.
Excel Knowledgebase (aka ExcelKB) is the name of the site and it’s mainly focused on the following aspects, at a semi-advanced level:
• Automation of Excel with VB / VB.NET and automation of Office-programs with Excel.
• Dataexchange between Excel and databases including ADO and ADO.NET.
• Developing COM Add-ins and standard Add-ins.
It’s not yet completed (will a web site ever reach this status?) and I will add new articles to it at an approximate speed of 1 new article / day and later 1 article / week.
For more information please visit:
The site uses excellent and highly recommended software, InstantKB, which use a SQL Server Database and relies on ASP.NET and .NET Framework 1.1. The vendor, InstantASP, also offer a very friendly, good and fast support.
For more information about InstantKB please see:
# 3 Things we should not do
Sometimes when I´m lurking around the forum and reading threads, I notice that some questions tend to be asked more frequently than others. Among these questions there are several that I classify to the group of “Things we should not do”.
The classification is based on my experience as a professional consultant and what I strongly believe represent bad or poor standard.
Manipulating end-users interface in Excel
End-users both expect and prefer to recognize the usual interface Excel offers, even if your solution has been implemented. It´s also very difficult to restore the interface if things go wrong (which they tend to do from time to time).
There is no need to “boost up” Excel just because your solution has been installed and from a strict developing point of view, it’s a better payoff to use the hours to create a practical and good workable solution.
Changing end-users screen-resolution
If you think that this is a necessary thing to do then I strongly suggest you to reconsider the whole project you´re working with.
Usually it refers to too large/poorly designed forms and/or worksheets. The only workable solution is to re-design the form(s) and/or the worksheet(s).
A form can be divided into several logical sections which means both parent and child-forms can be used. Also avoid giving the forms too much personalized design. End-users expect and prefer the standard-design a la Windows. Give them what they want!
It´s well documented that SendKey-statements don’t work 100% and even if we add an overworked error-handling, we can’t solve the problem related to SendKey. The only workable solution is to avoid SendKey.
This issue is usually related to avoiding the security-message when opening a workbook with code inside and when automating MS Outlook.
I can agree that the security-message can be annoying, but it exist for a good reason and instead of trying to override it, inform the end-users about it and explain the reason for its existence.
The same thing goes with the security-message we get when automating MS Outlook from Excel.
Sure, You must send Your 10.000 e-mails immediately and all at once and therefore are “forced” to override it. If you face this situation, then I highly recommend you use other software like SamLogic’s MultiMailer
For more information about MultiMailer see:
Have you noticed that the security-message, actually offer us the possibility to set the time in a span of 1 minute to 10 minutes?
If you want to avoid clicking on a button when the next security-message pop-up shows, then don’t use the Send-statement.
No matter if you are developing for clients, or for your corporation, you may be guilty of breaking the security-policy in place by trying to override it.
Protect Your VBA-project
If you are developing for a client, or for your corporation, then don’t overwork protection of the VBA-projects.
First of all, most end-users don’t even know it exist. Second, there will always be end-users that will try everything to get their hands on the VBA-project and study the code.
Suppose you need to ship out a revised version of the VBA-project to 100 end-users, where you had done “everything” possible to protect it, then it will be major problems trying to solve at your expense.
The solution for this issue is to have a written agreement with the client, or an agreement with the corporation, that if any end-user has changed the code then all costs will be on behalf of the client, or the department.
Always make sure you get a copy of the workbook, that has generated the reported error, which makes it possible to check if any code has been changed or not.
In public forums, like OzGrid, it’s easy to forget that specific questions are more or less, answered without any error-handling.
The most remarkable thing is, any questions relating to error-handling are rarely asked about, unlike the other issues being discussed here.
This does not, implicit nor explicit, mean that you shouldn’t implement error-handling.
Good professional solutions always include good error-handling, where the end-users should never receive “internal” error-messages or any unexpected behavior.
In order to achieve good error-handling, i.e reliable solutions, the only way is to test, test and test the solutions. Don’t make it easy on yourself and think that end-users will not try some strange scenarios or actions. Setup several What-If scenarios and run the entire test.
If you can’t do an appropriated test yourself then let some other person(s) act as the “devil’s lawyer” (for the peace in the family don’t ask your wife, husband or kids to do it!)
#4 The End
Imagine that you wake up one morning and less then 12 hours later, your life has been saved by the doctors and you’ve gone through tough surgery. Imagine also, it will take nearly a year before your “normal” life is back (if you are so lucky, so to speak).
What conclusions can we draw from this scenario? The only conclusion I draw is:
Life is unpredictable so try to enjoy your life.
#5 Special note
All the utilities, I mention and recommend, are from a very pleased customer and there exists no business interest for me.
In the coming posts I will discuss best practices for coding and also post some VBA-code as well.
Meanwhile enjoy life and don’t forget to turn off the computer(s)!