Going to a casino is probably quite an easy way to lose money. In the long term. Let’s say that you are following the strategy – always double when you lose and you bet on numbers above 18 on the …
C# – Casino Black and Red Simulation – is it Worthy? Read more »
Before learning how to program, one should learn how not to program – this is the idea of the book Bad Programming Practices 101, which was kindly provided to me for a review by Apress.com The book shows some bad …
Selecting Python for a “weapon” of choice has many positive points, which I am not going to describe now. Still, if you want to run python code with the help of VBA and the Shell through the CommandPrompt of Windows, …
Run Python Functions From Excel With VBA with Shell through CommandPrompt Read more »
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 …
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 …
Working with Python and Excel together is really fun for a VBA Developer! It somehow shows you what all these years of coding with VBA have taught you! Today, I was thinking how to implement the .Find() method of searching in …
The XlsxWriter library of Python is a nice gem for a VBA developer – you would be really interested to see the problems a Python developer should be facing, just to achieve a simple part Excel automatization. Still, these are …
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 …
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 »
I have missed the probably* easiest way to become rich in the last 1000 years – BitCoin. At the beginning I was thinking it is too small to care about and then I thought it is too expensive to buy. …
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 »
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 »
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 …
Sort a range in VBA with System.Collections.ArrayList Numerically Read more »
There is an ERP joke, claiming that the third most popular button of an ERP system is “Export to Excel” (after OK and Cancel). Thus, working with Excel from .Net is happens often. There are some pitfalls, that you should …
.Net – Reading Excel file in C# and transferring it to a matrix Read more »
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 »
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 – Avoid nested loops with recursion (Part 2) Read more »
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 …
This month I have passed Exam 70-462: Administering Microsoft SQL Server. Thus, officially I am a MCSA in SQL Server 2012/2014. Concerning the exam – it is tough! In order to pass it I have studied for about 6 months. The …
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 »
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 »
Using Visual Studio and Excel is actually quite fun – the Visual Studio advanced environment provides a bit more possibilities than the Visual Basic Editor, built-in Excel. In this article, about a year ago I have shown how to loop …
C# – Looking for a value in Excel with C# & VisualStudio Read more »
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 …
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 …
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 »
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 »
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 …
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 …
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 »
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 »
