Wednesday, 15 December 2010

Date Formats: Internationale MonthNames

Many times our solutions involves producing the report or functionality into multi regional formats, though translations driven from Databases/Worksheets, it is not ideal to go on translating the month names too in the data driven solutions for multi lingual formats.

One of the great pieces of functionality I came across recently working upon an solution for my workplace was regional date formatting solutions where by we can control the regional date formatting for dates within the worksheets. This is a great solution for the multi lingual date formatting.

And If I didn’t make myself clear here’s the screen shot for the same where picture speaks a million word..

Download Solution
Download solution


Sunday, 12 December 2010

Cell Text Formatted, via VBA

Recently, I was presented with a challenge to produce a report consisting of mixed section of dynamic tables, charts and text with varying font sizes to emphasise figures within the text. I couldn’t implement the varying font size text via the use of this technique just by using the static cell formatting, as the content kept change position within the text depending on the user input.

Just to highlight more clearly the challenge I faced was to produce the text as follows with highlighted numbers in bold changing their position as inputted by user, sample of text as illustrated below:

The company was added as a component to the S&P 500 index Thursday, and it gained 1.8% Friday, trading at $194.58.

This challenge, I overcome by using a great functionality presented by VBAExpress to format cell text under VBA control, where I was able to locate the required text and format as required.
The section code I utilized in my work is as follows with the comments illustrating the usage:

Option Explicit 
Sub xlCellTextMgmt( _ 
    TargetCell As Range, _ 
    TargetWord As String, _ 
    Optional FirstOnly As Boolean = True, _ 
    Optional FontName As String, _ 
    Optional FontBold As Boolean, _ 
    Optional FontSize As Variant, _ 
    Optional FontColor As Variant) 
     '       Title       xlCellTextMgmt
     '       Target Application:  MS Excel
     '       Function:   reformats selected text within the target cell
     '       Limitations:  no explicit checks for acceptable values, e.g., does not
     '                       check to ensure that FontName is a currently supported
     '                       font
     '       Passed Values
     '           TargetCell [input,range]   the target cell containing the text to
     '                                       be reformatted
     '           TargetWord [input,string]  the words in the target cell text that are
     '                                       to be reformatted.  TargetWord can contain
     '                                       anything from a single character to several
     '                                       words or even the entire text of the target
     '                                       cell
     '           FirstOnly  [input,boolean] a TRUE/FALSE flag indicating if the
     '                                       reformatting is to be done on ONLY the 1st
     '                                       instance of the target word (True) or on ALL
     '                                       instances (False)   {Default = True}
     '           FontName   [input,string]  the name of the new font.  Omit if the font
     '                                       is to be left unchanged
     '           FontBold   [input,boolean] a TRUE/FALSE flag indicating if the target
     '                                       words should be BOLD.  True ==> Bold.  Omit
     '                                       if the text is to be left unchanged.
     '           FontSize   [input,variant] the size of the new font.  Omit if the size
     '                                       is to be left unchanged.
     '           FontColor  [input,variant] the color of the new font.  Can be one of
     '                                       the standard colors from the Excel palette or
     '                                       can be one of the standard "vbColors".
     '                                       Omit if the color is to be left unchanged.
    Dim Start As Long 
    Start = 0 
         '           find the start of TargetWord in TargetCell.Text
         '               if TargetWord not found, exit
        Start = InStr(Start + 1, TargetCell.Text, TargetWord) 
        If Start < 1 Then Exit Sub 
         '           test for each font arguement, if present, apply appropriately
        With TargetCell.Characters(Start, Len(TargetWord)).Font 
            If IsNull(FontName) = False Then .Name = FontName 
            If IsNull(FontBold) = False Then .Bold = FontBold 
            If IsNull(FontSize) = False Then .Size = FontSize 
            If IsNull(FontColor) = False Then .ColorIndex = FontColor 
        End With 
         '           if request was for ONLY the first instance of TargetWord, exit
         '               otherwise, loop back and see if there are more instances
        If FirstOnly = True Then Exit Sub 
End Sub 

This code i used within a worksheet activate event where a user input could be plugged in on a diffrent worksheet and could be reflected instanly upon a change on the final report sheet.


Date Time Format: Internationale

Many times in globalized world of today, our creation of spread sheets attracts viewers from different parts of the world with “not so good” friendly regional settings on their computers. Due to this our work doesn’t show up as required, and many times the entire process fails.
In order to overcome this issue, I recently came across brilliant solutions to make our workbook globally adaptable to take on any cultural settings we put them under.

And the magic lies in the following code below, which returns the PC region specific identifier used for the item of interest it represents:


'Returns "d" represnting Day of date in GB, and "j" represnting Day of date in FR
'index Results Information
'1      1   Number corresponding to the country version of Microsoft Excel.
'2      44  Number corresponding to the current country setting in the 
'           Microsoft Windows Control Panel or the country number as determined
'           by your Apple system software
'3      .   Decimal Separator
'4      ,   Zero (or 1000) separator
'5      ,   List separator
'6      R   Row character
'7      C   Column character
'8      r   Lowercase row character
'9      c   Lowercase column character
'10     [   Character used instead of the left bracket ([)
'11     ]   Character used instead of the right bracket (])
'12     {   Character used instead of the left bracket ({)
'13     }   Character used instead of the right bracket (})
'14     ,   Column separator
'15     ;   Row separator
'16     @   Alternate array item separator to use if the current array separator is the same as the decimal separator
'17     /   Date separator
'18     :   Time Separator
'19     y   Year symbol
'20     m   Month symbol
'21     d   Day symbol
'22     h   Hour symbol
'23     m   Minute symbol
'24     s   Second symbol
'25     £   Currency symbol
'26     General General symbol
'27     2   Number of decimal digits to use in currency formats
'28     1   Number indicating the current format for negative currencies
'29     2   Number of decimal digits to use in noncurrency number formats
'30     3   Number of characters to use in month names
'31     3   Number of characters to use in weekday names
'32     1   Number indicating the date order
'33     FALSE   TRUE if using 24-hour time; FALSE if using 12-hour time.
'34     FALSE   TRUE if not displaying functions in English; otherwise, returns FALSE.
'35     TRUE    TRUE if using the metric system; FALSE if using the English measurement system.
'36     FALSE   TRUE if a space is added before the currency symbol; otherwise, returns FALSE.
'37     TRUE    TRUE if currency symbol precedes currency values; FALSE if it follows currency values.
'38     TRUE    TRUE if using minus sign for negative numbers; FALSE if using parentheses.
'39     TRUE    TRUE if trailing zeros are displayed for zero currency values; otherwise, returns
'40     TRUE    TRUE if leading zeros are displayed for zero currency values; otherwise, returns
'41     TRUE    TRUE if leading zero is displayed in months (when  months are displayed as numbers); otherwise, returns FALSE.
'42     TRUE    TRUE if leading zero is shown in days (when days are displayed as numbers); otherwise, returns FALSE.
'43     TRUE    TRUE if using four-digit years; FALSE if using two-digit years.
'44     FALSE   TRUE if date order is month-day-year when displaying dates in long form; FALSE if date order is day-month-year.
'45     TRUE    TRUE if leading zero is shown in the time; otherwise, returns FALSE.
Using this technique, with the combination of unique named ranges and “REPT” workbook function we can create the formats we would require across the workbook and make the format functions like “TEXT” work seamlessly across different regional settings based PC’s.
Below are some of the examples illustrating the use of the technique to identify your PC settings information:

Download Solution
Download solution: Workspace Information

Download Solution
Download solution: International Example