Vishesh's blog

Vishesh's picture

Color Chart Series Utility

 Download the attached Excel file to see the code behind it and how this utility works. Its makes colouring of chart series easier in a very user-friendly way.

Colour Chart Series

Vishesh's picture

Custom Number Formats

Each cell in Excel has a format which is divided into four Sections

from left to right:

  • Positive numbers
  • Negative Numbers
  • Zero Values
  • Text values

... each separated by a semi colon (;).

Vishesh's picture

Chart Zoom(er)

Chart-Zoomer

 Download the attached Excel file to see how the Chart Zoomer works. Click on the chart to Zoom and click again to size it back to normal.

Vishesh's picture

Show Blank Chart or 'No Data' on Empty chart

'Copy the following code in a general module and call TestRun function. Alternatively you can download the attachment and test run it.

Vishesh's picture

Difference of two Ranges

This function gives the difference of two ranges (output is range). It is opposite of union of ranges. Its NOT intersection either.
Copy the following code in a general module and call the TestRun function with required ranges to see how it works.

'--------------------------

 

Vishesh's picture

Find All Occurrences of string in range

 'The following code finds all occurrences of a string in a given range. Just call the following function with the required parameters - string to find and range to find in. It returns a range.


 

Vishesh's picture

Lotus Email

Finally its here...Copy the following code in a general module and call SendEmail function. It returns 0 if successfull else error number is returned.
Public Function SendEmail(strMailTo As String, strSubject As String, strBodyText _
    As String, blnReceipt As Boolean, Optional strAttachment As String, Optional  _
    strCCTo As String, Optional strBCCTo As String) As Long
 
    Dim objApp                  As Object
    Dim objMail                 As Object
    Dim objLotusDB              As Object
    Dim objLotusItem            As Object
    Dim arrAttachment()         A
Vishesh's picture

Compact Access Database

'Copy the following code in a general module and call it with required parameters.
'This function returns 0 is successful else error number is returned

Option Explicit
 
Function CompactDB_JRO(strDBPath As String, Optional strDBPass As String = "") As Long
On Error GoTo ErrFailed
    'Delete the existing temp database
    If Len(Dir$(strDBPath & ".tmp")) Then
        VBA.Kill strDBPath & ".tmp"
    End If
 
    With CreateObject("JRO.JetEngine")
        If strDBPass = "" Then 'DB without password
            .CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
Vishesh's picture

Align Shapes using VBA

'Following is the code to align 2 shapes.
Vishesh's picture

Export Excel Range to Access

'Following is a very simple piece of code to Export an Excel range to Access.
'Run the procedure ExportToAccess.
Vishesh's picture

Outlook Email

Copy the following code in a general module and call SendEmail function. It returns 0 if successfull else error number is returned.

Vishesh's picture

Read from Access Table into Excel

Following is a very simple piece of code to import an Access Table into Excel.

- Run the procedure ReadFromAccess.

- Copy the complete code given below:

Vishesh's picture

Changing Data in Image (Its Different). No VBA coding

This can be a unique way of displaying dynamic data in an image without actually playing with the image. Only change the data and the image would reflect the changes including the formatting changes in the cells. Download the attachment and see the steps required.

Dynamic Image

Vishesh's picture

Word Count in Cell using Split

This, I think, is the  simplest of code to find the word count in a cell using the Split function. Its a single line code but very useful.

Download the attachment file to see how this is used as user defined function in excel and as a formula.

 

Function intWordCount(rng As Range) As Integer
 
    intWordCount = UBound(Split(rng.Value, " "), 1) + 1
 
End Function
 

 

Word Count using Split

Vishesh's picture

Array To ADO Recordset

This is helpful in handling excel data in the same way as access data.
Vishesh's picture

Filter Workaround (Simulating Filter)

 Sort the data and try this tip after downloading the attachment. Follow the instructions in the attachment file

Filter Workaround

Vishesh's picture

Classes (Single event for multiple labels)

 Here I have created a simple Date Calendar for which only one click event is defined in a class for all the label for dates (1...31)

Download the attachment file and see it for yourself. Modify the code as per your requirement if needed.

Classes

Vishesh's picture

Chart Event (Double Click on Chart)

The example file attached shows how to create events for charts. Here, I have created double click event for charts. The chart which is double-clicked is exported to PowerPoint. Download the example file and have a look for yourself. See the code in Thisworkbook and mod_PPT module. 

 

Chart Event
Vishesh's picture

Types of Message Boxes (Simple ones)

 Please see attached xl sheet to get an understanding of some simple message boxes. Feel free to let me know if you want something on other types of message boxes as well...will try and upload that...

Here is the code that's there in the sheet as well.

Vishesh's picture

Sorting ListBox Items alphabetically

 This is the simple code to sort ListBox items alphabetically. Alternatively, you can download the file and test run it for yourself. Just pass the listbox object as function parameter.

 

Vishesh's picture

Export Excel Charts to PowerPoint

 Here is the code to export all charts in a sheet to powerpoint. Just pass the worksheet object as function parameter and it will export all charts in it onto Powerpoint. Alternatively, you can also download the attached file and see how the code works.

 

Vishesh's picture

Add Custom Options to Right Click Menu

 

Often when using Excel, you want the ability to call a macro, but don't want to display a button. Adding Custom Options to Right Click Menu gives you this functionality. Create the data as in the first sheet of the attached xl file and copy the following code in Thisworkbook module. Right click to see that your menus appear in the right click menu list. There is also an option to specify whether to show 'Begin Group' separator line or not.

 

Vishesh's picture

Creation Date of Excel File

There are two ways to get the creation date of a file...one using the filesystem object for any file (opened or not) and other is through workbook's buitin properties. Paste the following in a general module of a workbook test run it by running the ExecuteFunc procedure in the same module.

Vishesh's picture

File Manipulation from VBA

  Following are some of the functions that you can frequently use to copy, move and rename your file from VBA. You can either copy the following code directly in a module or alternatively download the attached file. For renaming a file there are two methods given for this...Try this out!

 

Vishesh's picture

Using Instr with Optional Backward Search

Below code can be used to find the starting position of a string in a text from start or can be used to search backward as well.

You can also download the attachment.

Option Explicit
 
Function lngFindString(strFullString As String, strSearchString As String,  _
   blnCaseSensitive As Boolean, blnReverseSearch As Boolean) As Long
 
    If blnCaseSensitive Then
 
        If Not blnReverseSearch Then
 
            lngFindString = InStr(1, strFullString, strSearchString)
 
        Else
 
            lngFindString = InStrRev(strFullString, strSearchString, , vbBina
Vishesh's picture

Start Position of a Nth Instance of String in a piece of Text

Code below returns the Starting position of Nth Instance of a string in a piece of text. Just paste the below code in the code module and run the 'TestIt' procedure. Alternatively download the attached file and run the same procedure from there from the module 'mod_NthInstance'.

 

Option Compare Text

Option Explicit

 

Function lngStartPosition(strSearchIn As String, strSearchString As String, lngInstance As Long)

    'This function will return 0 if Search String is not found

Vishesh's picture

Customised OnKey with example

Download the attached example file. Goto the module 'mod_OnKey' and run the procedure named 'Testit'.

Alternatively, paste the following code in any module and call it from anywhere in the workbook to set application onkey.

 Sub setAppOnkey(blnShiftKey As Boolean, blnCtrlKey As Boolean, blnAltKey As Boolean, strKey As String, strCallFunction As String, Optional blnSetNormal As Boolean)

    Dim strShift        As String

    Dim strCtrl         As String

Vishesh's picture

Check if a worksheet already exists in a workbook

Before adding a new sheet if you want to check if a sheet with the same name already exists then use the following function procedure.
Vishesh's picture

Extract Nth Word from a String - VBA

Here is a VBA function to Extract the Nth Word from a String:
Function GetNthWord(strStringFrom As String, strSplitCharacter As String, intExctractWordNumber As Integer) As String
    On Error Resume Next
    GetNthWord = VBA.Split(strStringFrom, strSplitCharacter)(intExctractWordNumber - 1)
    If Err.Number <> 0 Then
        GetNthWord = ""
    End If
    On Error GoTo 0
End Function
 
Sub ExecuteNthWord()
    MsgBox GetNthWord("Hi:Hello:Nick", ":", 2)
End Sub
Vishesh's picture

Excel VBA Data Validation Code

 

This is a generalised code for Excel VBA Data Validation. It can be used across sheets in a workbook.

 

Syndicate content