Blogs

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.

 

Creating a low cost GIS using Excel and Depiction

Geographic Information Systems (GIS) can be powerful tools for making sense of information, but building a full system has, in the past, been an expensive and technically demanding process, reserved for large corporations or those with significant technical training. However, new tools--and old ones--are beginning to change that.

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

If Formula

I am working daily on a worksheet with names and other personal information for clients such as identity numbers, physical as well as postal address and telephone numbers. Would you kindly help with formulas which I can use so that when I enter the client's reference number all the other details are automatically filled to another blank document

Directory

Hi please help me.

Contact Directory
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.

 

Nick's picture

Feb-2010 ExcelExperts.com Update

January had a slow start as everyone was on holiday, but the new year picked up quickly, and everyone came out wanting new spreadsheet systems.

Most encouraging this month has been the huge increase in total visitors up almost 40% on last month. Can we sustain this increase ?

Well... if we continue to offer good content, don't spam people, and bring Excel and VBA jobs to people who need them, of course we can !

Going forward, we are particularly interested in large complex projects. If you know of any, please refer ExcelExperts.com.

Nick's picture

98. Excel Tips - Sort Columns

Excel Tips - Sort Columns (Excel 2007)



We all know how to sort rows, but did you also know that you can sort columns ?

Here's a screen shot of our data in Excel:

sort-columns

And here's what happens when we sort columns:

Nick's picture

97. Excel Tips - Camera Tool



Excel Tips - Camera Tool (Excel 2007)

  • This is an excellent time saving tip to get screen shots from Excel into outlook
  • Use Camera Tool

I have the Camera Tool added to my custom toolbar. See this tip for more info on how to customise your toolbar.

Once you have the camera tool on your toolbar:

Nick's picture

What can't be done in Excel ?



What can't be done in Excel ?

When I ask customers why they're not using Excel for more things, they often say to me: "Well, we'd like to do XXXX, but you can't do that in Excel can you ?"

Often, the answer is: "YES YOU CAN !"

So my question to all of you, is this:  What do you think can't be done in Excel ?

Nick

Nick's picture

96. Excel Tips - Keyboard Shortcut To Open Excel

Keyboard Shortcut To Open Excel


There's no actual pre-defined Keyboard Shortcut To Open Excel, but you can set your own one up.

Here's how:

Go:  Start => All Programs => Microsoft Office

Then Right click on the Excel entry, and select Properties

VBA Tips: Easy way to navigating with Range Object

One of the most common use object is the Range object.

Normally, to refer a cell, we simply put the cell address into the range such as Range("A1"). Now, what is the simpliest way to refer to other cell like B1?

There are many ways to use the range object to refer to the cell from your starting cell. I find the simpliest way is the following: 

For example, if I want to refer to B1, I can simply write it as such:

Range("A1")(1,2).value or range("A1")(,2).value

This will return cell B1 value.

Nick's picture

Jan-2010 NEW Excel VBA Jobs section



Excel VBA Jobs section

 

ExcelExperts.com is pleased to announce a new section for jobs.

Recruiters looking for Excel / VBA experts will be posting jobs here.

Excel / VBA Recruiters:

Nick's picture

95. Excel Tips - Cause Of Big Excel Files



Cause Of Big Excel Files

 

We've all been there happily developing our spreadsheet, and all of a sudden, the file size balloons.

Q: How did that happen ??!!

A: Most likely, it's uneven formatting that's causing the problem

 

Vishesh's picture

Advanced Filter

With advanced filter in Excel using menu while trying to find out Unique, the limitation is that the destination range should be on the same sheet as the source. However, using VBA there is no such limitation. Using VBA the source and destination ranges in Advanced filter need not be on the same sheet.

Vishesh's picture

Excel VBA Tip - Bypassing Clipboard while copying values or formula in Excel

In VBA, to Copy Values from range A1:A5 to B1 you will generally write
Sheet1.Range("A1:A5").Copy
 
Sheet1.Range("B1").PasteSpecial xlPasteValues
Another method: write the following function in the code window
Sub CopyValues(rngSource As Range, rngTarget As Range)
 
    rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
 
End Sub
and call the above function using the following line of code wherever you need to paste values
Call CopyValues(Sheet1.Range("A1:A5"), Sheet1.Range("B1"))
The so
Nick's picture

32. VBA Tips - Turn Off Autofilter

Turn Off Autofilter

Turning off autofilter using VBA is easy and quick, but you need to know how it's done.

Here's some code to toggle the autofilter on and off:

Syndicate content