If you are one of the many VBA developers, then most probably you have been asked once or twice to download some file from a local eshare or site. Before downloading it, it really makes sense to check, whether this…
Category: VBA \ Excel
VBA is not a scripting language… Not at all!
VBA – Insert Into String
Inserting into string in Excel should be a trivial task, but it always makes sense to have something prepared in the boilerplate, before you start working. Ok, why is it important to have a nice function, if something like this…
Python – Write a DataFrame or List to multiple tabs in Excel
The idea of the article is to provide a Python code that does the following: Takes a list or DataFrame; Splits it into quite a few smaller parts; Writes each one on a separate Excel worksheet; Pretty much like this:…
VBA – Split worksheet to worksheets, save Excel worksheets to csv
Same article, but for Python is here – https://www.vitoshacademy.com/python-split-worksheet-to-worksheets-save-excel-worksheets-to-csv/ This article does 2 things: Splits one worksheet to multiple worksheets Then goes through the worksheets and saves them as *.CSV files I hope that is enough for you. Ok, so…
VBA – Option Compare (Text || Binary || Database)
Option Compare in VBA is the thing, that will save you ugly stuff like this one:
|
1 2 3 4 5 6 7 8 9 10 |
Sub Testing() Dim userInput As String userInput = InputBox("What is decimal 4094 in hex?") If UCase(userInput) = UCase("ffe") Then Debug.Print "You have correctly written " + userInput + "!" End If End Sub |
Yup, it is a bit ugly, using UCase to compare all the time, between small letter text and big letter text. So, if you…
VBA – Get Substring Between 2 Substrings – Locate value of Nth XML
Getting N-th string between two substrings might sound a bad idea, until you do not need it. And with VBA, you better have something prepared. So, imagine the following XML (from this article here):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<FootballInfo> <row> <ID>1</ID> <FirstName>Peter</FirstName> <LastName>The Keeper</LastName> <Club name ="NorthClub"> <ClubCoach>Pesho</ClubCoach> <ClubManager>Partan</ClubManager> <ClubEstablishedOn>1994</ClubEstablishedOn> </Club> <CityID>1</CityID> </row> <row name="Row2"> <ID>2</ID> <FirstName>Ivan</FirstName> <LastName>Mitov</LastName> <Club name = "EastClub"> <ClubCoach>Gosho</ClubCoach> <ClubManager>Goshan</ClubManager> <ClubEstablishedOn>1889</ClubEstablishedOn> </Club> <CityID>2</CityID> </row> </FootballInfo> |
Your task is to get…
Tagged with: between substrings, locate, mid, str, string, string between 2 strings, Substring Between 2 Substrings, VBA, xml
How to get redirect url using Excel VBA or Python
Getting some tiny URL, that redirects you to somewhere else might be dangerous. There are a lot of people, that would never ever click on bit.ly/something or goo.gl/something, just because they do not know where the redirect is going to…
VBA – VLookup with Multiple Criteria in Excel without Excel Formula but with VBA
Ok, the title became too lengthy, but the idea is to try to duplicate the beauty of what this formula does:
|
1 2 3 4 |
=INDEX(myTable[#All],MATCH(1, (myTable[[#All],[Profit]]=C9)* (myTable[[#All],[Currency]]=D9)* (myTable[[#All],[Value]]=B9),0),1) |
And this is the cell of the formula above: Anyway, the strange thing was that we have beautiful…
Python – Read and Write to Excel ActiveX Textbox
Reading and writing to an Excel ActiveX textbox with Python can be fun. If you really do not have anything better to do. Anyway, if this is your current task, you are on the correct place to see how it…
Python – How to fill Excel cell with image, using python
If you have ever dreamed that it will be possible to fill out an Excel cell with a picture, using Python, then you should probably reevaluate your dreams. Seriously, who dreams of something like that? Without further dreaming, the code…
VBA – Printing Dictionary of a Dictionary of a Dictionary…
Working with dictionaries in VBA is a bit more unpleasant, than working with dictionaries with almost any other language. Because of the VBA IDE, you only see the keys, but not the values. Thus, if you have experience with Visual…
VBA – Automatically Save Excel File with VBA
Saving Excel files, when working with VBA is unfortunately a bit of pain the ass. You always need to make a new version every now and then, because it does not matter how seasoned VBA developer you are, still there…
VBA – Ubound of Multidimensional Array or How to Get What We Need?
Transforming Range to Array in VBA is actually a one liner – we take the range and we assign it to a variant:
|
1 2 3 4 5 |
Dim myRange As Range Set myRange = Range("a1").CurrentRegion Dim myArray As Variant myArray = myRange |
The “strange” part is that the array is converted to 2-dimensional array after this: and working…
VBA and Python – Intersection of dictionaries
Intersection of dictionary (or of any two lists) is a list with elements, that are present in both other lists. Thus, if we have the following 3 dictionaries in Python:
|
1 2 3 |
dict_a = dict([('aa', 4139), ('bb', 4127), ('cc', 4098)]) dict_b = dict(aa=4139, bb=4127, ee=4098) dict_c = {'aa':12, 'bb':13, 'dd':23} |
Then, an intersection of their keys will contain only…
VBA – IIF() vs IF()
What is the difference between IIF() and IF() in #VBA? The idea of IIF() is that it evaluates both the True and the False part of the condition, before evaluating the condition itself. Thus, if the False part throws an…
VBA – Hashing in Excel with HMACSHA1
Hashing strings in general is useful. It is one of the good things, that we get from granted by the various .Net libraries and we should not try to do on our own, as the HMACSHA1 is quite a powerful…
VBA – Convert Excel Column Number to Letter and Letter to Column Number
Converting Excel column number to letter and letter to column number is actually something that every VBA developer does at least once a day. The easiest way is probably to use the property .Column or .Address of the range object that…
Excel – Index(Match) based on multiple columns
Everyone knows the feeling that they need to use the =Index(Match(),Match()) formula in Excel and something tiny was not enough to reach their goal. Some time ago I knew that something is missing I was trying to “filter” with the…
How to fix – This workbook contains one or more links that cannot be updated…
Ok, if you are visiting this article, then probably the reason is that you have received the following messagebox upon openning of Excel: And after looking all over your cells, with both VBA and “Find”, you have not found anything…
VBA – Nested Dictionary
Nested dictionary means a dictionary within a dictionary. Some days ago last week I was trying to build one and it took me some solid 15 minutes to do it, thus I have decided that it is worth an article…
VBA – Folders and Files Functions
Check if folder is empty:
|
1 2 3 |
Public Function FolderIsEmpty(myPath As String) As Boolean FolderIsEmpty = CBool(Dir(myPath & "*.*") = "") End Function |
Delete all files in a folder:
|
1 2 3 |
Public Sub DeleteAllFiles(path As String) Kill path & "*.*" End Sub |
Create text file in a given path with text:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Public Sub CreateTextFile(path As String, fileName As String, text As String) Dim fso As Object Dim file As Object Set fso = CreateObject("Scripting.FileSystemObject") Set file = fso.CreateTextFile(path & fileName, True) file.WriteLine text file.Close End Sub |
All together:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub Main() Dim path As String path = "C:\Users\Username\Desktop\New folder\" CreateTextFile path, "to_delete.txt", "some text inside the file" If Not FolderIsEmpty(path) Then DeleteAllFiles path End If End Sub |
VBA – Source Control with Git – Video
VBA and source control? Do you know what is VBA? Are you sure we can source control the code, that resides in Excel? Oh, you mean that lengthy way of extracting the modules one by one? But why? Don’t you…
Using API calls with VBA and Python to Read and Write to a Database
Using API calls with VBA and Python to write a database is actually a pretty decent architecture. Here, I would like to say that “It takes the best of the both worlds”, but the truth is that it takes the…
VBA – Compute All Combinations of a Given Set
Yesterday I received an email, refering to some 2 old articles in VitoshAcademy for nested loops without recursion: VBA – Nested loops with recursion (Permutations) VBA – Avoid nested loops with recursion (Part 2) As far as the person from…
VBA – Cells and Ranges in Excel – Video
Cells and ranges in Excel are quite interesting, as these will always give some points to think of. About an year, after writing the article about VBA – Cells and Ranges in Excel (A bit more than the standard story),…
VBA – How to locate a value in an Excel row?
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…
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…
Top
VBA – Make Excel Comments Beautiful
Everyone, who has worked with Excel more than a year knows that the comments are actually quite useful – they allow us to put info data into a cell, without actually putting data in the cell. However, the standard way…