Tag Archives: VBA

Great Support from MZTools

I’m a great fan of MZTools. On paper the features it offers don’t really seem spectacular, offering templates and assistants for commonly used constructs and tasks, an improved find and replace and so on. However, whilst on their own, each seems a fairly trivial addition or change, together they make a big difference.

I had the freeware VB6 and VBA version installed at SSE pretty well since I arrived, but until recently I hadn’t really looked at the newer .Net versions, not thinking I really needed them.

Anyway, recently I decided to give an evaluation of the .Net version a go, and, as with any good evaluation, found that I missed the features when the evaluation expired, so I decided to put my money down, and purchase a copy for myself.

I went for the VS2005 licence – although I primarily still work with VS2003, as Carlos Quintero, the developer kindly throws in a free copy of the 2003 version too. The 2003 version installed fine, however I hit a bit of a problem with the 2005. Part way through the install it came up with a pretty odd looking error message.

I e-mailed a copy of the message off to Carlos, who asked what platform I was using. I replied telling him that I had VS2005 installed onto an XP64 partition – and Carlos confirmed that he wasn’t able to support XP64, as he is still developing using a 32-bit system. He kindly offered me a refund, however since I could use it on a 32-bit install, and he said he was going to support 64-bit in the future I said no.

I wasn’t expecting to hear anything else for a while, at least until a 64-bit native version turned up, however this afternoon I got another e-mail from Carlos double checking my setup, followed by a zip file containing a replacement for one of the files in the installation. He asked me to replace the file, and run a single command at a DOS prompt, and hey presto, MZTools fired up fine under VS2005.

When you consider what lousy support you can get from some of the big names in the industry, it is great when you come across someone like Carlos with a great little product, and a fantastic level of support to back it up.

Whose Language is it Anyway?

Over the past couple of days I’ve been having one of my periodic frustrations with Microsoft Excel. This time it was down to date formatting. Basically I was asked to produce a VBA macro that saved off the contents of one worksheet to a CSV file.

This is in theory pretty straightforward as Excel has an option on the Save dialog to save in CSV format. However I didn’t bargain on the complications of Excel.

One of the common problem areas that people come across on Excel relates to it being a US product, so by default it will use the confusing US numeric date format, of mm/dd/yy. As a result we will always ensure that all of our dates are defined with a textual month, which avoids too many problems, we also ensure that the international settings are set to UK, which usually avoids problems. When you save out a CSV manually, the dates come out fine. However when you do the same thing in a VBA macro it ignores the settings, and converts everything to the US numeric date format.

After some digging around, I found that this is because VBA maintains a different language setting, so although I was working on a UK English machine, with fully UK English settings, VBA was in US English.

Having found this out, I then tried to force VBA into UK English, not possible as the language libraries are separate too. So this morning I got a copy of the VBA Software Development Kit, complete with language files. I ran through the install, and got to the screen where it allows me to choose the language in which to install VBA:

Language Choice

As you can see, not much of a choice at all – the only English provided is US English. If I want to get the dates to save in any other way than the confusing US numeric dates, I have to manually output the file.

As one respondant to a similar query about dates online said VBA is very US centric…