VBA – Resolving C# competition problem with VBA and Excel

Today I have decided to resolve one of the competition problem of the C# basics exam of the SoftUni (www.softuni.bg) with Excel. I have shown how I have resolved a similar problem in C#, where you should have drawn a glass of wine here. The problem is about building a string, which consists of dots and stars, in a way that it looks like a plane, if you have more phantasy:

PlaneExample

For the really curious, I have a pdf version of the exam problem 03.Plane it is licensed under the CC-BY-NC-SA license, so take it in mind, if you redistribute it.

Pretty much what I wanted to achieve is something like this – I wanted to write a custom formula in Excel, with one parameter  and to get the output as expected in the example. Did I manage to do it? Check it by yourself:

Plane

The question is more or less how I did it. The answer is – following the C# logic and accustoming VBA to my C# knowledge. As far as I did not have a string builder in VBA, I have created a similar function, called fStringBuilder(), taking two arguments – the string which is going to be repeated and the times of repetition. At the end, I have saved everything in the fPlane function, using VbCrLf for spaces. And the i=0 on each line was needed, because this is the way to code in VBA – the for loop is a little … different :). Last but not least, the result should be displayed in a font such as Courier, because it gives each char an equal space. Or simply print it in the immediate window with debug.Print.

Anyway, it is easier to be seen than explained.

At the end I wanted to make it a little bit more fancier, so I have created the sub colorMe(). It colors all the stars Chr(42) into Red. Quite a good one and rather slow actually, but if you look at the code you will realize the reason for the slowness – after all we are printing and this is a scripting language:

MakeItRed

 

Finally, here comes the code – just copy it in a module and use the new fancy function in Excel:

🙂

About

A VBA Developer.

Tagged with: , , , , , ,