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…
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…
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…
Some days ago I have decided to take a look at one of the homeworks problem of the OOP course at SoftUni. Then, I have decided to solve it with my VBA OOP knowledge, implementing it in C#. Whether VBA…
Abstract Classes in C# are classes, intended only to be a base class of other classes. Members marked as abstract, or included in an abstract class, must be implemented by classes that derive from the abstract class. Reference With other…
If you want to create a custom shortcut to a command in Visual Studio, these are the steps to be taken: Ribbon>Tools>Options Environment>Keyboard Show commands containing (write the command) Press shortcut keys Assign and OK
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…
After the previous two articles for C# – Foreign Keys in Entity Framework – Code First – Query with Linq and Entity Framework – Linq vs Extension Methods (Example) it is a good idea to present some actions with LINQPad. LINQPad5 is…
In this article, I present an Entity Framework example, building two tables with foreign keys, using code first. The sample I am using is building over the previous article for EF. Pretty much, when you are running your application, if…
This article presents a simple example of querying with Linq and Extension Methods in Entity Framework (EF). To set the EF, create a C# Console application. Then add the Package Manager Console from Tools: Then run Install-Package EntityFramework in the Package…
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…
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…
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:…
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…
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…
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 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…
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…
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…
Imagine, that you are given a string in C# with some separator in it, and you need to split it and refer to the first or the second part of the splitted string. E.g., you are having *AAAaa*SEPARATOR*zzZZZ* and with one line,…
Sometimes, but only sometimes, someone needs to handle specific VBA errors and to avoid them. Or to ignore them. Or anything like this. Let’s say, you want to avoid error number 91, because you think that it is a case…
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…
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…
Russ Ferguson and Keith Cirkel have managed to write an interesting book about JavaScript with some interesting knowledge. As I have read plenty of JS books and I have almost never worked professionally with JS, I am always eager to…
=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…
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…
Working with VBA is usually not considered high level of programming. Still, when it comes to Excel, this is probably the best solution. However, Python has some libraries which can help a bit, if you are willing to “mimic” the…
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.…
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…
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,…