Vishesh's blog

Vishesh's picture

Empty/Blank Modules in Excel

While working on Excel VBA project at times you leave a blank or empty module. However, this can be removed using the following code. Copy it in any general module and run.
Public Sub RemoveEmptyModules()
    'This CANNOT remove Sheet modules
    'Can remove Standard modules and Class modules only
    'For this to you should have the option "Trust access to the VBA project object model"
    'checked.
Vishesh's picture

Create Shortcut for Notepad on Desktop (VBA)

The following code creates a shortcut for notepad on Desktop.
Vishesh's picture

List and Type of Drives (VBA)

Copy the following code in any general module and run.
Vishesh's picture

Special Folders' Path (VBA)

The following code displays path of all special folders in Windows on the worksheet.
Vishesh's picture

Evaluate (VBA) for Concatenation

Just learnt it a few days back from one of my office colleagues...how to concatenate a range of values with other range without having to run a loop. Download the attachment to see how it works.
Evaluate
Vishesh's picture

Sheet Protect (User Interface only) using VBA

Generally, if you want your vba code to run on a protected sheet, you wrap your code in unprotect/protect statements in every procedure or function. You also have to remember to protect the sheet in case it is unprotected. Here is the attached file that clearly explains how you can avoid this.
Vishesh's picture

Solution to Problem

Solution for problem on url:

http://www.excelexperts.com/node/1144

Please download the attachment

Vishesh's picture

Solution for filter by day parts

Solution for problem on url
http://www.excelexperts.com/node/1139

For the user defined function used in the sample file check its Module.

Vishesh's picture

Solution for filter rows

Solution to problem given posted on url.

http://excelexperts.com/node/1137

Vishesh's picture

Concatenate Array

This is a small piece of code that can be used to concatenate the elements of an array.

You can use it on elements of a listbox or combobox. See the attached file for reference.

 

Function ConcatenateArrayElements(arrElement As Variant, Optional  _ 
strConcatenator As String = ", ") As String
 
 ConcatenateArrayElements = Join(arrElement, strConcatenator)
 
End Function

 

 

Concatenate_Array

Vishesh's picture

SUMIF - More than 1 Criteria in Excel 2007

The SUMIF function can be used to sum a range on cells based on certain criteria.

With Excel 2007, Microsoft introduced another addition to SUMIF i.e. SUMIFS  that allows you to pass more than one criteria. Both SUMIF and SUMIFS  have been explained in the attached workbook. Try changing the values in yellow cells and see how the result under sales column changes...

 

Sumif

Vishesh's picture

Solution to Problem

 Solution to problem asked on

http://excelexperts.com/node/1113

I have provided a button on the click of which your required output is generated.

Vishesh's picture

Create Access Table from Excel VBA

Using the following piece of code you can create an Access table specifying the fields and types of your choice. You don't need to have Access on your system.

Vishesh's picture

Unzip Files (using VBA)

Here is the small piece of code that unzips file into a target folder. Just use the following code procedure with your own parameter values.

Vishesh's picture

AlternativeText Property of Shapes

All shapes (including Charts) in Excel have AlternativeText property. This property can be used to store some information as well. Following is the code to read and write to AlternativeText property of a shape.

Sub SetAlternativeText()
 
    Sheet1.Shapes("Rectangle 1").AlternativeText = "ABC"
 
End Sub
 
 
 
Sub GetAlternativeText()
 
    MsgBox Sheet1.Shapes("Rectangle 1").AlternativeText
 
End Sub
Vishesh's picture

Set Excel Environment

Here are a set of commands that you can use to set excel environment.

Vishesh's picture

Custom Properties

This is how you can create a Custom Property in an Excel Workbook to save information. Go to File->Property  (as in pic).

Here is the code to read and write to the Custom Property.

Sub SetProperty()
 
    ThisWorkbook.CustomDocumentProperties("EmpName") = "Vishesh"
 
End Sub
 
 
Sub GetProperty()
 
    MsgBox ThisWorkbook.CustomDocumentProperties("EmpName").Value
 
End Sub

Custom Properties

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

 

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

 

Syndicate content