Home

Blogs

Vishesh's picture

Remove Duplicates (Get Uniques) Excel 2007

Submitted by Vishesh on 9 July, 2011 - 19:08
You can use the following simple procedure to remove duplicates either in place or get uniques at another range. The second parameter of the below procedure is optional. Try this in Excel 2007. 
Sub RemoveDups(rngDups As Range, Optional rngTarget As Range)
    If rngTarget Is Nothing Then
        rngDups.RemoveDuplicates Columns:=1, Header:=xlNo
    Else
        rngDups.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rngTarget, Unique:=True
    End If
End Sub
 
Sub Test()
    Call RemoveDups(ActiveSheet.Range("A1:A10"), ActiveSheet.Range("D1"))
End Sub
»
Vishesh's picture

Simple way to add Event Proc skeleton in a sheet module

Submitted by Vishesh on 1 July, 2011 - 17:08
'Here is the code to add an Activate event to the sheet module. 
Sub CreateEventProcedure(wks As Worksheet, strEvtProc As String)
    With ActiveWorkbook.VBProject.VBComponents(wks.CodeName).CodeModule
        'Add the blank procedure
        .CreateEventProc strEvtProc, "Worksheet"
    End With
End Sub
 
Sub TestProc()
    Call CreateEventProcedure(ThisWorkbook.Worksheets("Sheet2"), "Activate")
    MsgBox "Goto Sheet2 module in the code window. The activate procedure skeleton is added.", vbInformation, "Excel Experts"
End Sub
»
Vishesh's picture

Calling macro (with/without parameters) from other workbook

Submitted by Vishesh on 13 May, 2011 - 17:21
Following code runs the macro from other opened workbook.
»
Vishesh's picture

Open/Close CD Tray using VBA

Submitted by Vishesh on 11 May, 2011 - 17:22
Paste the following code in a module and try the two modules for open or close CD Tray. 
Declare Sub mciSendStringA Lib "winmm.dll" (ByVal lpstrCommand As String, _
    ByVal lpstrReturnString As Any, ByVal uReturnLength As Long, _
    ByVal hwndCallback As Long)
 
Sub CDTrayOpen()
    mciSendStringA "Set CDAudio Door Open", 0&, 0, 0
End Sub
 
Sub CDTrayClose()
    mciSendStringA "Set CDAudio Door Closed", 0&, 0, 0
End Sub
»
Nick's picture

33. VBA Tips - Output Array without looping.

Submitted by Nick on 11 May, 2011 - 13:04

A common approach to outputting an array is to loop through each element of the array, and write each element to an Excel cell.

This methodology however is very inefficient. If you have a big array, you will really notice that performance is awful, especially if calculation is on automatic.

Here's a code sample that picks up the values of a range, and writes them back:

»
Vishesh's picture

Check if VBProject is protected

Submitted by Vishesh on 11 May, 2011 - 08:53
This code lets you know whether the VB project is protected or not Add a reference to the Microsoft Visual Basic Extensibility Library It returns TRUE if the VB project in the active document is protected 
Function ProtectedVBProject(ByVal wb As Workbook) As Boolean
    Dim intVBComp As Integer
    intVBComp = -1
    On Error Resume Next
    intVBComp = wb.VBProject.VBComponents.Count
    If Err.Number = 1004 Then
        MsgBox "Add reference to the Microsoft Visual Basic Extensibility Library", vbCritical, "Excel Experts"
    End If
    On Error GoTo 0
    If intVBComp = -
»
Vishesh's picture

Show Font List with Example

Submitted by Vishesh on 11 May, 2011 - 08:32
Here is a code that lists all installed fonts with example. Copy it in any module and run (F5) 
Sub ShowInstalledFonts()
    Const StartRow As Integer = 4
    Dim cbcFontName As CommandBarControl, cbrFontCmd As CommandBar, strFormula As String
    Dim strFontName As String, i As Long, lngFontCount As Long, intFontSize As Integer
 
    intFontSize = 10
 
    If intFontSize = 0 Then Exit Sub
    If intFontSize < 8 Then intFontSize = 8
    If intFontSize > 30 Then intFontSize = 30
 
    Set cbcFontName = Application.CommandBars("Formatting").FindControl(ID:=1728)
 
»
Vishesh's picture

Dynamic Update solved

Submitted by Vishesh on 9 May, 2011 - 17:06

Solution for Question posted on url
http://excelexperts.com/node/1168

I have inserted a column at column A and concatenated the system and discipline. Used Vlookup to get the values. At some places it shows #N/A as there is no exact match of the discipline found.

»
Vishesh's picture

Generate Summary (Solved)

Submitted by Vishesh on 7 May, 2011 - 18:25

The attachment contains the solution for the problem posted on

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

I have added a summary sheet and provided a button to generate the summary. Hope this solves your problem

»
Vishesh's picture

Display Images and Charts on Form

Submitted by Vishesh on 7 May, 2011 - 07:31

Attached is an excel workbook that shows how you can display all images and charts of a workbook on a form. See the general module in the code window to see the code that runs to make it happen. Modify it accordingly to suit your requirements.

Screen Shot:

ImageOnForm
»

IF VALUE EQUALS X THEN COPY PASTE ROW TO NEW SHEET

Submitted by tigershark45_can on 3 May, 2011 - 15:30

Help!.. I would like to know how to copy paste from one sheet to another within the same file using the IF function.
Basically, looking a thousands of columns, if I want to copy specific rows (A15:K15), (B15:K15) etc.. of "sheet 1" to "sheet 2" only if (employee number) D15 = 11 (of sheet 1).

I would like the rows to follow in "sheet 2" and not appear at row 1500 if there is only 20 rows that were copied.

Does this make any sense? If VBA is needed... what would the code look like?

Thanks,

Tigershark

»
Vishesh's picture

Reading from Clipboard

Submitted by Vishesh on 30 April, 2011 - 20:21
The following piece of code displays any text which is there in the clipboard. To try it paste the following code in the general module. Select any text from anywhere and press Ctrl+C to copy to clipboard. Now, run the following code. It will display what you selected and copied to clipboard using Ctrl+C. 
Sub GetTextFromClipboard()
    'For DataObject add reference to Microsoft Forms 2.0 Object Library
    'If you cannot find this library just add a userform and
    'now you will find the library reference.
»
Vishesh's picture

Empty/Blank Modules in Excel

Submitted by Vishesh on 30 April, 2011 - 12:41
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)

Submitted by Vishesh on 30 April, 2011 - 12:11
The following code creates a shortcut for notepad on Desktop.
»
Vishesh's picture

List and Type of Drives (VBA)

Submitted by Vishesh on 30 April, 2011 - 11:58
Copy the following code in any general module and run.
»
Vishesh's picture

Special Folders' Path (VBA)

Submitted by Vishesh on 30 April, 2011 - 11:39
The following code displays path of all special folders in Windows on the worksheet.
»
Vishesh's picture

Evaluate (VBA) for Concatenation

Submitted by Vishesh on 27 April, 2011 - 16:07
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

Submitted by Vishesh on 19 April, 2011 - 14:31
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

Submitted by Vishesh on 18 April, 2011 - 13:29

Solution for problem on url:

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

Please download the attachment

»

Solution to Time Stamp Issue

Submitted by Robert on 17 April, 2011 - 10:26

One way of resoliving the issue described in

www.excelexperts.com/node/1134

is illustrated in the attached file.

»
Vishesh's picture

Solution for filter by day parts

Submitted by Vishesh on 12 April, 2011 - 16:37

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

Submitted by Vishesh on 10 April, 2011 - 07:35

Solution to problem given posted on url.

http://excelexperts.com/node/1137

»
Vishesh's picture

Concatenate Array

Submitted by Vishesh on 23 March, 2011 - 17:59

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

Submitted by Vishesh on 15 March, 2011 - 16:21

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

Submitted by Vishesh on 5 March, 2011 - 17:52

 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

Submitted by Vishesh on 5 March, 2011 - 10:54

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)

Submitted by Vishesh on 4 March, 2011 - 18:14

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

Submitted by Vishesh on 4 March, 2011 - 17:45

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

Submitted by Vishesh on 3 March, 2011 - 15:21

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

»
Vishesh's picture

Custom Properties

Submitted by Vishesh on 3 March, 2011 - 15:02

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

»
Syndicate content