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


Refrences:
http://office.microsoft.com/en-us/excel-help/creating-international-number-formats-HA001034635.aspx

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 
    Do 
         '
         '           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 
    Loop 
     
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.

Refrences:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=743

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:

Application.ExecuteExcel4Macro("Index(Get.Workspace(37),(##Code##))")

'Example
'Application.ExecuteExcel4Macro("Index(Get.Workspace(37),(21))")
'Returns "d" represnting Day of date in GB, and "j" represnting Day of date in FR
'Array
'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



Refrences:
http://www.rondebruin.nl/international.htm

Saturday 27 November 2010

Skip Clipboard during VBA.. operation

Recently i came across an error in Excel 2007 as

"Picture is too large and will be truncated."

Upon looking into the issue, i found that leaving stuff on the clipboard, used during the copy and paste operations within VBA was the culprit. This error occurred when the file was about to be closed by the user.
                                After digging in to many portals and unsuccessful attempts to resolve the error by clearing the clipboard via VBA, I found one way to fix the code by redesigning my VBA codes to skip the use of copy and paste and stick with the range based value transfers. Which later i realised is more robust and better way of doing things.

The simplest of the solutions to completed the copy and paste task is

DestinationRange.Value = SourceRange.Value
'with the format code later.

And the solution for the elimination of formulas and paste as values could be gained by

SelectionRange.Formula = SelectionRange.Value

As the office clipboard is now provided in task panes section its quite unclear as of now which solutions can clear the clipboard in VBA.

And to add upon the article
Application.CutCopyMode = False 
doesnt work to clear the office clipboard.

Friday 5 November 2010

Let outlook do the thinking: Time based header for outlook

              Myself, being lazy tend to respect the people with greeting appropriate to the time of the day, like Good Morning, Good Afternoon & Good Evening.

   But keeping track of code, time, life and every thing else takes a toll and some times i try to automate these bits as much as possbile to simplify my life.
                  To cut short i desgined a macro which could populate the outlook text for new email with correct headers based on the time of day with the shortcut "Ctrl+J"

To accomplish this

1. Start you outlook..
2. Open a new email interface, press Alt+F11 on you key board to power up your VBA editor.

Navigate to location "Normal" as shown in the image below:



Insert a module named "CustomTextMacro" (any thing you like as it personal choice
In that module Type(if you like)/CopyPaste in the following peice of code:

Sub InsertCustomText_Mail()
Selection.HomeKey Unit:=wdStory
Selection.Font.Color = wdColorDarkBlue
If TimeValue(Time()) <= TimeValue("12:00:00") Then
Selection.TypeText Text:="Good Morning,"
ElseIf TimeValue(Time()) <= TimeValue("16:00:00") Then
Selection.TypeText Text:="Good Afternoon,"
ElseIf TimeValue(Time()) > TimeValue("16:00:00") Then
Selection.TypeText Text:="Good Evening,"
End If

Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeText Text:="Thank you"
Selection.TypeParagraph
Selection.TypeText Text:="Regards"
Selection.TypeParagraph
Selection.TypeText Text:="Amol Pandey"
Selection.TypeParagraph
Selection.TypeParagraph
End Sub

3. Then once completed, navigate to following location as shown in the image:



clicking on it will bring the following window:




then navigate to the Keyboard section of the window and click on it to bring on the following next window:




In this window we will find our macro listed and assigning the macro the required shortcut keys will accomplish the job and, there we have out complete automated solution for the custom outlook header.

Just press the magical shortcut, and there you go on your new email you have the custom header based correctly on the times of the day...

Hope it simplifies your lives as mine...

Cubed Formulas: Compute Data Throughout Workbook

           Recently i landed upon a brilliant techniques demonstrated by Better Solutions for illustrating the CUBED FORMULA, power of excel. This techniques thrust's excel to aggreagte same worksheet locations on diffrent sheets mentioned in the formula as workshet series.

 "Sheet1:Sheet4!A2:B5". This technique can be used in formula as   "=Sum(Sheet1:Sheet4!A2:B5)"

This technique would help aggreagate the certain ranges from each sheet specified adress and contained withing the worksheet series. Though being an advantage one has to be care ful to be aware that movement of order of sheets would alter the logic of formula performed and the intended result.

   For further information please check the refrence for more in detail description of the functionality.

Refrences:
Better Solutions : Link

Exploring Drop Down...Multicolumns

Many times the case where by one list of drop downs is not enough business request for multiple columns for the drop downs list for one chosen item.

Face with the issue heres my quick code for the same to overcome...


'Lets populate the and set the control parameters.
    obj.Clear 'Clear ne thing from the pre population of control if already done.
    obj.ColumnCount = 2 'Set the number of columns required to display.
    obj.ColumnWidths = "150;150" 'Set the size if columns
       
       For i = 1 To NoOfItems
                With obj
                    .AddItem "column1 item"
                    .List(custom_Counter, 1) = "column2 item"
                    custom_Counter = custom_Counter + 1
                End With
       Next i                         

'DropDown Change Event
      obj.List(obj.ListIndex , 0) 'Return you the selected 1st column value
      obj.List(obj.ListIndex , 1) 'Return you the selected 2nd column value


and there you go .. Thats out multi column drop down box....

Download Solution
Download solution

Wednesday 3 November 2010

RefEdit Alternative: Let Textbox Handle it..

One of the most annoyances of VBA was the utility of RefEdit. Though working fine based on its mood of its own, with the trouble of focus and other petty issues.

Recently i stumbled upon a code illustrating the TextBox swiss army knife capabilties of providing a brilliant alternative to RefEdit control. the magic lies in the following code as follows when insetered during the form intiliazation process.

Me.txtRefChtData.DropButtonStyle = fmDropButtonStyleReduce 
'fmDropButtonStylePlain
'fmDropButtonStyleEllipsis
'fmDropButtonStyleArrow
Me.txtRefChtData.ShowDropButtonWhen = fmShowDropButtonWhenAlways

and the event handler for the same goes as follows:

Private Sub txtRefChtData_DropButtonClick()
 'do Somthing.. 
End Sub

and it does the jobs with no compatibiltiy and focus issues..


Download Solution
Download solution

Lets Talk : Unix to VBA

I had always wondered if i could some how skip the PUTTY screen for my basic task like runing some custom scripts at my work or if possbile i could bring them to a better world of windows push button technology. And more i wanted it in excel using VBA.

The solution to my problem was partly to a major extent aswered by PLink. This tool allowed me to act as a brigde to communicate to unix via batch script file. And with Excel VBA i can easily create them on fly. Hence forth i landed with this peice of solution i have provided as intiation of the project which could be developed as desired. This tool is ammendable as required to be used with .NET solutions as well.

A good help for the PLINK tools is avilable at Link.

A quick guide for the command list for PLINK:

'Z:\sysosd>plink
'PuTTY Link: command-line connection utility
'Release 0.58
'Usage: plink [options] [user@]host [command]
'       ("host" can also be a PuTTY saved session name)
'Options:
'  -V        print version information and exit
'  -pgpfp    print PGP key fingerprints and exit
'  -v        show verbose messages
'  -load sessname  Load settings from saved session
'  -ssh -telnet -rlogin -raw
'            force use of a particular protocol
'  -P port   connect to specified port
'  -l user   connect with specified username
'  -batch    disable all interactive prompts
'The following options only apply to SSH connections:
'  -pw passw login with specified password
'  -D [listen-IP:]listen-port
'            Dynamic SOCKS-based port forwarding
'  -L [listen-IP:]listen-port:host:port
'            Forward local port to remote address
'  -R [listen-IP:]listen-port:host:port
'            Forward remote port to local address
'  -X -x     enable / disable X11 forwarding
'  -A -a     enable / disable agent forwarding
'  -t -T     enable / disable pty allocation
'  -1 -2     force use of particular protocol version
'  -4 -6     force use of IPv4 or IPv6
'  -C        enable compression
'  -i key    private key file for authentication
'  -m file   read remote command(s) from file
'  -s        remote command is an SSH subsystem (SSH-2 only)
'  -N        don't start a shell/command (SSH-2 only)

Download Solution
Download solution


Tool Screenshots:



Refrences:
PUTTY: http://www.chiark.greenend.org.uk/%7Esgtatham/putty/
PLINK: http://the.earth.li/%7Esgtatham/putty/latest/x86/plink.exe
PLINK Help: http://the.earth.li/~sgtatham/putty/0.58/htmldoc/Chapter7.html

Tuesday 14 September 2010

Art on excel...Charts..

Tired of numbers ..
Wish for some artistic side of excel...

Peek on to a new world of art on excel...
The sample files for the sketched art are located at the following link.
Download Solution
Excel Art Sample


The tool for sketching your creativity is located at the following link.
Download Solution
Excel Art Tool

This project bring the fines lines of chart to art in excel.. Have a quick peek and get creative.
This is brilliant example designed by anonymous excel power user in C++ where the tools maps the image points as the user sketechs the line and translated them over to excel as numbers stroed within a CSV file.

Some of the brilliant example screenshots are illustrated, including the control panel screen shot for the real time image sketching as follows:

Excel Power Tools: Turbo Charge Your Processing Power

Recently i have completed my first phase of an excel addin tool for the most general day to day purpose utility.
The commonly used functionality desired by analyst and business solutions is featured in this addin. This addin is mainly focused on improving the user efficiency, power and creativty.

Some of the key highlights of this tool are

1. Google Charts
2.Google Translation (via UDF)
3.Named Range Functionality and navigation
4. SQL/Access Intellisense
5. Generic quick sort implementation as UDF for tables maintining relative column.
6. Range/Chart export as image
7. Workbook Name Navigator
8. Calender
9. Automated Formula Replacer
10. Visual Audit Tool for Formula Extension
11. Range Concatenation

and tons of more features to get you excited and highly simplified and organised design for the users to load on the future functional implemntations...

Link to Addin:

Download Solution
Download solution


I look forward to your comment and future suggestions for the added functionalities.

Addin Screenshots:

Intialize your Array... (No Loops)

Many Many Codes so far use loops to process, loops to intialize and loops to output... No Harm except the wasted time of user and effort of pc..

In order to overcome this issue i have came across one of the great solutions which had always thought of to practise in VBA is to intialize an array using a single line decalaration and with out loops to a defualt value may be even a string...

The following solutions of mine following illustrates my point.

Sub Demo()
Dim M() As Variant
'This will intialize your array with "-"
M = [if(isnumber(ROW(A1:J10)),"-",99)]
'This will intialize your array with integer value 5
M = [5*TRANSPOSE(SIGN(ROW(A1:J10))*SIGN(COLUMN(A1:J10)))]
End Sub

Check this excitng code out and gain the advantege of being a smart programmer ....

Sunday 9 May 2010

A Dream Come True: Multi Threading in Excel

As the title suggest its a true "Dream Come True" for all the excel application developers of now having a way to multithread process in excel. Though excel being a native STA application, there is workaround using the long forgotten VB Script to multi thread many of excel VBA task. Recently developed technique of extracting the web data using multi threaded vbscript is a clver way illustrated in the blog:


 This is great example for all the developers to harness the full power of the computers runing massive excel VBA applications. Go ahead explore the solution and explore the further possibilities of excel .....

Wednesday 28 April 2010

Switching Default Printer in Desktop & Citrix Environment using VBA

Here is code snippet which quite challenged me for a while and found a solution lately for the defualt switch of printer within the desktop and citrix environment and would like to share with all of you.

'*Note - this code work semalessly with the Citrix and
'the native desktop environement.
'**********************************************
'Declaration Required:
'this is required for the following functions to operate 
'normally, and it should be declared at the top
'of the module above all the declared functions/subs in the 'module.


Private Declare Function GetProfileString Lib "kernel32.dll" Alias "GetProfileStringA" _
(ByVal lpAppName As String, _
ByVal lpKeyName As String, _
ByVal lpDefault As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long) As Long



'SetPDFPrinter - This function returns the active defualt
'printer name as string and sets the required printer 
'specified in the code.
'Return the active defualt printer

Private Function SetPDFPrinter() As String
    'Save the old defualt printer and return as string
    Const BUFFSIZE As Long = 254
    Dim strBuffer As String * BUFFSIZE
    Dim lngRetVal As Long
    lngRetVal = GetProfileString("windows", "device", ",,,",    strBuffer, BUFFSIZE)
    SetPDFPrinter = Left(strBuffer, (InStr(strBuffer, ",") - 1))
    'Reset the printer if its not already selected 
    'to \\Prism\Apple Driver
    If SetPDFPrinter <> "\\Prism\Apple Color LW 12/660 PS" Then
        Dim objNetwork As Object
        Set objNetwork = CreateObject("Wscript.Network")
        objNetwork.SetDefaultPrinter "\\Prism\Apple Color LW 12/660 PS"
        Set objNetwork = Nothing
    End If
End Function


'ReSetOrgPrinter - This function is used to rest the active
'default printer to the named sent in the passed string to
'the function

Private Function ReSetOrgPrinter(printDrv As String)
'Retrive the current print driver string name 
'for comparision.
    Const BUFFSIZE As Long = 254
    Dim strBuffer As String * BUFFSIZE
    Dim lngRetVal As Long
    Dim actPrinter As String
    lngRetVal = GetProfileString("windows", "device", ",,,", strBuffer, BUFFSIZE)
    actPrinter = Left(strBuffer, (InStr(strBuffer, ",") - 1))
    If actPrinter <> printDrv Then
        Dim objNetwork As Object
        Set objNetwork = CreateObject("Wscript.Network")
        On Error Resume Next
        objNetwork.SetDefaultPrinter printDrv
        If Err.Number <> 0 Then
            MsgBox Err.Description & " :: " & Err.Number & vbCr & "The active/defualt printer would be: " & actPrinter, vbCritical
            On Error GoTo 0
            Err.Clear
        End If
        Set objNetwork = Nothing
    End If
End Function



'Usage in the actual procdeure.
Dim actDefualtPrinter As String  

'declare the string to hold  on the original default printer
actDefualtPrinter = SetPDFPrinter  

'Set the pdf printer to \\prism\apple 
'printer as default saving
'the old settings

'Reset back the defualt printer to the original settings.
ReSetOrgPrinter (actDefualtPrinter)





These procedures are primarily useful to switch the defualt printer seamlessly in native windows/citrix environment.

Welcome to Unlimited Excel

A warm welcome to my blog unlimited excel. With this blog i am seeking to spread out the possbile ways not thinked or stuck before with excel and present the unlimited possibilties with excel. I will be exploring and sharing the unique ways of making things happen which challenged me in my work.


            I would also like to hear from you if you would have any wiered ideas in you mind to spread the power  knowledge of excel as tool and share the ideas.


          Thanks and hope you enjoy the solutions provided on this blog.....