
Have you ever got your connectors in MS Word not in line? Like this one: The good and intended way to do it, was pushed by Microsoft some time ago – go buy yourself MS Visio. But, if you do…
VBA is not a scripting language… Not at all!
Have you ever got your connectors in MS Word not in line? Like this one: The good and intended way to do it, was pushed by Microsoft some time ago – go buy yourself MS Visio. But, if you do…
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…
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…
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…
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…
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…
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…
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…
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…
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 – 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…