This code saves active sheet to temp file and then email it to the recipient. Once the mail has been sent the temp file is deleted by the code.
You will need to copy this code to regular VBA module of your workbook
|
Most Popular
Showing posts with label Excel Macros. Show all posts
Showing posts with label Excel Macros. Show all posts
Saturday, July 24, 2010
Excel Macros : Send Email with VBA
The Code I am sharing with you today is capable of sending active sheet of your workbook through outlook mail to email ID specified by you. You will not get any warning messages and the email will be sent using outlook profile available on your machine.
This code saves active sheet to temp file and then email it to the recipient. Once the mail has been sent the temp file is deleted by the code.
You will need to copy this code to regular VBA module of your workbook
This code saves active sheet to temp file and then email it to the recipient. Once the mail has been sent the temp file is deleted by the code.
You will need to copy this code to regular VBA module of your workbook
Saturday, April 10, 2010
Show Developer Tab in Excel 2007 Ribbon
In case you are working with ActiveX Controls or Macros in Excel , you need to have Developers Tab visibile in your Excel 2007. When you start using Excel 2007, developers Tab is not visible , however you can set it visible with following steps
Step 1 - Click MS Office Button
Step 2 - Click Excel Options
Step 3 - Click Populor
Step 4 - Tick Show Developer Tab in the Ribbon check box
Step 5 - Click Ok
Step 1 - Click MS Office Button
Step 2 - Click Excel Options
Step 3 - Click Populor
Step 4 - Tick Show Developer Tab in the Ribbon check box
Step 5 - Click Ok
Thursday, March 4, 2010
Excel VBA : Deploy Macros Found Elsewhere
Today I am sharing various ways to add macros found elsewhere to a Excel file where that macro is to be deployed. Will be covering 6 ways to add macro code to your excel file.
1. Excel VBA : Add code to a Regular Module
2. Excel VBA : Add code to WorkBook Module
3. Excel VBA : Add code to Worksheet Module
4. Excel VBA : Copy Module from Existing Workbook
5. Excel VBA : Copy Forms from Existing Workbook
6. Excel VBA : Import code from .bas file
Excel VBA : Add code to a Regular Module
-Copy code from the source
-Open the workbook where you need to deploy this code
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-Click Insert > Module (Shortcut ALT+I+M)
-Paste macro code Click Edit > Paste (Shortcut CTRL+V) in right window where cursor is
See screencast below for Alternate method using right click menu through mouse.
Excel VBA : Add code to WorkBook Module
Some macros are event based macros and get activated as soon as particular event happens. Events which are universally applicable to the entire workbook are called workbook events. These macros will run once a particular event happens in a particular workbook. Typical example of such macros are Auto Open / Auto Close macros
-Copy code from the source
-Open the workbook where you need to deploy this
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-Double Click on ThisWorkbook object
-Paste macro code Click Edit > Paste (Shortcut CTRL+V) in right window where cursor is
See screencast below
Excel VBA : Add code to Worksheet Module
Some of event based macros get activated as soon as particular event happens on a particular worksheet within the workbook. These are the macros which will run on a event in particular worksheet. Typical example of such macros are Worksheet Select / Worksheet Change macros
-Copy code from the source
-Open the workbook where you need to deploy this
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-Double Click on Worksheet object (Choose particular worksheet where you want to deploy the code)
-Paste macro code Click Edit > Paste (Shortcut CTRL+V) in right window where cursor is
See screencast below
Excel VBA : Copy Module from Existing Workbook
You may have the code in existing workbook (Source) with you or recently downloaded from Internet. Following steps will deploy it to new workbook (Target)
-Open both the Workbooks (Source and Target)
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-In Left window of project explorer find Source workbook
-Select the source module
-Drag it to the Target workbook
See screencast below
Excel VBA : Copy Forms from Existing Workbook
Steps and requirement is similar to copy code from existing workbook
-Open both the Workbooks (Source and Target)
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-In Left window of project explorer find Source workbook
-Select the Form to be copied
-Drag it to the Target workbook
See screencast below
Excel VBA : Import code from .bas file
Some time you may get code in downloadable file with extention BAS. This is visual basic file .bas , you can import this to your workbook.
-Open your Workbook
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-In Left window of project explorer find Source workbook
-Click File > Import (CTRL+M)
-Choose downloaded VB file (*.BAS , *.FRM, *.CLS)
-Click Open
See screencast below
1. Excel VBA : Add code to a Regular Module
2. Excel VBA : Add code to WorkBook Module
3. Excel VBA : Add code to Worksheet Module
4. Excel VBA : Copy Module from Existing Workbook
5. Excel VBA : Copy Forms from Existing Workbook
6. Excel VBA : Import code from .bas file
Excel VBA : Add code to a Regular Module
-Copy code from the source
-Open the workbook where you need to deploy this code
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-Click Insert > Module (Shortcut ALT+I+M)
-Paste macro code Click Edit > Paste (Shortcut CTRL+V) in right window where cursor is
See screencast below for Alternate method using right click menu through mouse.
Excel VBA : Add code to WorkBook Module
Some macros are event based macros and get activated as soon as particular event happens. Events which are universally applicable to the entire workbook are called workbook events. These macros will run once a particular event happens in a particular workbook. Typical example of such macros are Auto Open / Auto Close macros
-Copy code from the source
-Open the workbook where you need to deploy this
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-Double Click on ThisWorkbook object
-Paste macro code Click Edit > Paste (Shortcut CTRL+V) in right window where cursor is
See screencast below
Excel VBA : Add code to Worksheet Module
Some of event based macros get activated as soon as particular event happens on a particular worksheet within the workbook. These are the macros which will run on a event in particular worksheet. Typical example of such macros are Worksheet Select / Worksheet Change macros
-Copy code from the source
-Open the workbook where you need to deploy this
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-Double Click on Worksheet object (Choose particular worksheet where you want to deploy the code)
-Paste macro code Click Edit > Paste (Shortcut CTRL+V) in right window where cursor is
See screencast below
Excel VBA : Copy Module from Existing Workbook
You may have the code in existing workbook (Source) with you or recently downloaded from Internet. Following steps will deploy it to new workbook (Target)
-Open both the Workbooks (Source and Target)
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-In Left window of project explorer find Source workbook
-Select the source module
-Drag it to the Target workbook
See screencast below
Excel VBA : Copy Forms from Existing Workbook
Steps and requirement is similar to copy code from existing workbook
-Open both the Workbooks (Source and Target)
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-In Left window of project explorer find Source workbook
-Select the Form to be copied
-Drag it to the Target workbook
See screencast below
Excel VBA : Import code from .bas file
Some time you may get code in downloadable file with extention BAS. This is visual basic file .bas , you can import this to your workbook.
-Open your Workbook
-Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
-In Left window of project explorer find Source workbook
-Click File > Import (CTRL+M)
-Choose downloaded VB file (*.BAS , *.FRM, *.CLS)
-Click Open
See screencast below
Thursday, January 14, 2010
Custom Function : Excel Closest Match using VBA
Some days back I got a mail from Kevin asking for how to get closest match in a list sorted in acending order
I responded to him with a Custom Function capable to Finding Closest Match as per his requirement. This worked as the list was sorted in ascending order. He sent me a thanks note for this.
However this left certain questions in my mind such as
1. What if the list is not sorted, Custom function should be able to do this in unsorted list.
2. He wanted greater than equal to value, however it should be able to handle less than equal to value also
With these things in my mind I worked out another custom function which works with unsorted data range and is capable of finding both type of closest match in excel.
I am sharing updated Custom Function with all of you. You will need to copy this code to regular VBA module of your workbook
I responded to him with a Custom Function capable to Finding Closest Match as per his requirement. This worked as the list was sorted in ascending order. He sent me a thanks note for this.
However this left certain questions in my mind such as
1. What if the list is not sorted, Custom function should be able to do this in unsorted list.
2. He wanted greater than equal to value, however it should be able to handle less than equal to value also
With these things in my mind I worked out another custom function which works with unsorted data range and is capable of finding both type of closest match in excel.
I am sharing updated Custom Function with all of you. You will need to copy this code to regular VBA module of your workbook
|
Download Excel file with Custom Function to Find Closest Match to see how it works
Friday, January 1, 2010
Excel Macros : Excel Date Picker
This is first post of the year 2010, I wish all my blog readers a very happy and prospurous year. Today I am sharing Excel Date Picker Utility with you. Following picture will demonstrate some to the capabilities of this utility.
This utility will help you with following
1. In case you select a cell with date, or select any cell around the cell with a date. It will show floating button on the left side of the cell. If you click on the floating button it will show popup calender.
2. In case the cell already has a date , the calendar will show same date by default, else it will show current date.
3. You can jump the months by click on the month value on the popup calendar.
4. Click on the year will show spin buttons next to it and you can change year easily with spin buttons.
5. When you click on the date in popup calander, it will insert date clicked to the selected cell
6. You can also use Ctrl+Shift+d to show popup calender on your screen.
7. In case you get pop up calander and you do not want to click any date, just press Esc to close the pou up calendar.
This utility could be usefull in following ways
1. Many people end up entering date in text format as some time it is difficult to enter the date in correct computer readable format. With the popup calander, system takes care of the proper date entry.
2. Some times you have to get data from other users and you want them to enter dates in proper format so that rest of the worksheet can use those date inputs. With this utility you can be assured about this issue.
You may figure out many more usage based on your needs. The VBA code for Excel date Picker is available in the downloadable file. You can further modify the code as per your needs.
1. Download file and save it as Excel Addin on your machine.
2. You will need to copy this code to worksheet module of your workbook.
You will need to add this code to show the Floating button. This code will creat the button in case it does not exist on it. All other features like cell menu on right click and popup calender on Ctrl+Shift+d will be available in case you do not add this code by saving download able file as Excel Addin on your machine. I am suggesting this because I still need to learn how to trigger worksheet selection change event with a vba code in addin.
However this will not work on the files you send to other users. In that case you need to builtin the entire utility into the file you are sending out.
Following steps will help you to do this.
1. You will need to copy this code to workbook module of your workbook
2. You will need to copy this code to regular VBA module - Module1 of your workbook
3. Move form named calander from the downloadable file to your file.
With these steps you can send the file to other users and it will work in similar way as it works on your machine.
In addition you can force users to enable macros while using your file
This utility will help you with following
1. In case you select a cell with date, or select any cell around the cell with a date. It will show floating button on the left side of the cell. If you click on the floating button it will show popup calender.
2. In case the cell already has a date , the calendar will show same date by default, else it will show current date.
3. You can jump the months by click on the month value on the popup calendar.
4. Click on the year will show spin buttons next to it and you can change year easily with spin buttons.
5. When you click on the date in popup calander, it will insert date clicked to the selected cell
6. You can also use Ctrl+Shift+d to show popup calender on your screen.
7. In case you get pop up calander and you do not want to click any date, just press Esc to close the pou up calendar.
This utility could be usefull in following ways
1. Many people end up entering date in text format as some time it is difficult to enter the date in correct computer readable format. With the popup calander, system takes care of the proper date entry.
2. Some times you have to get data from other users and you want them to enter dates in proper format so that rest of the worksheet can use those date inputs. With this utility you can be assured about this issue.
You may figure out many more usage based on your needs. The VBA code for Excel date Picker is available in the downloadable file. You can further modify the code as per your needs.
Now how to use this Excel Date Picker Utility
1. Download file and save it as Excel Addin on your machine.
2. You will need to copy this code to worksheet module of your workbook.
You will need to add this code to show the Floating button. This code will creat the button in case it does not exist on it. All other features like cell menu on right click and popup calender on Ctrl+Shift+d will be available in case you do not add this code by saving download able file as Excel Addin on your machine. I am suggesting this because I still need to learn how to trigger worksheet selection change event with a vba code in addin.
Private Sub FloatingButton_Click()
Calander.Show
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = 1 Then
Exit Sub
ElseIf Target.Cells.Count > 1 Then
Exit Sub
Else
Set r = Target
Hasdate = 0
For x = -1 To 1
For y = -1 To 1
On Error GoTo Etrap1
If IsDate(r.Offset(x, y)) Then
Hasdate = 1
Exit For
End If
Rsume1:
Next y
If Hasdate = 1 Then Exit For
Rsume:
Next x
If Hasdate = 1 Then
FloatingButton.Left = r.Offset(0, 1).Left
FloatingButton.Top = r.Top
FloatingButton.Visible = True
Else
If Not FloatingButton.Visible = False Then
FloatingButton.Visible = False
End If
End If
Exit Sub
End If
Etrap1:
If r.Row = 1 Then
Resume Rsume
ElseIf r.Column = 1 Then
Resume Rsume1
End If
End Sub
Private Sub Worksheet_Activate()
Dim s As String
On Error Resume Next
s = ActiveSheet.FloatingButton.Caption
If Err.Number <> 0 Then
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=147.75, Top:=34.5, Width:=15.75, Height:= _
15.75).Select
Selection.Name = "FloatingButton"
ActiveSheet.OLEObjects("FloatingButton").Object.Caption = ""
ActiveSheet.OLEObjects("FloatingButton").Object.BackColor = &H80000005
Range("A1").Select
FloatingButton.Visible = False
End If
On Error GoTo 0
End Sub
|
However this will not work on the files you send to other users. In that case you need to builtin the entire utility into the file you are sending out.
Following steps will help you to do this.
1. You will need to copy this code to workbook module of your workbook
Private Sub Workbook_Open()
Dim NewControl As CommandBarControl
Application.OnKey "+^{D}", "Module1.ShowCalander"
On Error Resume Next
Application.CommandBars("Cell").Controls("Show Date Picker").Delete
On Error GoTo 0
Set NewControl = Application.CommandBars("Cell").Controls.Add
With NewControl
.Caption = "Show Date Picker"
.OnAction = "Module1.ShowCalander"
.BeginGroup = True
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Cell").Controls("Show Date Picker").Delete
End Sub
|
2. You will need to copy this code to regular VBA module - Module1 of your workbook
Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Declare Function DrawMenuBar Lib "user32" (ByVal hwnd As Long) As Long
Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Declare Function ReleaseCapture Lib "user32" () As Long
Private Const GWL_STYLE As Long = (-16)
Private wHandle As Long
Sub ShowCalander()
Calander.Show
End Sub
|
3. Move form named calander from the downloadable file to your file.
With these steps you can send the file to other users and it will work in similar way as it works on your machine.
In addition you can force users to enable macros while using your file
Download file with Excel Date Picker Utility
Saturday, December 5, 2009
Excel Macros : Text to Numbers VBA
Some times you get data which has some numbers surrounded by lots of text or non text characters or space in between text and numbers. Position of the numbers within text is not same in each data line. In such situation you may not be able to apply standard Excel Formulas to extract number from text. In such scenario you will need to use VBA to Extract Number from String. Today I am sharing with you UDF to perform this function. Just for illustration following table will show you capability of this UDF.
Above Numeric Values have been extracted using simple formula =Number(B3) , In this case text string is in Cell B3. This formula is not available in standard excel functions. To apply this formula You will need to copy this code to regular VBA module of your workbook
| Text Data | Extracted Numeric Value |
| xusdhd 10005000 | 10005000 |
| AKSID0100050000 A1 | 100050000 |
| IU EW KFID100050000 A 1 | 100050000 |
| 10005 0000 A1 | 100050000 |
| 01000 A1 B 12 | 1000 |
Above Numeric Values have been extracted using simple formula =Number(B3) , In this case text string is in Cell B3. This formula is not available in standard excel functions. To apply this formula You will need to copy this code to regular VBA module of your workbook
|
Download excel file with UDF for Text to Numbers
Wednesday, November 18, 2009
Excel Macro : Email Address Extract from String
Last week I got a mail from my friend asking for help in extracting email IDs from data available with him. The position of the mail IDs within the text string was not same and he was finding it difficult to us extract email addresses.
I wrote a quick UDF for him which did the job. I am sharing same with you as you may find it use full.
Sample of the data and mail IDs extracted with the UDF is as below
Here is the VBA code to Email Address Extract from String. You will need to copy this code to regular VBA module of your workbook
I wrote a quick UDF for him which did the job. I am sharing same with you as you may find it use full.
Sample of the data and mail IDs extracted with the UDF is as below
Here is the VBA code to Email Address Extract from String. You will need to copy this code to regular VBA module of your workbook
|
Download file with VBA code to Email Address Extract from Text String
Friday, November 13, 2009
Excel Macro : Hide Cell Content from Printing
Some times we have some information in worksheet which is we need to input but do not want that information to be printed. With the following macro code you can achieve the desired results
You will need to copy this code to workbook module of your workbook
As per above code the contents of Range A1 will not be printed, you will need to change this reference to your cell.
You will need to copy this code to workbook module of your workbook
|
As per above code the contents of Range A1 will not be printed, you will need to change this reference to your cell.
Download file with the code to hide cell contents before printing
.Friday, October 30, 2009
Excel Number Format : Indian Style Comma Separation
Yesterday I got a comment asking for custom number format for Indian style comma separation. While I responded to question by providing the custom number format I knew that the answer does not handle the complete range of numbers and can not be applied in all the cases. This forced me to look around for a solution that can be applied for all kind of numbers.
Just for the information of people who are not aware of Indian Style of comma separation, I have produced table below which explains Indian style formatting for various numbers. The basic rule is that first comma separation happens at 3 digits i.e. 1000 then it happens after every 2 digits. Check out table below for better understanding.
Soon I realised that there is no single custom format available for handling all kind of number length and we need separate format for it depending upon the number of digits. Doing this manually and accuratly every time is very difficult. This leaves only one option to automate this through a macro. I got a Macro code for doing this and modified and tested that for long numbers.
I am sharing the macro code with you. You will need to select the numbers and run macro to format them as per India style comma sepration.
For easy application of this code you need to save it as a excel addin or add it to your personal macro book. Adding a custom toolbar for this code will make it easy to apply. You can chage the number of decimal places by changing the number of zeros at the end of custom number format given above.
Just for the information of people who are not aware of Indian Style of comma separation, I have produced table below which explains Indian style formatting for various numbers. The basic rule is that first comma separation happens at 3 digits i.e. 1000 then it happens after every 2 digits. Check out table below for better understanding.
Number | Formatted As |
10 | 10.00 |
100 | 100.00 |
1000 | 1,000.00 |
10000 | 10,000.00 |
100000 | 1,00,000.00 |
1000000 | 10,00,000.00 |
10000000 | 1,00,00,000.00 |
100000000 | 10,00,00,000.00 |
1000000000 | 1,00,00,00,000.00 |
10000000000 | 10,00,00,00,000.00 |
100000000000 | 1,00,00,00,00,000.00 |
Soon I realised that there is no single custom format available for handling all kind of number length and we need separate format for it depending upon the number of digits. Doing this manually and accuratly every time is very difficult. This leaves only one option to automate this through a macro. I got a Macro code for doing this and modified and tested that for long numbers.
I am sharing the macro code with you. You will need to select the numbers and run macro to format them as per India style comma sepration.
|
For easy application of this code you need to save it as a excel addin or add it to your personal macro book. Adding a custom toolbar for this code will make it easy to apply. You can chage the number of decimal places by changing the number of zeros at the end of custom number format given above.
Sunday, September 27, 2009
Excel Macros : Log User Activity to Log Sheet
We all store Excel workbooks on shared drives. While those files are accessed by many users but there is no track about who did what at various points of time. However this problem can be resolved with the help of VBA. A VBA programme with following capability can help us to get the log of user activity.
Following code has all these capabilities. Macro given below records user activity based on event information passed to it by another macro. You will need to copy this code to regular VBA module of your workbook
Following macros record events like
You will need to copy this code to worksheet module of your workbook
Since this code will record user activity only in case macros are enabled, do not forget to force users to enable macros while useing the particular file.
1. Add sheet named Log in case it is does not exist in the file.
2. Record user activity based on events to Log sheet.
3. Along with recording a event, it records user information and time of event.
4. Manage log size and removes old entries while creating space for new entries.
5. User can view the log but can not make changes to the log.
Following code has all these capabilities. Macro given below records user activity based on event information passed to it by another macro. You will need to copy this code to regular VBA module of your workbook
|
Following macros record events like
"Open" , "Save" and "Print"
and pass on the information to above macro to record user activity.You will need to copy this code to worksheet module of your workbook
|
Since this code will record user activity only in case macros are enabled, do not forget to force users to enable macros while useing the particular file.
Download file having these macros to record user activity to a log sheet. Check how it works
record user activity , track user activity, logging user activity, log user activity , monitor user activity
Tuesday, September 22, 2009
Excel Macros : Check Mark Symbols with Double Click
I am sharing with you very interesting trick to insert check mark symbols / to change check mark symbols with the double click in excel.
We can use Wingding font check boxes and change them with the double click with the help of macro
You can add as many of them by just defining the range in your macro. Values can be changed by just a double click.
You will further need to do data validation for that range so that user does not enter any other value by mistake.
You will need to copy this code to worksheet module of your workbook
|
Download file having trick to insert /change wingdings check mark symbols with double click
Make sure that you have enabled macros before using this file.
This trick can be used to choose any values you want change with the double click and is not limited only to check boxes in wingdings. See the attached file for Yes/No as another example.
Symbol Check Mark , Symbols Check Mark , Type Check Mark , Wingding Font Check Mark , Wingding Check Mark , Wingdings Check Mark , Check Mark Symbol , Check Mark Symbols , Check Symbol , Change values with double click in excel
Wednesday, September 16, 2009
Excel Macro : File open VBA
Before opening a excel file using VBA, you need to test particular file is open or not in excel. This is required as it can result into a error if particular file is already open.
You will need to copy this code to regular VBA module of your workbook
You need to go step by step to open the file
1. Check if excel file is open
2. Open that file using VBA , This should be done only in case you know the full path for the particular file
3. In case you do not know the full path , it is advisable to give excel file open dialog and let user open the file.
You will need to copy this code to regular VBA module of your workbook
Sub Open_File_after_IsOpen_or_Not() |
Download file having File open Macro with test if particular workbook is open or not
File open VBA , VBA open files , File Open Macro , Excel File open VBA , VBA Excel Workbook Open , VBA Excel Open Workbook , VBA open Workbook , Test particular file is open or not in excel , Check if file is open - Excel
Wednesday, September 9, 2009
Excel Macros : Remove Duplicates Excel
Excel 2007 has built in command to remove duplicates, however earlier versions of excel does not have built in command. You need to use VBA routine to do the job.
You will need to copy this code to regular VBA module of your workbook
Above works well in case you are trying to clean a data table on the sheet, however if you have lots of other data also on the sheet, you should try the Find and Remove duplicates within Range
You will need to copy this code to regular VBA module of your workbook
Use following code to Identify duplicate values in a column and Remove Rows having Duplicate Values
After identifying the duplicate values system will give you warning and ask for confirmation to delete the entire row(s) having duplicate values. You can choose No in case you do not want to delete them.You will need to copy this code to regular VBA module of your workbook
|
Download file having Macro to Find and Remove Duplicates in Excel.
Above works well in case you are trying to clean a data table on the sheet, however if you have lots of other data also on the sheet, you should try the Find and Remove duplicates within Range
You will need to copy this code to regular VBA module of your workbook
|
Download file having macro to find and remove duplicates within a range
Delete Duplicates , Delete Duplicate , Remove Duplicates , Remove Duplicate , Find Duplicates , Find Duplicate , Excel Delete Duplicates , Remove Duplicates Excel , Remove Duplicate Excel
Friday, September 4, 2009
Excel Macros : Find Last Row
Many times we need to find out the last row of excel sheet which is used by data. This has many uses such as to
Function GetLastRowWithData gives inaccurate results if you have active filters on the worksheet. That is why I have included code to remove active filters from the worksheet before using this function to get last row of data.
Download sample file to check how it works.
There is one more option which gives you satisfactory results
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Take care to remove filters before using above code
There are some more options to find last row however are not very reliable as they give you last row of the worksheet irrespective of fact that row could be a empty row.
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
or
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Add new column to the data only up to the last used row of exiting dateAdd new row to the data immediately after the last row so as you do not over write the exiting data.See the code below for the doing the activities as listed above.
Sub LastRowOfData() |
Function GetLastRowWithData gives inaccurate results if you have active filters on the worksheet. That is why I have included code to remove active filters from the worksheet before using this function to get last row of data.
Download sample file to check how it works.
There is one more option which gives you satisfactory results
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Take care to remove filters before using above code
There are some more options to find last row however are not very reliable as they give you last row of the worksheet irrespective of fact that row could be a empty row.
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
or
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Select Last Row , Find Last Row , VBA Last Row , VBA Find Last Row , Excel Last Row
Saturday, July 25, 2009
Hardware Locking for excel workbook with VBA
Many time we desire that our workbook is locked to work with a particular machine. This can be achieved through Hardware Locking of your workbook using VBA. Some of the avaiable solutions will require you to buy external harware dongles and will cost you good amount of money.
However if you can lock your workbook with the existing hardware of the machine , you will not need external hardware dongles.
if you use following Excel function to get Mother Board Serial Number, you can compare the current machines mother board serial number with the desired one. If you know that the desired serial number does not match with current machine you know that there is security voilation and you can close the workbook through VBA. You will need to copy this code to regular VBA module of your workbook
After you have placed above function in VBA module. You will need to copy this code to ThisWorkbook section of your workbook . This is autoopen macro which compares the MBSerialNumber of current machine with the desired MBSerialNumber. In case it does not mactch, it will close activeworkbook.
However you can down load Unlocked version of Hardware Locking for Excel Workbooks.
However if you can lock your workbook with the existing hardware of the machine , you will not need external hardware dongles.
if you use following Excel function to get Mother Board Serial Number, you can compare the current machines mother board serial number with the desired one. If you know that the desired serial number does not match with current machine you know that there is security voilation and you can close the workbook through VBA. You will need to copy this code to regular VBA module of your workbook
Excel Function to Get Mother Board Serial Number
| Public Function MBSerialNumber() As String Dim objs As Object Dim obj As Object Dim WMI As Object Dim sAns As String Set WMI = GetObject("WinMgmts:") Set objs = WMI.InstancesOf("Win32_BaseBoard") For Each obj In objs sAns = sAns & obj.SerialNumber If sAns < objs.Count Then sAns = sAns & "," Next MBSerialNumber = sAns End Function |
After you have placed above function in VBA module. You will need to copy this code to ThisWorkbook section of your workbook . This is autoopen macro which compares the MBSerialNumber of current machine with the desired MBSerialNumber. In case it does not mactch, it will close activeworkbook.
| Private Sub Workbook_Open() Set RMBSN = Sheets(1).Range("C4") ' This is whare you have already stored required MBSerialNumber If MBSerialNumber <> RMBSN Then ' Checking if current machine serial number is matching with required MsgBox ("Data Security failier, This workbook will close") ' In case it does not match workbook will be closed ActiveWorkbook.Save ActiveWorkbook.Close End If End Sub |
Download Excel file with macro for Hardware Locking for Excel Workbooks
and try to open it on your machine and see how it works. This is locked version and you will not be able to open it on your machine.However you can down load Unlocked version of Hardware Locking for Excel Workbooks.
Saturday, July 18, 2009
Excel Macros : VBA Expiry date for Excel Workbook
Many times we have a situation where we need to force users to use updated file and stop using the old version of worksheet. The only way to do so is to put expiry date for your workbook. User will not be able to work upon the file once the expiry date is crossed and will be forced to look for updated version.
You will need to copy this code to ThisWorkbook section of your workbook .
Once the expiry date is crossed, user will get message and workbook will be closed by macro
Once the expiry date in with next thirty days, user will get warning message about expiry date and number of days left.
You will need to copy this code to ThisWorkbook section of your workbook .
| Private Sub Workbook_Open() Dim Edate As Date Edate = Format("31/08/2009", "DD/MM/YYYY") ' Replace this with the date you want If Date > Edate Then MsgBox ("This worksheet was valid upto " & Format(Edate, "dd-mmm-yyyy") & " and will be closed") ActiveWorkbook.Close End If If Edate - Date < 30 Then MsgBox ("This worksheet expires on " & Format(Edate, "dd-mmm-yyyy") & " You have " & Edate - Date & " Days left ") End If End Sub |
Once the expiry date is crossed, user will get message and workbook will be closed by macro
Once the expiry date in with next thirty days, user will get warning message about expiry date and number of days left.
Download file containing Workbook Expiry Date Macro
Thursday, July 2, 2009
Excel Functions : Convert Numbers into Words
Many times we need the amount in figures to be converted into words. This is a typical requirement for writing checks or any other financial reports. Microsoft Excel does not have standard function available for this requirement. However there are customised functions available on the Internet. One such solution is available at Allexperts.com.
Function available at the net covers USD as currency, whereas I needed it in Indian Rupees. I have modified this to give results in any currency. The revised version gives me results as shown in the screen cast below.
Make sure that you enable macros to use this function. In case macros are disabled this function will not work in downloaded file
Syntex for the modified UDF is :-
SpellCurr(MyNumber, MyCurrency, MyCurrencyPlace, MyCurrencyDecimals, MyCurrencyDecimalsPlace)
where
MyNumber = Numeric Value you need to convert into words
MyCurrency = Name of your Currency - i.e. Dollar for USA
MyCurrencyPlace = Prefix or Suffix the currency, use "P" for Prefix and "S" for Suffix
MyCurrencyDecimals = Name of your Currency Decimals - i.e. Cent for USA
MyCurrencyDecimalsPlace = Prefix or Suffix the currency decimals, use "P" for Prefix and "S" for Suffix
Modified code given below for those who want to use it. Currency inputs are optional and you will not need to input currency details in case you are using it for Indian Currency. Still this can be used for any currency provided you give currency inputs.
You will need to copy this code to regular VBA module of your workbook
Display Numbers to Text.
You need to copy this to your regular macro module. Once you have added it to your file you can use function SpellNumbers to convert any number into words easily as you use any other function of excel.Function available at the net covers USD as currency, whereas I needed it in Indian Rupees. I have modified this to give results in any currency. The revised version gives me results as shown in the screen cast below.
Download excel file having this user defined function to convert numbers to words
Make sure that you enable macros to use this function. In case macros are disabled this function will not work in downloaded file
Syntex for the modified UDF is :-
SpellCurr(MyNumber, MyCurrency, MyCurrencyPlace, MyCurrencyDecimals, MyCurrencyDecimalsPlace)
where
MyNumber = Numeric Value you need to convert into words
MyCurrency = Name of your Currency - i.e. Dollar for USA
MyCurrencyPlace = Prefix or Suffix the currency, use "P" for Prefix and "S" for Suffix
MyCurrencyDecimals = Name of your Currency Decimals - i.e. Cent for USA
MyCurrencyDecimalsPlace = Prefix or Suffix the currency decimals, use "P" for Prefix and "S" for Suffix
Modified code given below for those who want to use it. Currency inputs are optional and you will not need to input currency details in case you are using it for Indian Currency. Still this can be used for any currency provided you give currency inputs.
You will need to copy this code to regular VBA module of your workbook
Function SpellCurr(ByVal MyNumber, _ |
Spell Currency Excel Addin avilable now
Numbers to Words , Convert Number to Words , Convert Number to Word , Number to Words , Number to Word , Number to Text , Number in Words , Number to Letters , Convert Number to Text , VBA Number to Text , Number to Text Function , Numeric to Text
Sunday, May 10, 2009
Macros in Excel : Selecting Multiple Ranges in Excel VBA
Selecting multiple ranges in Excel VBA helps your code to work faster. You can select multiple ranges in single line code and perform the action you want to perform.
Normally we code a range by writing it within "" as under
In the above macro we have selected range three different ranges
A1:A10
D1:D10
F1:F10
After selection we have copied the contents to Range A11. Another option for doing this was to copy the contents one by one for each of the range. However copy and paste is only one of the example you can use this for any desired action by you.
The following example creates a Range object called myMultipleRange, defines it as the ranges A1:B2 and C3:D4, and then formats the combined ranges as bold.
Normally we code a range by writing it within "" as under
Range("A1:A10").Select However if you put , between multiple ranges within the same line of the code you can select multiple ranges as given underRange("A1:A10,D1:D10,F1:F10").Select How to use Multiple Ranges in Excel VBA
Following macro code will explain you how to use multiple ranges in Excel VBA| Sub Multiple_ranges() Range("A1:A10").Select MsgBox ("Single Range Selected") Range("A1:A10,D1:D10,F1:F10").Select MsgBox ("Multiple Ranges Selected") Selection.Copy Range("A11").Select ActiveSheet.Paste End Sub |
In the above macro we have selected range three different ranges
A1:A10
D1:D10
F1:F10
After selection we have copied the contents to Range A11. Another option for doing this was to copy the contents one by one for each of the range. However copy and paste is only one of the example you can use this for any desired action by you.
You can combine multiple ranges into one Range object using the Union method.
The following example creates a Range object called myMultipleRange, defines it as the ranges A1:B2 and C3:D4, and then formats the combined ranges as bold.
|
Sunday, May 3, 2009
Macros in Excel : Forcing Users to Enable Macros
Many times we face issue of disable macros situation while the user is using our project. Since the macros are disabled, there is no way that the macros in your project will work the dessired way. However you can force your users to enable macros while working with your files. Simplest way is to hide the main sheet in your project using xlVeryHidden property in your macro. The workseets hidden by using this method can be made visible only by using VBA. The unhide workseet dialog box does not display the names of sheets hidden by using VBA with xlVeryHidden property.
You need to have one sheet which displays message that macros are disabled and needs to be enabled to use this file. Once the macros are enabled, the message sheets can be hidden and data sheets can be made visible. You can copy the following code to Thisworkbook section of your VBA project.
First macro code makes Msg Sheet visible and hides Data Sheet. This macros runs on even of closing the workbook. Second macro makes Data Sheet Visible and hides Msg Sheet. This macro runs on even of opening of workbook.
You can add following line to first macro to compulsoraly save the workbook before closure. However use this only if you are sure that it is okay to save the file before closing.
In case you have multiple sheets and you want to hide all the sheets except the Msg sheet, use following code in ThisWorkbook Module. Make sure that the Msg sheet is first sheet of your workbook. Following code will keep first sheet visible on close and Unhide all other sheets and hide first sheet on open.
Download file with Macro code to Force users to Enable Macros
Code above is simplest way to force normal users to enable macros and make your project file work the way you want them to. However the advanced users can always get into your project by using VBA routine stored in onother file. This can be done by following the steps as given below.
1. Open the project file while the Macros are disabled.
2. Encable the Macros.
3. Unhide the hidden sheets in project file by macros code in another file
There are ways and means to overcome this situation also, but a advanced user will find a way to get into your project if they realy want to. So I feel there is no need to complecate the code for keeping them away. You can always make normal users to use your project file the way you want them to by using simpe code given above
You need to have one sheet which displays message that macros are disabled and needs to be enabled to use this file. Once the macros are enabled, the message sheets can be hidden and data sheets can be made visible. You can copy the following code to Thisworkbook section of your VBA project.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Msg").Visible = True
Sheets("Data").Visible = xlVeryHidden
End Sub
Private Sub Workbook_Open()
Sheets("Data").Visible = True
Sheets("Msg").Visible = xlVeryHidden
End Sub
First macro code makes Msg Sheet visible and hides Data Sheet. This macros runs on even of closing the workbook. Second macro makes Data Sheet Visible and hides Msg Sheet. This macro runs on even of opening of workbook.
You can add following line to first macro to compulsoraly save the workbook before closure. However use this only if you are sure that it is okay to save the file before closing.
ActiveWorkbook.Save
In case you have multiple sheets and you want to hide all the sheets except the Msg sheet, use following code in ThisWorkbook Module. Make sure that the Msg sheet is first sheet of your workbook. Following code will keep first sheet visible on close and Unhide all other sheets and hide first sheet on open.
| Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim myCount 'This line of code is optional Dim i 'This line of code is optional On Error Resume Next myCount = Application.Sheets.Count Sheets(1).Visible = True Range("A1").Select For i = 2 To myCount Sheets(i).Visible = xlVeryHidden If i = myCount Then End If Next i ActiveWorkbook.Save End Sub Private Sub Workbook_Open() Dim myCount 'This line of code is optional Dim i 'This line of code is optional On Error Resume Next myCount = Application.Sheets.Count For i = 2 To myCount Sheets(i).Visible = True If i = myCount Then Sheets(1).Visible = xlVeryHidden End If Next i End Sub |
Download file with Macro code to Force users to Enable Macros
Code above is simplest way to force normal users to enable macros and make your project file work the way you want them to. However the advanced users can always get into your project by using VBA routine stored in onother file. This can be done by following the steps as given below.
1. Open the project file while the Macros are disabled.
2. Encable the Macros.
3. Unhide the hidden sheets in project file by macros code in another file
There are ways and means to overcome this situation also, but a advanced user will find a way to get into your project if they realy want to. So I feel there is no need to complecate the code for keeping them away. You can always make normal users to use your project file the way you want them to by using simpe code given above
Tuesday, April 28, 2009
Macros in Excel : Learn Excel VBA : Variables
This is continuation of my earlier posts on
It is not necessary to declare a variable in VBA, Visual Basic automatically creates storage for a variable on first use in the code. Automatically created variables are of type variant and can contain any type of data. It can be strings, boolean values, object , arrays or numbers.
Simple statement such as given below creates a variable and assigns value to it
Similarly you can use different variables in calculation to create another variable for your VBA code. Following code will explain the use of Variables in Excel VBA
This code explains that
1. Var_A is declared as constant number in the code itself.
2. Var_B is declared as value in ActiveCell i.e. Cell A4 in this case with Value as number 5.
3. We have used Var_A and Var_B to calculated Var_Total
4. We have used Var_Total to update the value in Cell A5, the result of this code is number 15 in Cell A5
Macros in Excel : Learn Excel VBA : Objects
Macros in Excel : Learn Excel VBA : Methods
Macros in Excel : Learn Excel VBA : Properties
It is not necessary to declare a variable in VBA, Visual Basic automatically creates storage for a variable on first use in the code. Automatically created variables are of type variant and can contain any type of data. It can be strings, boolean values, object , arrays or numbers.
Simple statement such as given below creates a variable and assigns value to it
Myvariable = 28
Similarly you can use different variables in calculation to create another variable for your VBA code. Following code will explain the use of Variables in Excel VBA
Sub Explain_Variables()
Var_A = 10
Range("A4").Select ' Value of Cell A4 is 5
Var_B = ActiveCell
Var_Total = Var_A + Var_B
Range("A5") = Var_Total
End Sub
This code explains that
1. Var_A is declared as constant number in the code itself.
2. Var_B is declared as value in ActiveCell i.e. Cell A4 in this case with Value as number 5.
3. We have used Var_A and Var_B to calculated Var_Total
4. We have used Var_Total to update the value in Cell A5, the result of this code is number 15 in Cell A5
Subscribe to:
Posts (Atom)
