Category: VBA Excel Tricks

VBA is not a scripting language…

VBA – Find function in Excel does not return the first value by default

The Range.Find() function in VBA-Excel is quite a tricky one. In general, you may think it is straight forward, because it usually returns what you are looking for, until the moment when it simply surprises you. This is the MSDN description

Tagged with: , , ,

VBA – Type Declaration Suffixes

If you are coming from the .Net world, then you are used to declare variables like this:

In this case, both foo and bar are of type Long. Then, someone tells you that you want to switch for a

Tagged with: , , ,

VBA – Attributes. The high-end in VBA.

You have probably noticed, that whenever you export a class module from the Visual Basic Editor, you get some strange lines in the exported file, above the Option Explicit. This is something, that you have not written, but it is

Tagged with: , , ,

VBA – RegEx in Excel – Part 2

After the first part about RegEx in Excel lately I had to do something else with RegEx. The task was quite simple, pretty much called “stripping a string”. The idea is that you have a string and you should remove all

Tagged with: , , , ,

VBA – Join a Collection (Collection to Array)

Working with VBA is sometimes trivial  – you need to rewrite and reinvent the wheel every now and then 🙂 I mean, defining your own sorting method, your own test driven developement framework, etc. But, this is the funny &

Tagged with: , ,

VBA Online Resources (Ex documentation from StackOverflow) Excel Microsoft MVPs    The blogs above are one of the top Excel/VBA blogs that I know. If you are interested in exchanging VBA links, please feel free to contact me

Tagged with:

C# – CodeForces – Table Tennis

The Table Tennis problem in CodeForces was looking actually easier than the Borya’s Diagnosis. However, I have decided to publish it, because I have managed to “lie or cheat” the CodeForces system with it in a cool way. Currently, I

VBA Excel – How to convert range to array easily

Converting a range to array makes a lot of sense in VBA, when some operations should be performed with data in a given range. The time saving is quite noticeable and it is a bit easier to access and modify

Tagged with: , , , ,

VBA – RegEx in Excel

There is a well-known joke in the IT world – if you have a problem and you try RegEx to solve it, now you have two problems. Still, RegEx in VBA (and thus in Excel) exists as an additional library.

Tagged with: , , ,

VBA – Change Conditional Compilation Arguments in Access

VBA is not a scripting language. It compiles. As such, it has compilation arguments, which can be changed through code or manually. In MS Access you may find these here: VBEditor>Tools>VBA Project>Database Properties: If you want to use them for

Tagged with: , ,

Software Architecture Summit – Hitchhiker’s guide to Architecture Documentation

On Monday, I was at Berlin for the first day of the Software Architecture Summit.  There I took part at the Hitchhiker’s guide to Architecture Documentation workshop. This is the summary of what I learned: Hitchhiker’s Guide to Architecture Documentation In order

Tagged with: , , ,

C# – SoftUni – Bridges – Greed Algorithm

Some days ago I was checking the algorithm problems for the algorith exam preparation of SoftUni and I have decided to solve one problem. The problem is named “Bridges” (hence the youtube link above) and is a nice example of Greed

Tagged with: , ,

VBA – Listing all procedures in all modules

Sometimes you need to unite two projects into one in VBA. In these two projects, if you are using similar “own built libraries” you are in a kind of trouble. The trouble is called Compile Error :Ambiguous name detected: name Then

Tagged with: , , ,

PowerPoint VBA – Format All Forms in the active slide

In Powerpoint sometimes it is a bit boring (or challenging), when one has to make the shapes in a presentation the same size and to drag them to the same position. As far as there is no macro recorder there,

Tagged with: , ,

Excel VBA – Search and highlight text

Sometimes we have an excel file, where we want to highlight anything, matching a given cell. Without using the search & highlight option, if possible. Like this: Thus, in this case, whenever something in range A1 is written, the values

Tagged with: , , ,

Excel VBA – Activity Selection Algorithm

Wikipedia says that the activity selection problem is a combinatorial optimization problem concerning the selection of non-conflicting activities to perform within a given time frame, given a set of activities each marked by a start time (si) and finish time (fi). The problem is to select the maximum

Tagged with: , , ,

Excel – Use Sumproduct() instead of Sumifs() or Countifs()

Sumifs() and Countifs() are formulas in Excel, that sum or count a range with multiple conditions. Unfortunately, these formulas do not work, when you want to refer to a closed workbook. That’s where the magic of the Sumproduct() formula comes.

Tagged with: , , , , ,

Excel VBA – NetworkDays Formula

In Excel, there are plenty of ways to calculate the differences between days. However, the NetworkDays Formula includes weekends and may include the holidays, which would be different for each country/province. Thus, it is useful! Here is how to use

Tagged with: , ,

VBA – Knight’s tour algorithm with Excel

Wikipedia says the following for the Knight’s tour : A knight’s tour is a sequence of moves of a knight on a chessboard such that the knight visits every square only once. If the knight ends on a square that is

Tagged with: , ,

VBA vs C++ function – which one is faster in Excel?

Comparing the speed of VBA and C++ in general is quite not fair. Its like comparing the speed of a military jet and a Boeing 767. But still, it’s interesting to see how much C++ is faster in Excel’s native

Tagged with: , , ,

C++ – Adding C++ function to VBA/Excel

After adding a c# library to VBA I have decided to take a look how to do a similar trick with C++. C++ is a bit fancier in a way. And it works somehow else. So, let’s start. Imagine that we

Tagged with: , , ,

Oasis SVN – Software Review – with Video! :)

In VitoshAcademy I try to be on the edge of VBA technologies. Unfortunately, this is not that difficult, as far as VBA is really like the hated child of Microsoft – it works quite a lot and it does a pretty

Tagged with: ,

Review – vbWatchdog – professional error tracking tool

As a VBA Developer, I am always interested in the latest technologies in the VBA world. This is not that difficult, as far as there is not plenty of new stuff there, thus whenever I see something interesting, I try

Tagged with: , ,

Excel VBA – Floating Point Numbers in Excel – Not Exact

Everyone works with floating point numbers daily. If you go at a supermarket, the prices are like this: 11 eur 29 cents, 23 eur 35 cents and etc. As a C# developer and a blogger, I knew that there is some

Tagged with: , ,

C# – EF – Beginning with Entity Framework

What is Entity Framework? If we take a look at Wikipedia, it says Entity Framework (EF) is an open source object-relational mapping (ORM) framework for ADO.NET. What does it mean? Pretty much, magic. 🙂 You make some classes in C# and

Tagged with: , ,

VBA – The perfect userform in VBA

Somewhere in the summer of 2012 I have started coding in VBA. A little less than 5 years have passed since that very moment when I have pressed Alt+F11 for a first time and I have entered another Excel world.

Tagged with: , , ,

VBA – Code stops unexpectedly with “Code execution has been Interrupted” – Ghost Break

Ghost breaks in Excel can turn you crazy. Probably. If you do not know what to do and how to google them. Pretty much your code is perfect, it has worked 1000 of times, it compiles perfectly and still you

Tagged with: , , , ,

VBA – Protect your code – Unviewable+ and the Ribbon Commander

Let’s state it straight every VBA developer is happy, when he can protect his own code. The VBE password is not secure protection, everyone with a few free hours and a good internet access can go around it. Thus, I was

Tagged with: , ,

VBA – Add a C# library to a VBA project – Part 2

After writing the simple part here I think that it is a really good idea to introduce IntelliSense as well 🙂 Something like this in the code:   And like this is in the library:   The question is always one

Tagged with: , ,

VBA – Add a C# library to a VBA project

For today I was thinking to present an algorithmic problem, but somehow I have decided to show something more useful. It is a Step-by-step instruction for the creation of a C# object in VBA. You know, that references, which you

Tagged with: , ,