Most probably you feel like you have a good clue already how to locate a value in a given Excel row. There are plenty of ways – Find() , looking through the cells one by one, looking through the Internet for…
Category: VBA \ Excel
VBA is not a scripting language… Not at all!
How to declare as decimal in VBA
More than 3 years ago, I wrote an article about floating point numbers in Excel and a tiny problem with those, concerning all floating point numbers. This article is actually a nice one and it is worth reading – What…
VBA – Convert Month Name to Number and Vice Versa
Convert number to name – [3 -> March] Converting month number to name in VBA is actually coming out of the box with the MonthName() function, which is built-in the stanard VBA library:
|
1 2 3 4 5 6 7 8 9 |
Sub MonthNameExample() Dim i As Long For i = 1 To 12 Debug.Print MonthName(i) Debug.Print Format(MonthName(i, True)) Next End Sub |
The result is not flabbergasting, but…
VBA Debugging – Video
I have decided to make a 19 minute YouTube video, to explain the basics of VBA debugging, as there are quite a few tricks that not everyone knows. The agenda: Properties Window -> F4 Immediate Window -> Ctrl + G…
VBA – Copying Excel worksheet to a new workbook without leaving the old Excel reference
Copying excel worksheets around is actually a piece of cake for a VBA developer (or so they/we think). Mainly because you can record the actions and then “learn” from them, editing a bit the code and etc. Well, there is…
VBA – Copy Worksheet
Copying worksheet in VBA is actually a trivial task – use the macro recorder see the code and use it further. Anyway, if you are coding professionally with VBA (yup, we still exist:), you somehow get stomach problems, whenever you…
Python – The End of the Matrix Wandering
Ok. This article is for all the people, who have written code like this one:
|
1 2 3 4 |
if direction == "left" then if column > 0 then: new_row = current_row new_col = current_col - 1 |
When they were solving a problem like this one: You know who you are. You are probably thinking what is wrong with it? After…
VBA – String.Format or how to put variables in a string
C#, Python, Java and all the other “fancy” languages have a built-in method, which writes variables into a string. VBA does not have one. Depending on why do we need it, and if we can guarantee that no injection would…
VBA – Make a list of the formula errors in an Excel
Errors in Excel are different – from “Division by Zero” (#Div/0) and #Name to #REF! and #Num. There are lots of ways to summarize these into some kind of report and in the current article I will show the VBA…
VBA Boilerplate
Dreaming of having a boilerplate for Excel VBA project? Or you have other dreams, which are more normal? Well, I cannot help in the latter case, but if you want a boilerplate for VBA – here it is!
Writing data from text file to Excel cells
There are quite a few methods for writing data from a text file to Excel. The main are the following: Through a Query Table Through opening the text file and Reading and writing line by line Reading the text file,…
Tagged with: Data Transfer, from text file, LoopThroughLines, QueryTables.Add, to excel cells, Writing data
VBA – Extracting financial data from a website in table format
Although there are plenty of other tools for extracting data from a website (take a look at Beautiful Soup), VBA is somehow good, because … well, because it is somehow challenging to do it every time. Yesterday, I answered a…
Tagged with: Data scraping, from cell, from internet, reading data, reading from table, remove empty elements from array, VBA
Excel – How to build magic square with VBA, following Python Mod Mathematics
Some 4 years ago I wrote an article for calculating magic square with python. What the article did was actually checking whether the square is magic, and not calculating a new one. This one is going to show how these…
Dynamic dropdown list in Excel VBA with variables
Dropdown lists in Excel usually are built from a range or from a variable list. Dropdown list from Excel Range Thus, building a dynamic dropdown list may include building a dynamic range, with undeclared end. Thus, let’s imagine that the…
Write Formulas in Excel through Python XlsWriter
Writing formulas with Excel with Python is a technique, that needs some practice, actually. Although XlsWriter has some good documentaion, not a lot is covered on the point, that concerns creating a file with a formula in it. First and…
Python – The birthday paradox algorithm
The birthday paradox is an interesting problem, mainly because of its somehow “unexpected” results. The problem usually is stated as the following: What is the minimum number of people in a room, in order to have a probability higher than…
VBA – How to implement workbook events in Excel – GIF
The Excel Workbook object is a part of the Workbooks collection. The events are the code, which is “activated” or “triggered”, once a specific event happens. These events are different and could be unlimited, but there are a few built-in Workbook…
VBA – How to avoid naming variables with non-latin alphabet characters (special letters)
Writing code in the local language is actually taught by about 100% of the German VBA books, which I have read. Which is “kind of ok”, if the VBA code only works on German computers. However, if this is not…
VBA – Color Only Half of the Border of an Excel Cell
Coloring the border of an Excel cell is a trivial task. The LineColor feature in Excel is easy to use out of the box: However, whenever half of the cell needs to be colored, the trick is a bit more tough…
VBA – Longest Palindromic Subsequence Algorithm with Excel – GIF
After writing about the longest palindromic substring, now it is time to see the longest palindromic subsequence. What is the difference? Pretty much, if we take the word abracadabra: the longest substring is ada in abracadabra the longest subsequence is…
VBA – Longest Palindromic Substring Algorithm with Excel – GIF
The longest palindromic substring algorithm exists since 1975. It is different (and easier) than the longest palindromic subsequence. The idea of the substring is to return “anana”, if “bananazorro” is given. This is achieved with a 2 dimensional boolean array matrix and…
VBA – Insert an image/picture in Excel and position it correctly
Inserting an image to Excel with VBA is actually one line: ThisWorkbook.Worksheets(1).Pictures.Insert ("C:\Users\myPic.png") The interesting part happens, when it should be correctly positioned. In order to position it the way we want, we need to give 3 parameters – the image…
VBA – Put a Collection inside a Scripting Dicitonary OR Make an Updateable ComboBox
The idea of the article is to make an updateable combobox, which takes all the keys of column A and writes them to the ComboBox. Then, upon selection of the corresponding values in the combobox, it provides the corresponding values…
VBA – ListBox in Excel – Adding, Editing and Removing Data
The idea of the article is to show how to add, edit and remove specific entries of the ListBox in Excel through VBA: ListBox is a control, which is available through Developer>Insert>ActiveX>ListBox in Excel: Once you add it, it has…
VBA – Reverse words in a sentence
Reversing words in a sentence with VBA in Excel is quite easy, using the built-in StrReverse function. StrReverse takes a string and reverses it. Thus, “VitoshAcademy.com” becomes “moc.ymedacAhsotiV”: The idea of reversing the order of the words in a sentence…
VBA – Cells and Ranges in Excel (A bit more than the standard story)
Excel’s ranges and cells are both complicated and very simple. They are simple, because everyone has an idea what is a range and what is a cell, and they are complicated, because these are both: Properties of the worksheet object…
VBA – Benford Analysis (First-Digit Law)
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…
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…
Top