Blogs

Vishesh's picture

Want to automate report - (Solution)

Solution for problem asked on

 http://excelexperts.com/node/1093

Vishesh's picture

Create and Remove Hyperlinks (from VBA)

Often you have to create and remove hyperlinks at run time. Just paste the following code in a general module:
Sub Create()
    Call CreateHyperlink(Sheet1.Range("A1:A10"))
End Sub
 
Sub Delete()
    Call RemoveHyperlink(Sheet1.Range("A1:A10"))
End Sub
 
Sub CreateHyperlink(rng As Range)
    Dim rngCell As Range
    For Each rngCell In rng
        rngCell.Hyperlinks.Add rngCell, "", , "Screen_Tip", rngCell.Value
    Next rngCell
End Sub
 
Sub RemoveHyperlink(rng As Range)
    rng.Hyperlinks.Delete
End Sub
Vishesh's picture

Check if exists in List

This utility demonstrates how you can use the match function to find a string even in a listbox or a dropdown.
Sub Check()
 
    If blnExists(Sheet1.Range("C13").Value, Sheet1.ListBoxes("List Box 1")) = True Then
 
        MsgBox "Exists"
 
    Else
 
        MsgBox "Does not exists"
 
    End If
 
End Sub
 
 
 
Function blnExists(strFind As String, lst As ListBox) As Boolean
 
    On Error Resume Next
 
    blnExists = (Application.WorksheetFunction.Match(strFind, lst.List, 0) > 0)
 
    If Err.Number > 0 Then blnExists = False
 
    On Error GoTo 0
 
End Function
</
Vishesh's picture

Assign Macros to Shapes

This small utility shows how to assign macros to all the shapes in one go. See the code in module modAssignMacros

 

Assign Macros

Vishesh's picture

Form Control vs ActiveX Control

In Excel 2003

to view Form Controls toolbar: Menu: View->Toolbar->Forms

to view ActiveX toolbar: Menu: View->Toolbar->Control Toolbox

 

Draw a listbox from each of the toolbars

 

Form Control:

 1. The code is always written in a general module.

 2. Right Click the control and using assign macro option choose the right procedure that you want to call on the click/change in that control

To see code for form control goto FormControl module

 

Facebook Texas Holdem Poker Download

Vishesh's picture

Zip Files from Excel

Copy the following code in a general module and call the TestRun function with the file paths and name as per you requirement.
Vishesh's picture

Customised Cell Comments

This is a small piece of code showing how to customise your cell comments to make it look better. The code works fine in Excel 2003.
Vishesh's picture

Create a blank Access Database from Excel

Following is a small piece of code to create a blank Access Database.
Vishesh's picture

Record Keeping from Excel to Access

 This is a small utility to show how to use Access as a backend to the Excel interface. Using this utility you can enter data in Excel which is then saved in Access. The use of Access makes the data sharing easier.

Access Database Details used in the example (Create an access database with same details):

Database name: Trade_DB

Table Name: TRADING_DETAILS

Field 1: TradeNumber ; Type: Number

Field 2: Trader ; Type: Text

Vishesh's picture

Multi-Level Sort

This utility works with Excel 2007 version only as Excel 2003 doesn't allow for more than 3-level sorting. Try and use it. Its very useful and easy to use and implement in your own projects.

 

Multi-Level Sort

Vishesh's picture

Quick Immediate Window Commands useful for debugging

1. When you have a form open and you encounter an error and you want to go to the excel sheet

You need to hide the form first before you can click on the sheet

    Me.Hide

2. If you want to activate a sheet other than the current activesheet its better to use sheet code name than the names which are displayed in sheet tabs

    Sheet3.Activate

3. To display/hide sheet tabs

    ActiveWindow.DisplayWorkbookTabs = True

4. If you are working on more than one workbooks, then to activate a particular workbook

Vishesh's picture

Get Values from other workbook without opening

You can use the following piece of code to get values into an opened workbook from a closed workbook.

 

Vishesh's picture

Break External Links with other workbooks

At times you come across the this dialog box when you open an Excel workbook. To get rid of this you can run a small piece of code just once. This code would break all links.

External Links Exist

 

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:

Move information from one column (automatically) to another

I have a row of names, with numerical data in 10 ajoining rows (name in row A, with numerical date in rows B-K).  I want to automatically move these entries, from rows A-K to matching names in row M along with the corresponding numerical data.  There are single entry names in Row A, with numerous corresponding names in Row M.  Very time consuming to individually copy and past. 

Appreciate any help you can provide.

Automated email from excel / outlook

 

I have a spreadsheet, which we maintain for work allocation for our team, we have a 24 hours Turn around Time (TAT) for the work to processed. I want an automated email to be sent through excel once a particular work has exceeded the 24 hours TAT.

 

This spreadsheet is a shared file, as we all need to update it.

 

Please advice.

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

Syndicate content