Blogs
Array To ADO Recordset
Submitted by Vishesh on 17 September, 2010 - 17:54- Vishesh's blog
- Login or register to post comments
- Read more
- 36633 reads
Filter Workaround (Simulating Filter)
Submitted by Vishesh on 13 September, 2010 - 09:39Sort the data and try this tip after downloading the attachment. Follow the instructions in the attachment file
- Vishesh's blog
- 6 comments
- 7362 reads
Classes (Single event for multiple labels)
Submitted by Vishesh on 13 September, 2010 - 09:36Here 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.
- Vishesh's blog
- Login or register to post comments
- 6822 reads
Chart Event (Double Click on Chart)
Submitted by Vishesh on 29 August, 2010 - 17:17The 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.
- Vishesh's blog
- 5 comments
- 25819 reads
Types of Message Boxes (Simple ones)
Submitted by Vishesh on 29 August, 2010 - 16:48Please 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 blog
- Login or register to post comments
- Read more
- 26287 reads
Sorting ListBox Items alphabetically
Submitted by Vishesh on 28 August, 2010 - 19:56This 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 blog
- Login or register to post comments
- Read more
- 48367 reads
Export Excel Charts to PowerPoint
Submitted by Vishesh on 28 August, 2010 - 19:51Here 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 blog
- 10 comments
- Read more
- 66700 reads
Creating a low cost GIS using Excel and Depiction
Submitted by timgoddard on 17 August, 2010 - 19:30Geographic 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.
- timgoddard's blog
- 1 comment
- Read more
- 15704 reads
Add Custom Options to Right Click Menu
Submitted by Vishesh on 17 August, 2010 - 15:01
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 blog
- 1 comment
- Read more
- 22933 reads
Creation Date of Excel File
Submitted by Vishesh on 17 August, 2010 - 14:58There 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 blog
- Login or register to post comments
- Read more
- 18155 reads
File Manipulation from VBA
Submitted by Vishesh on 17 August, 2010 - 14:56Following 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 blog
- 1 comment
- Read more
- 21986 reads
Using Instr with Optional Backward Search
Submitted by Vishesh on 16 August, 2010 - 16:08You 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 blog
- Login or register to post comments
- Read more
- 9771 reads
Start Position of a Nth Instance of String in a piece of Text
Submitted by Vishesh on 8 August, 2010 - 17:00Code 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 blog
- Login or register to post comments
- Read more
- 6443 reads
Customised OnKey with example
Submitted by Vishesh on 8 August, 2010 - 14:32Download 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 blog
- Login or register to post comments
- Read more
- 14480 reads
Check if a worksheet already exists in a workbook
Submitted by Vishesh on 8 August, 2010 - 14:16- Vishesh's blog
- 2 comments
- Read more
- 26791 reads
Extract Nth Word from a String - VBA
Submitted by Vishesh on 19 July, 2010 - 05:29Function 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 blog
- 2 comments
- 23909 reads
If Formula
Submitted by patricn on 3 May, 2010 - 12:51I 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
- patricn's blog
- 1 comment
- 4173 reads
Directory
Submitted by fashmina on 1 April, 2010 - 12:16Hi please help me.
- fashmina's blog
- 2 comments
- Read more
- 5560 reads
Excel VBA Data Validation Code
Submitted by Vishesh on 27 March, 2010 - 04:05
This is a generalised code for Excel VBA Data Validation. It can be used across sheets in a workbook.
- Vishesh's blog
- 1 comment
- Read more
- 33618 reads
Feb-2010 ExcelExperts.com Update
Submitted by Nick on 3 February, 2010 - 09:42January 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 blog
- 1 comment
- Read more
- 5934 reads
98. Excel Tips - Sort Columns
Submitted by Nick on 2 February, 2010 - 16:12Excel 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:
And here's what happens when we sort columns:
- Nick's blog
- 1 comment
- Read more
- 7641 reads
97. Excel Tips - Camera Tool
Submitted by Nick on 26 January, 2010 - 11:36
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 blog
- 4 comments
- Read more
- 19328 reads
What can't be done in Excel ?
Submitted by Nick on 25 January, 2010 - 10:48
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 blog
- 8 comments
- Read more
- 15686 reads
96. Excel Tips - Keyboard Shortcut To Open Excel
Submitted by Nick on 18 January, 2010 - 10:01Keyboard 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
- Nick's blog
- Login or register to post comments
- Read more
- 45430 reads
VBA Tips: Easy way to navigating with Range Object
Submitted by JeffLo on 16 January, 2010 - 05:43One 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.
- JeffLo's blog
- Login or register to post comments
- Read more
- 14821 reads
Jan-2010 NEW Excel VBA Jobs section
Submitted by Nick on 15 January, 2010 - 14:36
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 blog
- Login or register to post comments
- Read more
- 12979 reads
95. Excel Tips - Cause Of Big Excel Files
Submitted by Nick on 14 January, 2010 - 16:03
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
- Nick's blog
- 9 comments
- Read more
- 101414 reads
Advanced Filter
Submitted by Vishesh on 9 January, 2010 - 21:49With 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 blog
- 1 comment
- 13584 reads
Excel VBA Tip - Bypassing Clipboard while copying values or formula in Excel
Submitted by Vishesh on 9 January, 2010 - 18:04Sheet1.Range("A1:A5").Copy Sheet1.Range("B1").PasteSpecial xlPasteValues
Sub CopyValues(rngSource As Range, rngTarget As Range) rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value End Sub
Call CopyValues(Sheet1.Range("A1:A5"), Sheet1.Range("B1"))
- Vishesh's blog
- 28 comments
- Read more
- 147450 reads
32. VBA Tips - Turn Off Autofilter
Submitted by Nick on 4 January, 2010 - 14:43
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:
- Nick's blog
- 1 comment
- Read more
- 46709 reads
Recent comments
5 years 36 weeks ago
6 years 22 weeks ago
6 years 34 weeks ago
6 years 37 weeks ago
6 years 38 weeks ago
6 years 43 weeks ago
6 years 52 weeks ago
7 years 2 days ago
7 years 3 days ago
7 years 3 days ago