Sunday, May 4, 2008

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.


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


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

Sub backmacro()
wkshtVar = wkshtCurrent
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)-

Friday, May 2, 2008

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