Hi Folks, there are many available built-in spreadsheet functions in Excel. Ocasionaly one might need to write a special custom function. This second short tutorial is an introduction to that.
Creating a “Time Stamp” function is presented as a first example after which a concatenation function between a custom formatted date and a string (text) is introduced. This last function came about from a question I was asked by a finance person at one of my courses. A date is represented in general format as the number of days passed since 31 of December 1899.
You can format the cell to a normal date appearance, but if you concatenate the date with some arbitrary string you loose the date formatting. This user defined function overcomes this problem. More custom functions will be used in future models. I’d love to hear from you.
Cheers,
George
[sociallocker] 
[/sociallocker]
More User Defined Excel Functions (U.D.F. #2)
by George Lungu
Let’s create several more user defined functions:
A “Time Stamp” function:
Any user defined function must be written in a “Module” for it to work. The function
will record a fixed time (time stamp) when the formula is entered in the cell:
Function TimeStamp() As Date ‘function declaration – it has no argument and it returns a date
TimeStamp = TimeValue(Now) ‘ TimeValue(Now) is a construct which returns the time at the moment the macro or the function is being activated (typed in)
End Function ‘End of function declaration
– In a new worksheet called “TimeFunctions” type “= timestamp()” in cell B4.
– Format cell B4 as “time” (Format -> Cells -> Time)
– You can copy down B4 with few seconds delay and see how time stamping works
(don’t forget to pre-format those cells as “Time” before the copy operation).
<www.excelunusual> 1
Another Time Function, a Concatenation Date-Text:
While using concatenation of text with a date in a cell, the format of the date will be messed up. In order to preserve the
date format let’s write a new function.
Any user defined must function be written in a “Module” in order to work. The function will concatenate a custom formatted
date with a string of text:
Function DateText(DateNow as Date, Literal_Argument As String) As String ‘function declaration – it has two arguments, a date and a string and it returns a string.
DateText = Format(DateNow, “mm/dd/yyyy”) & ” ” & Literal_Argument ‘A construct which while used in declaration a macro or function formats an argument number
in a chosen format.
End Function ‘End of function declaration
The second argument (string)
Space
Typical VBA concatenation (you can search Google for more info)
<www.excelunusual> 2
Excel implementation of the time-text concatenation function:
– After writing the “DateText()” function code in “Module1” fill in cells “B20” and “B24:B28” with the labels shown in the picture to the right.
– Cell B21: “= Now()” -> this will print the date in the cell if B21 is formatted as “Date”
– Cell B24: “= datetext(B$21,A24 )”
– Cell B25: “= datetext(B$21,A25 )”
– Cell B26: “=datetext(B$21,”Something we write” )”
– Cell B27: “=datetext(B$21,A27 )”
– Cell B28: “=datetext(B$21,A28 )” – (I typed few spaces before “BP” in cell “A28” to demonstrate that
the spaces in the string are transmitted in the concatenated expression).
– There are other ways to format the data and several options are explained in the worksheet (see the yellow table).
by George Lungu <www.excelunusual>
