To simplify the conversion of a variant array to a string array in Microsoft Excel we have made a set of utility functions. The method will also flatten any variant arrays within the source array (i.e. jagged arrays). Usage Here is an example on how to use the method called VariantArrayToStringArray: The method will return […]
Excel VBA: Custom SumIF function that ignores N/A
Below we have made a custom function which sums the values of one column/range given that the value of another column is equal to a fixed value. This function ignores the N/A values, which is not the case for the built-in function called SumIf in Excel. If the built-in function finds a N/A value the function returns N/A. […]
JavaScript: Get the Query argument of an URL
Often I have the need to parse the URL query to retrieve the value of a specific named argument. There is no direct way of doing this is JavaScript, so this requires a custom function. The strQuery argument is the full URL query i.e. a=val1&b=val2 etc. The full URL should not be included.strArgumentName is the name of the […]
.NET: Alternatives to the .NET Reflector
.NET Reflector is dead, long live ILSpy. Well, .NET Reflector is not really dead, as you might already know, but you now have to pay if you want to use the disassembler. Below we have listed the most common alternatives to the previously popular tool: ILSpy dotPeek JustDecompile Mono Cecil Kaliro Dotnet IL Editor (DILE) […]
Excel VBA: Re-throw error/exceptions
The current version of Microsoft Excel does not support the new Try/Catch blocks found in VB.net or any other modern programming languages. To handle errors within a procedure the “On Error Goto” statement must be used. When used, VBA expects the procedure to handle the error by either displaying a message visible to the user […]
Excel VBA: Download files from the Internet
There is no built-in function in Microsoft Excel which allows you to download contents from the Internet on the fly. To accomplish this task we need to use the API for WinInet. The use and explanation of API in VBA is for advanced users which have prior experience from either Visual Basic 6.0 or .NET. Pitfalls […]
Excel VBA: Using .NET string.Format in Excel
The functions string.Format and StringBuilder.AppendFormat are two very usefull functions for formatting strings and increasing the readability of your .NET code. The Format function in VBA unfortunately works in a quite different way than the string.Format function in .NET. As far as I know there is no built-in function in VBA to acomplish the exactly same result as string.Format. In Excel VBA the […]
Excel VBA: Adding custom buttons to the Right-Click popup menu
In Microsoft Excel it is possible to extend the right click popup menu by using VBA. The right click menu is implemented as a special CommandBar (like the toolbars in Excel 2003) with the name “Cell”. Customs buttons are added before the popup menu is displayed. It is up to yourself to make sure that […]
Excel VBA: Adding custom Button to the Toolbar or Ribbon
Custom buttons are added to the toolbars or ribbons when a Microsoft Excel sheet is opened, and removed when the sheet is closed. To be notified when a given Excel sheet is loaded we need to listen to the Workbook_Open event of the ThisWorkbook object found in the VBA Project explorer. Please notice that the following code works best with […]
Visual Studio 2019: Debug the .NET assemblies
The .NET framework is under heavy development and bugs are removed by Microsoft developers when found. However, you may still experience major bugs that might cause your application to crash. Sometimes it is even hard to determine whether it is a bug in your own code or in the framework. This issue has been taken […]