Bullseye Chart


Bullseye chart can be used to visualise the parameter in a particular range. Microsoft excel doesnt have this as default Chart type but it provides way to create one by combining couple charts like X,Y and Doughnut. here is one we have build for you.  You can download this chart. Please share feedback in the…

Link Chart 3


Download the file from here see Link Chart 2 see Link Chart 1 Thanks Rajan

Link Chart 2


The last chart i posted here was showing item link to a category and vice versa , This one shows each items links with other items.   download the file from here unlock the file with “j33hqiwy8@12345”

Link Chart


here is another graph i have created using XY to show the items under a category or category belongs to multiple items. see the below table that you can fill 1 in, and chart will update automatically. Here you can select Category and Items in this table to the left and graphs will highlight the…

Tree Chart


Hi Guys, This chart shows the hierarchy, anyone can use this to build organization chart, family tree etc. i have tried to make it simple, user just need to fill the data in first two columns. and the graph will update automatically. feedback and suggestions are welcome. Download the file from here

Sort data by excel formula


  Excel provides SMALL and LARGE formula to get sorted numeric values , but sometime we need our string/text data to be sorted by formula only. Though, there is an inbuilt sorting feature available in excel, but here I am going to demonstrate a formula. So, Lets say you have your data list in Range…

How to delete worksheets without Loop.


After a long time I came up with an interesting trick, it might be rarely useful for anyone but it is tricky. And the trick is “How to delete all worksheets except one” without any loops by VBA. I always love to use array everywhere (I mean where possibleJ), so here is the trick Worksheets()…

Send E-mail using Other account by outlook.


Sometime there are more than one account are configured in outlook and when we use VBA to send Emails, it use the default account. but in case you have to send emails through other account , you can use this code. the bold lines are telling outlook to use another account to send emails. Sub…

Convert Specific formula to value :


Many times i have seen this query on Excel discussion groups and forums to convert values for a specific formulas on worksheets. so i have decided to post a something about that. it might be little helpful for Excel users. here is a code which will convert formulas to values on every sheets, this code…

Calling UDFs by Hyperlink


So you are creating a dashboard and you are in a deep thought , thinking what should I add to make it more interactive You have added scroll bar, dropdowns to refresh your charts and all what you can do with the excel, but may time you feel like there should be a mouse hover…

Indexer


hi Guys, it took my lot of time to create home/Index tab to navigate in excel workbook, i always need it in most of dashboards ,dont you? You can set any shape style default by right click on the shapes, it will work accordingly.   here is the code to create index :  Sub CreateIndex()…

Change Hyperlink Screentip


It may take lot of time to change screen tip of each hyperlink in Excel, here is a code can do in few seconds here you go : Sub ChangeHyperLinkSceenTipOnAllShape()     ‘—————————————–     ‘–This Procedure will change screenTip     ‘  of Cells and Shapes in ActiveSheet     ‘—————————————–     Dim…

Array Class


Although VBA is a great language to manipulate office objects accordingly, but when it came to array handling  there are lack of method to handle array, To resolve some of problems i collect and create some method and properties, see the content listed below which you can find in the class module . Functions available…

Hierarchy Chart :


Hi All, I have customize stack bar chart to a hierarchy chart by using some VBA, This chart displays the next level data of a selected , user can select a parameter by double click on a series, Example : you can download  .xlsm from here Note : You can use it with excel 2010….

Encrypt and Decrypt Text


Here is a basic Function to Encrypt and Decrypt  any text Public Function evtEncrypt_Decrypt(strText As String, strPWD As String) As String     Dim byteText() As Byte     Dim bytePWD() As Byte     Dim intPWDPos As Integer     Dim intPWDLen As Integer     Dim intLoop As Integer     byteText = strText…

Comment Formatting


This code can be use to Format a Comment , you can make your excel cell comment bit beautiful by using below  code   Sub FormatComment()       Dim rngRange As Range     Dim rngCell As Range     Dim objCom As Comment          Set rngRange= Range(“rngRange”)     For…

Get Random List :


Here is a code to get random list in array, you can pass top and bottom value. Function GetRandList(ListCount As Long, Bottom As Long, Top As Long) As Variant     Dim lngCOunt As Long     If Bottom < Top Then         With CreateObject(“Scripting.Dictionary”)             Do While…

Find Mismatch Characters:


 Here is a code to highlight the misspelled characters in two Text paragraphs suppose you have data like this table, and you want to highlight the misspelled characters , you can use given code  Sub HighlightWrongCharacters()     Dim bytFirstWord()  As Byte     Dim bytSecondWord() As Byte     Dim VarArrData     Dim…

Exclude Contents From List :


Hi, What would be your approach to do this job 🙂  Suppose you have 2 List As showing below , and you need to remove List2 contents from List 1 , here is better code to accomplish this job , By using dictionary i just tried to make this process fast 🙂 Sub ExcludeFromList()  …

Download All Attachment from outlook Mails :


You can use this code to Download all Attachments in a specific folder from outlook mail. Sub SaveAttachment(StrPath As String)       Dim outLookApp       As Object     Dim ObjMitem         As Object     Dim NameSpace        As Object     Dim ObjFolder    …

Looping through Each Mail Item in Outlook Folder :


  Here is a Code to make loop on mail item in a particular folder of Outlook. you can use this code to make summary of received Mail Like  X person send Y Number of Mails.     Sub GetSummaryFromOutLook()       Dim outLookApp       As Object     Dim ObjMitem  …

Showing Values on Angle:


here is chart to show value on Angle Suppose you have data like below table : Angle Strength 45 40 90 60 135 50 180 90 225 45 270 55 300 65 315 60 360 80 Here is a trick to show This data on chart, Here is xlsx file Thanks Rajan

Join [Vba Method]


JOIN its a method to concatenation values in a string variable , you can use this trick to avoid a Loop for this purpose.         Sub MakeString()     Dim strSting As String     strSting = Join(Application.Transpose(Selection), “,”)     Debug.Print strSting End Sub

Looping Through Each Region on Sheet


  Somewhere i found this interesting question . How to loop through Each Regions on a worksheet   this subroutine can loop every regions on worksheets , i was also thinking for a better way (if possible please post here)   Sub AllCurrentRegions()       Dim rngUsedRange        As Range    …

Unique List From All worksheet.


Get Unique Element from all worksheet at new sheet Sub GetUnique()     Dim wksSheet As Worksheet     Dim VarArr     Dim lngCOunt As Long     With CreateObject(“Scripting.Dictionary”)     For Each wksSheet In ThisWorkbook.Worksheets         VarArr = wksSheet.Range(“A1”).CurrentRegion         For Each kk In VarArr  …

Bubble Chart :


Bubble chart represent Data in form of Bubble on X or Y Axis , every point of Bubble accept 3 argument X, Y and Size, you can refer below table to create a bubble chart. Name X Y Size A 70 40 1256 B 46 74 1433 C 67 94 1266 D 58 80 884…

Formula Challenge #2 :


Hi Guys. Enjoy the new formula challenge 🙂 suppose you have data in A and Column b like 1  A 2  B 3  C 4 you just need to create a Excel Formula to get output below : 1A 1B 1C 2A 2B 2C 3A 3B 3C 4A 4B 4C Enjoy Rajan.

Assign Event to a Bunch of Controls :


Hi Guys , Many times we have a situation when we have array of controls on which we need same event for every control So, this can be simplified by using class module where we will have only one event for all the controls. Example: Suppose, you have many Command bar Buttons on a user…

Hook Chart :


Showing Values on By Hook.  Download the .xlsx from here Regards Rajan

Create Summary Table From Data


This Function Gives you summary of Two Columns Data , see the example below : if you have Table like this : India City 3 India City 4 US City 4 China City 2 China City 3 UK City 2 US City 4 China City 2 Germany City 3 China City 4 India City 3…

Get All Files hyperlinks in Excel :


Do you want a tool which can give you all the links of files at one place? you are welcome here: You just need to select a Root Folder to get all files links in excel file . Download .xlsm from here. Thanks for reading Rajan

Quarter Date Format :


Hi Guys, Many time we need to show Dates in QTR. Format , here is a trick to accomplish that task Date Required Format 01-Jan-12 Q1-12 01-Apr-12 Q2-12 30-Sep-12 Q3-12 01-Oct-12 Q4-12 For this, i used Custom Format in  conditional formatting , you can see the below table for conditional formula and custom format Suppose…

Excel VBA Challenge


Hi Guys,   Challenge is to Delete all Sheets Except one without any loop and mapping table   Keep Excelling 

Get Unique Array From an Array


Function GetUnique(InputArray) As Variant     ‘==========================================     ‘Created on 13-Aug-2012     ‘Function Return an Array of Unique Values     ‘==========================================     Dim lngRow      As Long     Dim lngCol      As Long     Dim ResArray     With CreateObject(“Scripting.Dictionary”)         If IsArray2Dimensional Then…

Bulls Eye Chart :


Hi All, Just sharing the bulls Eye chart. Bulls Eye chart can be used to see weather parameter is in its desired range or not . I have created this chart by using Radar and PIE , hope you can make use of it .. Click Here to Download the file  Your Comment and suggestion are welcome!!…

HTML Body of a Table :


This Function return the HTML Script of any given array , Suppose you are trying to send a mail through outlook and want to paste entire table in Mail Body . there you can use this function. ObjMailItem.HTMLBody=HTMLBody(MyTableArray)   Function HTMLBody(InputArray) As String     ‘==========================================     ‘Created on 13-Aug     ‘Return the…

About IsMissing() :


IsMissing()   IsMissing is a VBA Function to Check , weather an optional arguments is supplied or not in the function.   “To use Ismissing  your optional argument must be Variant Type , if you argument is not variant type Ismissing will not work on that. Because Everything except variant have it defaut value Like…