Imagine you have a task to sort the range from column A based on the number after the last digit. A quick decision is to use Column “B” as a helping column where you write the cell, splitted by the…
Category: VBA Excel Tricks
VBA is not a scripting language…
VBA – Delete / Remove rows from a specific worksheet in Excel
Imagine one of the most trivial tasks in Excel – remove rows from Excel, for which contain the number 1 in their first column. Or in the column below, these are the rows, which are selected. If you are familiar with…
VBA – Avoid nested loops with recursion (Part 2)
About two years after the “Nested loops with recursion” today I was looking for a way to avoid nested loops with recursion in google and found my article 🙂 . Lots has changed in my coding style since then as…
VBA – Formulas in Excel LocalFormula
The formulas in Excel, used through VBA are not easy. You need to write a string like “=IFERROR(OR((1=A1),(2=A2)),””No Error””,””Error””)” and this is really not easy and fun to debug. Furthermore, if it is false, VBA will nicely throw a runtime…
VBA – Difference between Trim, VBA.Trim, =Trim() and WorksheetFunction.Trim
Trimming is a function, that reduces spaces. There are at least 4 ways to Trim a string in Excel/VBA: Trim VBA.Trim =Trim() WorksheetFunction.Trim Thus, is there a difference between these 4 actually? This is what MSDN says about String.Trim: Returns…
VBA – What day is Friday in the week of a given date?
Imagine that you want to check when is Friday for the week of Christmas in the next 10 years. In general, the week of Christmas is the week that contains the 25.December. Getting a given day from a given week…
VBA – Why Range.Find() sometimes mistakes January with November and Febrary with December?
After writing that the built-in Find() function in VBA does not return the first value by default, a new interesting case with the Find function appeared. In order to simulate it, the following task could be imagined: Write the first…
VBA – Select a Single Node XML
Selecting and working with XML in Excel and VBA is always a bit challenging. Whenever a programmer (or a developer or an analyst or a portfolio controller) hears about XML and VBA, something weird happens in their minds. The programmer…
VBA – ArrayList class – a helper to sort arrays in VBA
If you are coding professionally in VBA, most probably you are using arrays every now and then and you have your own function for sorting an array. Something like this bubble sort or anything similar is what you have written…
Algorithm testing system, reading from text file with VBA
For the people, familiar with my blog, it is known that usually I am resolving problems from Codeforces.com. Codeforces supports plenty of languages, unfortunately VBA is not one of them. Thus, I have decided to build up a small algorithm…
The dates in Excel, VBA and SQL Server
There is a lot over the Internet, written for the dates in Excel, VBA and SQL Server, but still, there is quite much to be written. In general, it all has started, when the developers of Lotus 1-2-3 have decided…
VBA – Sum array with recursion
This is what Wikipedia says about Recursion: Recursion occurs when a thing is defined in terms of itself or of its type. Recursion is used in a variety of disciplines ranging from linguistics to logic. The most common application of recursion is in mathematics and…
VBA – How to refer a function ByVal, when the parameter is explicitly ByRef
Ok, you have read the title correctly – there is a way to refer a parameter ByValue, even though the parameter contains explicitly the word ByRef in it. Let’s start with the functions. Imagine a function, getting an array as parameter…
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…
VBA – Type Declaration Suffixes
If you are coming from the .Net world, then you are used to declare variables like this:
|
1 |
Dim foo, bar as Long |
In this case, both foo and bar are of type Long. Then, someone tells you that you want to switch for a…
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…
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…
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 &…
VBA Online Resources
cpearson.com rondebruin.nl msdn.microsoft.com/en-us/vba/vba-language-reference Riptutorial.com (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 at review@vitoshacademy.com
Tagged with: vba online resources
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…
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.…
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…
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…
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…
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…
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,…
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…
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…
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.…
Top