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…
Tag: Excel
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 – 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,…
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 – 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 – 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:
|
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…
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…
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…
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…
.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…
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…
VBA – Formulas in Excel LocalFormula
The formulas in Excel, used through VBA are not easy. You need to write a string like “=IFERROR(OR((1=A1),(2=A2)),””No Error””,””Error””)” and this is really not easy and fun to debug. Furthermore, if it is false, VBA will nicely throw a runtime…
VBA – Difference between Trim, VBA.Trim, =Trim() and WorksheetFunction.Trim
Trimming is a function, that reduces spaces. There are at least 4 ways to Trim a string in Excel/VBA: Trim VBA.Trim =Trim() WorksheetFunction.Trim Thus, is there a difference between these 4 actually? This is what MSDN says about String.Trim: Returns…
C# – Looking for a value in Excel with C# & VisualStudio
Using Visual Studio and Excel is actually quite fun – the Visual Studio advanced environment provides a bit more possibilities than the Visual Basic Editor, built-in Excel. In this article, about a year ago I have shown how to loop…
Why Range.Find() sometimes mistakes January with November and Febrary with December in VBA?
After writing that the built-in Find() function in VBA does not return the first value by default, a new interesting case with the Find function appeared. In order to simulate it, the following task could be imagined: Write the first…
VBA – ArrayList class – a helper to sort arrays in VBA
If you are coding professionally in VBA, most probably you are using arrays every now and then and you have your own function for sorting an array. Something like this bubble sort or anything similar is what you have written…
The dates in Excel, VBA and SQL Server
There is a lot over the Internet, written for the dates in Excel, VBA and SQL Server, but still, there is quite much to be written. In general, it all has started, when the developers of Lotus 1-2-3 have decided…
VBA – RegEx in Excel – Part 2
After the first part about RegEx in Excel lately I had to do something else with RegEx. The task was quite simple, pretty much called “stripping a string”. The idea is that you have a string and you should remove all…
VBA Excel – How to convert range to array easily
Converting a range to array makes a lot of sense in VBA, when some operations should be performed with data in a given range. The time saving is quite noticeable and it is a bit easier to access and modify…
VBA – RegEx in Excel
There is a well-known joke in the IT world – if you have a problem and you try RegEx to solve it, now you have two problems. Still, RegEx in VBA (and thus in Excel) exists as an additional library.…
VBA – Listing all procedures in all modules
Sometimes you need to unite two projects into one in VBA. In these two projects, if you are using similar “own built libraries” you are in a kind of trouble. The trouble is called Compile Error :Ambiguous name detected: name Then…
Excel VBA – Search and highlight text
Sometimes we have an excel file, where we want to highlight anything, matching a given cell. Without using the search & highlight option, if possible. Like this: Thus, in this case, whenever something in range A1 is written, the values…
Excel VBA – Activity Selection Algorithm
Wikipedia says that the activity selection problem is a combinatorial optimization problem concerning the selection of non-conflicting activities to perform within a given time frame, given a set of activities each marked by a start time (si) and finish time (fi). The problem is to select the maximum…
Excel – Use Sumproduct() instead of Sumifs() or Countifs()
Sumifs() and Countifs() are formulas in Excel, that sum or count a range with multiple conditions. Unfortunately, these formulas do not work, when you want to refer to a closed workbook. That’s where the magic of the Sumproduct() formula comes.…
Excel VBA – NetworkDays Formula
In Excel, there are plenty of ways to calculate the differences between days. However, the NetworkDays Formula includes weekends and may include the holidays, which would be different for each country/province. Thus, it is useful! Here is how to use…
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,…