Erlandsen Data ConsultingCalculating the difference between two dates is rather easy, just subtract one date from the other to return the count of days between the two dates. If you add a number to a date, you will get a new date that is the number of days into the future from the initial date. You can [...]
If you need to delay something or want to get the elapsed time and don’t need an accuracy better than 1 second, you can use the information in the example macro below: Sub TimerExample1() ‘ accuracy in seconds Dim s As Double, e As Double, i As Long Dim dblElapsedTime As Double s = Now [...]
With the functions below you can align a string (left, center or right) within a fixed width, this might be useful when you e.g. create reports with fix-width column formats. Function AlignLeft(varItem As Variant, intWidth As Integer) As String Dim strResult As String, i As Integer strResult = CStr(varItem) i = intWidth – Len(strResult) If [...]
If you need to get the result from a formula in a cell, you would normally do something like this: dblValue = Range(“A1″).Value If you need to calculate parts of a formula or math expression then you can do something like this: ‘ assume that cell A1 contains this formula: ‘ =(A2+A3)/(A4+A5) strFormula = Range(“A1″).Formula [...]
With the custom functions below you can calculate the count of workdays between two dates. Workdays includes normal weekdays except Saturdays and Sundays or holidays e.g. like Easter). The functions can be used like this in a worksheet cell: =CountWorkDays(A1,B1) =AddWorkDays(A1,15) =DateIsHoliday(A1) The date input cells must contain valid Excel dates, or formulas/functions that return [...]
The macro below will return a collection with the unique items from a cell range. The collection can optionally be populated with separate keys and values. Function GetUniqueItems(KeyRange As Range, Optional ItemRange As Range) As Collection Dim r As Long, c As Long, varItem As Variant, strKey As String If Not KeyRange Is Nothing Then [...]
Display any number as text, 9999 = ninethousand ninehundredandninetynine. English and Norwegian text, other languages needs some editing. Source code for Indonesian language (Bahasa Indonesia) is also included, translated by Billy S. C. Ticoalu. Click here to download this file. Updated: 2006-05-07 & 2012-04-11 Requires: XL5 File size: 40 kB
With the macros below you are able to open and close the default CD/DVD tray. Declare Sub mciSendStringA Lib “winmm.dll” (ByVal lpstrCommand As String, _ ByVal lpstrReturnString As Any, ByVal uReturnLength As Long, _ ByVal hwndCallback As Long) Sub OpenDefaultDiscTray() mciSendStringA “Set CDAudio Door Open”, 0&, 0, 0 End Sub Sub CloseDefaultDiscTray() mciSendStringA “Set CDAudio [...]
This function splits a character separated string into its indivual parts and returns any given part. The function can also be used as a worksheet function. The function will only work in Excel 2000 or later. Function GetStringPart(strInput As String, strDelimiter As String, _ intPart As Integer) As String Dim varStrings As Variant varStrings = [...]
The function below can be used to let the user pick or select a custom color. It uses the Excel applications own built in dialog for selecting colors. Unfortunately this built in dialog does not return a value for the color selected by the user. If the user selects a color in the dialog, it [...]