Author: Vitosh

Last Used Row (Last Used Column) in Excel with Formula (without VBA)

Getting the last row of a given column in Excel is easily done with a User-Defined Function in VBA:

There are two other formulas, which would get the last used cell in a given row, depending on whether it

Tagged with: , , ,

C# – Split String in One Line and Get the First SubString

Imagine, that you are given a string in C# with some separator in it, and you need to split it and refer to the first or the second part of the splitted string. E.g., you are having  *AAAaa*SEPARATOR*zzZZZ* and with one line,

Tagged with: ,

VBA – How to handle only specific errors in VBA

Sometimes, but only sometimes, someone needs to handle specific VBA errors and to avoid them. Or to ignore them. Or anything like this. Let’s say, you want to avoid error number 91, because you think that it is a case

Tagged with: , ,

Add comments in Excel with VBA

Adding comments with VBA is somehow quite a trivial task, if you already have the text of the comment and you are allowed to delete the comment if it is present. In general, the only difficult part is the check,

Tagged with: , , ,

Call Stack Dialog Box in VBA

Calling the stack dialog box in VBA is actually a very useful feature of the VBA Editor. This is what the Microsoft documentation says about it: Displays a list of currently active procedure calls during break mode. When executing code

VBA – Data scraping from Internet with Excel

Sometimes, you may need to scrape some structured data from the internet with Excel. Honestly, Excel. Honestly, Excel should be your last weapon of choice there, as far as it is really not a web-scraping software. Still, it is quite

Tagged with: ,

Book Review – Java Script Recipies

Russ Ferguson and Keith Cirkel have managed to write an interesting book about JavaScript with some interesting knowledge. As I have read plenty of JS books and I have almost never worked professionally with JS, I am always eager to

Tagged with: , ,

Excel – Get Top Row of a Multi Row / Mult Column Range

=VLOOKUP and =INDEX(MATCH(),MATCH()) are well known formulas, if you need to get some value corresponding to another value from a specific column. The problem comes, when you are having more than one column or row, and you need to locate the

Tagged with: , , ,

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

Tagged with: , , ,

Python – Write Values in all Worksheets of existing Excel File

Working with VBA is usually not considered high level of programming. Still, when it comes to Excel, this is probably the best solution. However, Python has some libraries which can help a bit, if you are willing to “mimic” the

Tagged with: , ,

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.

Tagged with: , , ,

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

Tagged with: , ,

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

Tagged with: , ,

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.

Tagged with: , , ,

C# – Casino Black and Red Simulation – is it Worthy?

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

Tagged with: , , ,

Book Review – Bad Programming Practices 101

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

Tagged with: ,

Run Python Functions From Excel With VBA with Shell through CommandPrompt

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,

Tagged with: , , ,

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 random values, let’s run the following:

After this, we have

Tagged with: , ,

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

Tagged with: , , , ,

How to Search In Excel Worksheet with Python

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

Tagged with: , , ,

Python – Simple writing in Excel with XlsWriter

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

Tagged with: , ,

VBA – Hide the Named Ranges in Excel

Named ranges are really nice parts of Excel – you just give a name to the range and you start referring it by it. No more Z56 or AM31, the ranges start having names the way you like them. So, one

Tagged with: , , ,

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

Tagged with: , , , , ,

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

Tagged with: , , ,

Book Review – Blockchain Basics

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.

Tagged with: ,

“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).  

Tagged with: ,

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

Tagged with: , , ,

Sort a range in VBA with System.Collections.ArrayList Numerically

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

Tagged with: , ,

.Net – Reading Excel file in C# and transferring it to a matrix

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

Tagged with: , , , ,

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

Tagged with: , , , ,
Top