Long time ago, an astronomer called Benford was taking a look at logarithm books, noticing that the earlier pages (starting with 1) are pretty much more worn out than the later ones. He thought about the result, and managed to…
Category: VBA \ Excel
VBA is not a scripting language… Not at all!
VBA – Dictionary
VBA has a dictionary structure. Dictionary is an object, and it can be referenced either with early binding or with a late binding, referring to Microsoft Scripting Runtime: What is a dictionary? This is the Microsoft definition of the VBA…
C# – CodeForces – From Hero to Zero
It was some time ago, since I last took a look at programming challange in CodeForces, thus I have decided to take a look again. So, I chose the lowest problem of the second division just to boost up my…
Top VBA-Excel errors
Some months ago #VBA was finally considered the most dreaded language in the 2019 StackOverflow survey: One of the reasons for this, is probably that #VBA is quite too easy to enter, thus plenty of non-IT professionals feel fascinated about…
VBA – Abstract classes. Classes, that cannot be instantiated.
There are parts of #VBA, that can scare any VBA developer, who has not bumped into other languages. Although, the “abstract class” term is quite popular in C# and other languages, and is quite easy to be created, in VBA,…
C# – Reading from Excel Spreadsheets with Asynchronous Programming (Async)
Reading from Excel in C# is actally not at tough as many VBA developers would assume. The simple reading is actually quite straightforward – there is only small little trick, requiring the adding of the Microsoft.Office.Interop.Excel reference to the Visual…
C# – How to get the last filled cell of a given row in Excel
Getting the last row of a given column in Excel with a formula or with VBA is quite a trivial task. It is explained here quite well 🙂 However, when we come to C#, the only “trick” that is provided…
C# – Find the Biggest Square in a Matrix – What can Excel and VBA add to it?
Finding the biggest square of specific units in a given matrix of these units is a standard dynamic programming problem. The examples in Google for it are above 18 million, thus I would simply show how it is done with…
VBA – How to copy a new object in VBA, without copying its reference
Copying an object in VBA to a new object is somehow tough. If you use Set foo = bar then, both foo and bar would be pointing to the same address in the heap. And if you change the properties of one, you would automatically…
VBA – Environmental Variables
The environmental variables are a dynamic-named values that can affect the way running processes will behave on a computer. With other words, if you want to see them in your PC, simply open the command prompt and write the nice word…
VBA – Data scraping from Internet with Excel – Part 2
Some time ago I wrote an article for Data scraping from Internet with Excel , which was scraping book information from amazon.com, based on a given word. Thus, for a keyword as “VBA”, this is the data, printed in the immediate…
VBA – Add Shape to Range in Excel
Adding shapes to a range in VBA with Excel is actually a trivial task, if you are aware of the Shapes.AddShape method. It has 5 parameters, 4 of which could be pretty easily remapped to the parameters of the range:…
VBA – Animation with Excel
Animation consists of some pictures. In general, there are about 24 pictures per second, in order to do the “magic”. This could be easily automated with Excel, using Application.Wait (Now + #12:00:01 AM#), if we agree to make it a bit old-fashioned, with 1 picture…
VBA – Extracting text from string between two identical characters using VBA
Automation of Excel is actually pretty interesting task. VBA is indeed the best built-in tool for this and although there is a lot of “hate” towards it it really deserves to be taken into account seriously, at least for small daily…
VBA – RegEx Used to Take the Valuable Data Out
Writing for RegEx in VBA is sometimes a good idea and sometimes a bad idea. Anyway, if you need to write one, you better be careful. These are the RegEx articles in VBA in VitoshAcademy so far: https://www.vitoshacademy.com/vba-regex-in-excel-part-2/ https://www.vitoshacademy.com/vba-regex-in-excel/ So,…
VBA – AutoMacro AddIn – VBA Software Review
VBA is extremely underestimated programming language. I have always been thinking that there are almost no real #VBA developers, because the “real” developers do not want to do some meaningless “macro” stuff and the people who work with #VBA are…
VBA – How to check messages for specific words before sending them with Outlook
Gmail has the option to revert a message for about 10 seconds after you have sent it: Well, Outlook with VBA can do similar things, if you are willing to program it a bit with VBA. Let’s say that you…
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:
|
1 2 3 4 5 |
Function LastRow(wsName As String, Optional columnToCheck As Long = 1) As Long Dim ws As Worksheet Set ws = Worksheets(wsName) LastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row End Function |
There are two other formulas, which would get the last used cell in a given row, depending on whether it…
VBA – Error handling
Error handiling in VBA is a standard – once the error occurs, something should happen to prevent “breaking” the whole program. And usually this “something” is a MsgBox with information for the error. General error handling in VBA The general…
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…
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…
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…
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.…
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,…
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Public Sub FullMe() Dim myCell As Range Dim myRange As Range Dim cnt As Long Set myRange = Range("A1:F20") For Each myCell In myRange myCell = cnt If cnt = 6 Then cnt = 1 cnt = cnt + 1 Next myCell End Sub |
After this, we have…
Top
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,…