VBA – Read different levels of XML
After the article here for VBA – Select a Single Node XML now I am writing an update from it, which shows how to loop through all the nodes that we are interested in and get data from them. The XML is …
Tag: VBA
VBA – Fill Numbers in a Given Range
So, the idea is the following – imagine that you have an NxN range, which should be filled out with consecutive numbers. Thus, if the range is 6×6, you start filling the numbers normally and once you reach the 6. …
Format and Color Text in Excel Cell with VBA
Formatting a substring of a string in an Excel Cell is actually quite a challenging task. There are two ways to do it – select part of the substring that you wish to format and change its color. Then make …
How to Change Tab Color in Excel with VBA
Changing tab color of Excel is somehow useful. With VBA, it is easily programmed, as far as one can use the macro recorder to see the correct way to do it. However, if you need to change the color of …
VBA – Using Parameters in a VBA SQL Query To a Database
In this article I will simply use the code from my first guest author Mathieu, to build up some small working solution, that uses parameters: So, the two classes, provided by Mathieu are here as well GitHub, named AdoValueConventer.cls and SqlCommand.cls. …
VBA – Using Parameters in a VBA SQL Query To a Database Read more →
VBA – Select Multiple Cells in a Excel Range
Selecting multiple cells in Excel through VBA may be quite a difficult task. Although, using Select is not always a good practice, sometimes it should be done. To generate some numeric values, let’s run the following: Public Sub FullMe() Dim myCell …
VBA – Simulation of Rolling Dices
Excel and VBA are a powerful tool for simulation. The idea is to simulate dice throwing and to keep the best N throws as our score. Other model variables are Times I roll, which stands for how many times do …
VBA – Markov Chain with Excel example
Markov model is a a stochastic model describing a sequence of possible events in which the probability of each event depends only on the state attained in the previous event. Wikipedia This is a good introduction video for the Markov …
VBA – How to Change Merged cell in Excel with Events
The worksheet events which are built within VBA are quite useful. One may build them quite easily in Excel and they work satisfactory. However, let’s imagine, that you have a merged cell as in the picture above and you want …
VBA – How to Change Merged cell in Excel with Events Read more →
Automagic & Secure ADODB
VitoshAcademy has the pleasure to welcome its first guest author – Mr. Mathieu Guindon. He is the project manager behind Rubberduck – an open-source COM add-in project written in C#, extending the glorious VBE with modern-day IDE features. On Code Review and Stack Overflow he is closely monitoring …
“OMG I CAN DO VB6 IN EXCEL! WATCH ME!!!” – Mathieu Guindon about VBA 15 years ago
Mathieu Guindon is the project manager behind Rubberduck – an open-source COM add-in project written in C#, extending the glorious VBE with modern-day IDE features. On Code Review and Stack Overflow he is closely monitoring the vba tag. Ex-moderator on Code Review (2015-2018), Microsoft Excel MVP (2018). …
“OMG I CAN DO VB6 IN EXCEL! WATCH ME!!!” – Mathieu Guindon about VBA 15 years ago Read more →
VBA – How to Find TaxiCab Numbers in Excel – Quick Search in Array
Quick Search for value in Array. In VBA this is sometimes considered problem, as far as people tend to loop through the whole array for the search. There is a better way to do it, with System.Collections.ArrayList, which is not exactly …
VBA – How to Find TaxiCab Numbers in Excel – Quick Search in Array Read more →
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 – Delete / Remove rows from a specific worksheet in Excel Read more →
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 – Difference between Trim, VBA.Trim, =Trim() and WorksheetFunction.Trim Read more →
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 – What day is Friday in the week of a given date? Read more →
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 …
VBA – ArrayList class – a helper to sort arrays in VBA Read more →
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 …
Algorithm testing system, reading from text file with VBA Read more →
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 – How to refer a function ByVal, when the parameter is explicitly ByRef Read more →
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 – Find function in Excel does not return the first value by default Read more →
VBA – Type Declaration Suffixes
If you are coming from the .Net world, then you are used to declare variables like this: Dim foo, bar as Long In this case, both foo and bar are of type Long. Then, someone tells you that you want …
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 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 Excel – How to convert range to array easily Read more →