Category Archives: Uncategorized

VBA Coding Part 2

In the seventies there was a computer pundit, Edsger W. Dijkstra  who used to claim that a programmer only needed to use three statements to construct any programme:

Assignment: LET,

Branching: IF THEN ELSE,

and: LOOP

He considered everything else a confusing frippery. Whilst this is possibly true, using the full range of statements available within a programming language is the best way to achieve an efficient program. DO you care … If you’re writing an excel Macro and it works, probably not.

An example of needing to be efficient is. I was once asked to look at a company’s process to migrate the data from system A to system B. The data extraction from system A took 60 hours and they wanted to do it over the weekend without disruption. By making the programming more efficient, I reduced the extraction to 120 minutes. This meant I could do the migration overnight, rather than at the weekend. Saving them the cost of all that weekend work.

So lets take a look at the rest of the code:

   Do Until tvar = ""
      Do Until tvar = ""
        Cells(y, x) = tvar
        x = x + 1
        tvar = trim(Cells(y, x))
      x = 1
      y = y + 1
      tvar = trim(Cells(y, x))

The DO statement introduces the start of a LOOP in the code.

You will notice that between the While and the Loop the lines of code are indented. Why have I done this? It is a visual clue to the code included in the loop. It serves no purpose, but can save a lot of time when tracking down a bug in the code.

It might seem odd that the very next line of the Loop code is another Do Loop section. You will also see that this is further indented. Again, this serves only to highlight the inner loop.

Look at the code and I am sure you will make sense of it:

Do Until tvar="" Means that the loop will run until tvar = "" (The cell is empty)
Cells(y, x) = tvarReturns the contents of tvar into the spreadsheet cell on row Y, Column x.
x = x + 1Is incrementing the variable x (take the contents of x and add one)
tvar = trim(Cells(y, x))Is reloading the variable tvar with the next cell on row y.

And then the loop causes the code to return to the do statement which will run until the condition is TRUE
The final part of the programme executes after the inner loop has finished.

x = 1Resets the column pointer to one.
y = y + 1Increments the row pointer
tvar = trim(Cells(y, x))Once more reloads the variable tvar to satisfy the outer loop

The outer loop repeats until the condition is TRUE


That was a brief insight into how my mind works! I tend to think a little like a computer and so I find these things easy to write and understand. I hope it has tempted you to consider trying to extend your macro writing beyond simply duplicating a simple sequence of tasks.

SSRS Error

SSRS Email sending error

Sql Server Reporting Services is an amazing tool provided as part of Microsoft’s SQL Server product.

The front end isn’t the prettiest, but it allows for users to get to reports under control of active directory security.

One of the great features is being able to schedule a subscription to send an email. It works like clockwork and everybody forgets about it until the day the report fails.

‘Failure sending mail. Exceeded storage allocation. The server response was 4.3.1 Message size exceeds fixed maximum size. Mail will not be resent’

Starting the diagnosis was fairly simple. Checking the subscription showed the failure message. The puzzle being what had changed? I’d heard of a ten Meg limit on some mail servers, but surely this was going to an exchange server. Did that have a limit. After running the report and saving as a spreadsheet, the whole thing was less than 2meg.

The answer was a palm on face slapping. Of course the mail wasn’t going straight out. It was routing through a local server running the ancient SMTP service that I think has been around since NT 4 days.

To be honest, I don’t know if these are the standard settings, or if somebody had been trying out the effects of the settings. However, unticking the restrictions on message size fixed the problem and meant the report will continue to go out for the next geological epoch.

Scammers – You’ll never fall for it, will you?

A local business has just told me about this one:

Their customer received an email from them with a change of bank details. 30000 pounds, yes count the zeroes, thirty thousand pounds paid from the customer into the ‘new bank account’.

The email looks almost perfect except for the double letter in the domain name. It is obviously an email that has been around the building and then sent out.

There is no come back. The bank is not liable, the chances of anybody seeing that cash again is remote. Possibly two local businesses will be forced to close.

The accounts person thought they were doing the correct thing making the change. This company even maintains old-fashioned procedures. The person who makes payments can’t change account details.

Is it time to review your procedures to double check emails and requests?

This is the second email scam aimed at business I have seen in the last month.

RSSQL API Coding fun

There’s a strangely satisfying feeling when I manage to get the RSSQL API to work. It’s only half documented and written for c++. I’ve never yet had to use the parts that are documented and I use c#.

Its a sad confession, but I have never written c++, I can cope with C, and I consider C# to be one of the cleanest languages to use, but c++ and I never got along. I’d like to claim I objected to the implementation of objects, or point to the over use of pointers, but the simple truth is nobody ever paid me to learn it. I have no pride and will code in any language the client asks me to use. However, without a project I don’t have the enthusiasm required to really get a handle on the language.

The problem with using .net to interface onto the API is I need to define every record layout byte by byte. Added to the fact that RSSQL programmers use every form of INT from tiny – big, plus they define a new alias for the type, making it really fun to translate.

However, enough whining, because once I crack the record structure, I then get to fight the internal integrity checks. Nothing better than watching procedure lines through the profiler to ascertain where the error is kicking out!

The latest set of mods were to update records received as web requests over the ONeil bridge. I’m extracting the detail lines to consolidate them into single orders based upon more complex rules than ONeil allows me to use.

Extracting the records proved easy enough, but going back to update the status and work order details onto each line gave me issues.
All now cracked and working sweetly.

Maybe I should publish the records I currently have defined to use the RSSQL API? I’ve done work orders, work order line, Web order details, and items. If anybody wants to have them, drop me an email and I’ll fire them over. Obviously, you have to remember, these can be different on every dot release of the product and rarely survive a full version number increment.

ONeils API – Moving to version 5.01

I do quite a lot of programming work around the edges of ONeil RS-SQL records management system. I write reports and process data extracts using SQL tools like reporting services, or integration services.

However, there is one job that comes up every couple of years.
Aligning the old code to the new API.

I was never a c++ programmer. I skipped from C, to C# without hitting that awful thing in the middle.

Things always change. The original code, used unmanaged memory and literally byte arrays to pass data. So, when a record grew a new field, the required bytes had to be aligned.

This time around they’ve changed the way the login process works, but forgotten to include this code in their API examples.

If you’re using the ONeil API for using the old unmanaged methods, you’re really going to need this:
namespace ONeilSoft.RSSQLAPI
public class APIImports
#region Login\free\get last error
[DllImport(“RSSQLAPI.DLL”, CharSet = CharSet.Unicode, ExactSpelling = true, CallingConvention = CallingConvention.Cdecl)]
public static extern UInt32 RSSQLClientLogin(
string userCode,
string userPassword,
string recordCenterGuid,
string loginOptions,
string configServer,
string configDatabase);

New style / Managed

I have to say, the new style code is looking much better. Unfortuantely, there are still some gaps in the functions available, but it really is now in the realms of normal human programming.

Sales Reporting How complex should it be?

I’ve spent years providing sales reports from databases. We went through inch thick wads of music paper with columns of numbers, we tried BI with dice and slice, drill down cubes and animated graphs.
The end result is, that I am convinced 99% of it is a waste of time. Whilst this stuff might be great for IT savvy managers, or for Information Engineers, it is invariably too much for the sales person to consume.
Possibly its ‘horses for courses,’ but I’ve not yet met the sales person on the road who wants to drill down into their data. (Please, email me, tell me you are the person who only makes his figures because of a multi-dimensional cube!)
A client recently came up with the idea of producing the most simple report they could. Four lines on a bar chart, with values as the labels. Year To Date (YTD), and Period, Actual and Budget.

The report is sent every day and the budget accumulates a daily proportion.
Of course, I’m always happy to discuss reporting and provide anything the client wants, but really ask the question: what can the sales force genuinely use?

SQL Reporting Services parameter memory

The great thing about Google is the way you can find a way to do just about anything.

I’ve been using SSRS for quite a time and haven’t had this request, but as people expect things to be so much more intuitive these days, it seems logical for a reporting system to remember what they typed last time.

I found I had to combine techniques from a couple of sites as this one says it will only cope with up to 32 entries and I had a few thousand to handle.

I’m selecting from an account number list and originally this used a long human readable code rather than a foreign key. This burst the size of reporting parameter passing and the SQL string limit. Luckily, I could painlessly convert to the unique ID.

So, I claim no original work in this, I used this link to get the basic logic functions to work.

I found the spit function on another site. The guy claimed to have written it 6 years ago.

I opted for creating a separate database to store the reporting memory. It’s a third party app and they might get upset if they found new tables appearing. I toyed with the idea of adding database and server name into the options, but decided it wasn’t a problem today and might never be.

The only issue here was the dim gotcha that the split function I’d opted to use had the parameters the other way around to purple frog’s. Took a few minutes of head scratching on that one Doh!

Works like a charm.

Thanks to the two people who did all of the hard work.

Flattening data for the end user

SQL databases are usually big complex beasts. An end user knows they have the information in the system, but it often proves too complex to extract it in a useable form.

We can use a number of tools to provide a company with data in a form they can use.

What do I mean by “flattening” the data?

A database designer works to “normalise” a database. This means removing any repeating data from a table and placing it in its own table.

For example a stock record:

Stock Code
Stock Description
Stock Category
Stock Category Description
Stock Category Discount Level
Stock price


Would be normalised into two tables:

Stock Code
Stock Description
Stock Category Code
Stock price


Stock Category Code
Stock Category Description
Stock Category Discount Level


By “joining” these two tables, they can appear as one, but when a change is required on a discount level, only one record needs to be amended, instead of all of the stock table.

This is a simple example, but it highlights the most frequent issue facing an amateur report writer. “How can I include the details from both tables on a single report?”

We would ask if the report writer needs to know how? If we provide a “view” of the data, we can offer the report writer the data joined correctly and displaying the fields they need.