Excel Duplicate Finder

After experiencing issues with some of my spreadsheets being rejected upon database importing due to duplicates, i decided to create a macro to make the solution simpler. The macro that i created, is contained within the excel file "excel_dupes_range_Value.xls" and can be easily applied to any spreadsheet you are working on.

Instructions:

1. Simply download this file, and open it in excel. Make sure to allow macros in the security dialog. In order to reduce confusion, i hid this workbook, so don't be alarmed when nothing new pops open - you will be able to tell if it is open in the next step.

2. The macro searches for duplicates only within the selection of cells. Select a group of cells (THIS IS CURRENTLY LIMITED TO DATA WITHIN THE SAME COLUMN).

3. In the toolbar go to Tools>Macro>Macros and you should see the "excel_dupes_range_Value.xls!findDupes" macro in the list of open workbooks. Select this and click "Run" -see example-

4. Once the macro is run, a new window will open up which displays all of the duplicate values. By double clicking on any value in the list, you will be taken to that value in the selection of data that you ran the macro on. If you click on the same value twice consecutively, it will take you to the second duplicate and so on. -see example-

Here is the duplicate findinding spreadsheet

-excel_dupes_range_Value.xls-

---
The macro works by identifying the selection's range, copying the data within the range into th macro spreadsheet, sorting the data, and then looking for two identical values in sequence. A loop works through one cell at a time comparing the present and previous value. The loop runs until it reaches the end of the range selected by the user's selection. Once duplicates are found, the values are added to a listbox, and then the search is performed based on that value in the originating spreadsheet.

Excel Macro Confirm MSG Box

I recently realized a danger in keyboard-shortcut driven macros. Excel allows you to tie a macro to common shortcuts (like CTRL-C, CTRL-B) and if pressed accidentally, changes made by the macro can not be undone. As a result i came up with this simple bit of code to force confirmation before the macro is run, through a MSG Box.



Simply place this at the beginning of each of your macros:
----

ans = MsgBox("Run Macro?", vbYesNo)
If ans = vbNo Then
Exit Sub
End If

----
If the user says "NO" the macro sub will exit, and if yes, the code below this section will be run. An easy way to protect users from accidentally misusing your macros.

VB6 Roulette

For fun, i created a Roulette game using VB6. It is pretty simple, but gets the job done. I figure i might as well post it, so i have included both the exe and the script. I also made a variation of the program which would allow you to run a specific bet X number of times, but if you like gambling, i suggest you stay away from that one. :)
MyRoulette.exe

MyRoulette_tester.exe

MyRoulette_Source.zip

Excel Simple Loan Calculator

Below is a simple (yet complete) Excel loan calculator. I developed this spreadsheet for auto loans, and focus was placed on the payment calculation. I added a VB6 macro which will put you in control of Excel's Goal Seek function, to find target values for the various variables involved.

-Loan_Pmt.xls-

Excel Back Button

This Excel macro acts like the back button in your internet browser. This is a sample, but you should be able to implement this code into your worksheets. To do this, just copy the code from the worksheet and module into your own worksheets, and then assign the macro to a button in your toolbar.

The idea is this: any time a new sheet is activated within a workbook, a subroutine records the previous worksheet name and the new worksheet name into respecive variables. (wkshtBack and wkshtCurrent). This is done by adding code to the worksheet object in the VB Editor (tools>macro>VBEditor), under the Workbook_SheetActivate event. Once the current and previous sheets are known, the macro simply tells excel to move between the two. The code is shown below:

Workbook object:
Private Sub Workbook_Open()

wkshtCurrent = ActiveSheet.Name
wkshtBack = ActiveSheet.Name

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

wkshtBack = wkshtCurrent
wkshtCurrent = ActiveSheet.Name
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
End Sub


BackMacro:Module:

Global wkshtCurrent As String, wkshtVar As String, wkshtBack As String

Sub backmacro()
wkshtVar = wkshtCurrent
Sheets(wkshtBack).Select
wkshtBack = wkshtVar
End Sub



-ExcelBack Macro-

Excel 4 Function Calculator (VB6)

Working with spreadsheets a lot, i really hated the fact that it was sometimes necessary to use a seperate calculator to make quick calculations. The Windows calculator offered some help, however i still wanted more. I created a VB6 4 function calculator to address my problems and am now sharing it with you. In addition to all expected functions, the calculator allows users to import numbers from cells, export numbers to cells, and adjust numbers in excel by adding or subtracting directly to the selected cell. This is all in a neat Excel-VB6 based form which can easily be launched from within any spreadsheet. I have found this to be a great time saver and hope you do too.

-Excel Calc (Sample XLS)-

-Excel Calc (Form Only)-

Excel Bowling Score Calculator

Here is an excel calculator which i created in order to compute your bowling score, frame by frame.

-Bowling Calc-

Note: thanks to those who helped me find some bugs on this. I updated the sheet and reposted.

Update: I have modified the spreadsheet so that now all of the formulas are contained in the calculating cell, instead of hidden rows as it was before. If one wanted to make a copy of the calculator to track multiple games, people, etc., All that they need to do is highlight the score box and Copy and Paste. New version is _v2