Blogs

Remove Duplicates (Get Uniques) Excel 2007
Submitted by Vishesh on 9 July, 2011 - 19:08Sub 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 blog
- Login or register to post comments
- 11229 reads

Simple way to add Event Proc skeleton in a sheet module
Submitted by Vishesh on 1 July, 2011 - 17:08Sub 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 blog
- 2 comments
- 5875 reads

Calling macro (with/without parameters) from other workbook
Submitted by Vishesh on 13 May, 2011 - 17:21- Vishesh's blog
- Login or register to post comments
- Read more
- 39183 reads

Open/Close CD Tray using VBA
Submitted by Vishesh on 11 May, 2011 - 17:22Declare 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
- Vishesh's blog
- Login or register to post comments
- 15478 reads

33. VBA Tips - Output Array without looping.
Submitted by Nick on 11 May, 2011 - 13:04A 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:
- Nick's blog
- Login or register to post comments
- Read more
- 70127 reads

Check if VBProject is protected
Submitted by Vishesh on 11 May, 2011 - 08:53Function 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 blog
- Login or register to post comments
- Read more
- 5269 reads

Show Font List with Example
Submitted by Vishesh on 11 May, 2011 - 08:32Sub 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 blog
- Login or register to post comments
- Read more
- 12149 reads

Dynamic Update solved
Submitted by Vishesh on 9 May, 2011 - 17:06Solution 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 blog
- Login or register to post comments
- 5488 reads

Generate Summary (Solved)
Submitted by Vishesh on 7 May, 2011 - 18:25The 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 blog
- 4 comments
- 6879 reads

Display Images and Charts on Form
Submitted by Vishesh on 7 May, 2011 - 07:31Attached 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:
- Vishesh's blog
- Login or register to post comments
- 5187 reads
IF VALUE EQUALS X THEN COPY PASTE ROW TO NEW SHEET
Submitted by tigershark45_can on 3 May, 2011 - 15:30Help!.. 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
- tigershark45_can's blog
- 4 comments
- 46673 reads

Reading from Clipboard
Submitted by Vishesh on 30 April, 2011 - 20:21Sub 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 blog
- 1 comment
- Read more
- 15724 reads

Empty/Blank Modules in Excel
Submitted by Vishesh on 30 April, 2011 - 12:41Public 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 blog
- 1 comment
- Read more
- 20051 reads

Create Shortcut for Notepad on Desktop (VBA)
Submitted by Vishesh on 30 April, 2011 - 12:11- Vishesh's blog
- Login or register to post comments
- Read more
- 15388 reads

List and Type of Drives (VBA)
Submitted by Vishesh on 30 April, 2011 - 11:58- Vishesh's blog
- 1 comment
- Read more
- 35122 reads

Special Folders' Path (VBA)
Submitted by Vishesh on 30 April, 2011 - 11:39- Vishesh's blog
- 3 comments
- Read more
- 18995 reads

Evaluate (VBA) for Concatenation
Submitted by Vishesh on 27 April, 2011 - 16:07- Vishesh's blog
- 3 comments
- Read more
- 15307 reads

Sheet Protect (User Interface only) using VBA
Submitted by Vishesh on 19 April, 2011 - 14:31- Vishesh's blog
- Login or register to post comments
- Read more
- 51294 reads

Solution to Problem
Submitted by Vishesh on 18 April, 2011 - 13:29- Vishesh's blog
- Login or register to post comments
- 5225 reads
Solution to Time Stamp Issue
Submitted by Robert on 17 April, 2011 - 10:26One way of resoliving the issue described in
www.excelexperts.com/node/1134
is illustrated in the attached file.
- Robert's blog
- Login or register to post comments
- 5467 reads

Solution for filter by day parts
Submitted by Vishesh on 12 April, 2011 - 16:37Solution 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 blog
- Login or register to post comments
- 6553 reads

Solution for filter rows
Submitted by Vishesh on 10 April, 2011 - 07:35Solution to problem given posted on url.
- Vishesh's blog
- 1 comment
- 5224 reads

Concatenate Array
Submitted by Vishesh on 23 March, 2011 - 17:59This 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
- Vishesh's blog
- 1 comment
- 11511 reads

SUMIF - More than 1 Criteria in Excel 2007
Submitted by Vishesh on 15 March, 2011 - 16:21The 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...
- Vishesh's blog
- Login or register to post comments
- 11858 reads

Solution to Problem
Submitted by Vishesh on 5 March, 2011 - 17:52Solution 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 blog
- 2 comments
- 4579 reads

Create Access Table from Excel VBA
Submitted by Vishesh on 5 March, 2011 - 10:54Using 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 blog
- 1 comment
- Read more
- 42893 reads

Unzip Files (using VBA)
Submitted by Vishesh on 4 March, 2011 - 18:14Here 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 blog
- 14 comments
- Read more
- 154538 reads

AlternativeText Property of Shapes
Submitted by Vishesh on 4 March, 2011 - 17:45All 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 blog
- Login or register to post comments
- 10756 reads

Set Excel Environment
Submitted by Vishesh on 3 March, 2011 - 15:21Here are a set of commands that you can use to set excel environment.
- Vishesh's blog
- Login or register to post comments
- Read more
- 8482 reads

Custom Properties
Submitted by Vishesh on 3 March, 2011 - 15:02This 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
- Vishesh's blog
- Login or register to post comments
- 22140 reads

Recent comments
5 years 50 weeks ago
6 years 36 weeks ago
6 years 47 weeks ago
6 years 50 weeks ago
6 years 51 weeks ago
7 years 5 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago