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…

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…

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

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…