There is no straight forward function in Transact-SQL to create a simple date based on year, month and day. Below we have created a userdefined function to handle this. Notice that the base date in Microsoft SQL Server is January 1st 1900. We generate the date by adding the year, month and day to the base […]
MS SQL: Get the Date portion from a DateTime
To return only the date portion of a DateTime variable in Microsoft SQL server, you need to use a combination of the dateadd and datediff functions in Transact-SQL. Releated How to get the Date portion of a DateTime variable in Transact-SQL? How to create a Date in Transact-SQL?
Excel VBA: Send an E-mail from Excel
There are several ways to send e-mail from Excel using Microsoft Outlook. It is possible to use the built-in function ActiveWorkbook.SendMail. However, it only allows simple e-mails to be created, and there is no way to add attachments. In this example we are going to use the COM library of Microsoft Outlook to get more control. […]
Excel VBA: Get all the Selected rows
If the user selects multiple rows in Microsoft Excel, the selection can contain multiple areas. An area is a subselection of rows created when the user presses the Shift button while doing a multiselect. In VBA we need to handle each area independently to be able to retrieve all the selected rows. In the example we have […]
Excel VBA: How to Expand and Collapse a Group
In Microsoft Excel it is possible to group rows and columns. To expand or collapse a group you can use the VBA method called ShowLevels. In the example below we are expanding a row group. We have also disabled automatic recalculation to improve the performance. Please notice that expand and collapse work in the same way. Collapsing a […]
Excel VBA: Convert a Variant Array to a String Array
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 […]