XL stuff

["Walter Lake" <wlake@xxxxxxxxx>]

Thanks for your emails

Look at the Fritch site for a very large XL workbook for TA. Lots of
excellent VBA code to adapt and ideas for changing of the Excel interface to
put your own package together.

http://home.earthlink.net/~jfritch/

ProUtilities for XL is a backtesting system for XL just starting out.

Also enclosed is some VBA code for over-writing formulas from another List.
The ideas should be helpful in solving your problem.

Best regards

Walter

=======================

"... This isn't super-technical but it gets around the age-old problem of
users wanting to type over formula cells.  We use the Capital Asset Pricing
Model for some stock valuations (I work at a stockbroker).  The user wanted
to be able to either put in the inputs to the CAPM formula and get the
discount rate OR just to type in any discount rate directly.  Typing in a
rate would, of course, overwrite the formula (unless we had a second cell
for user input - ugly).  The Worksheet_Change event got around this, as
below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Allows typeover of Discount rate formula; reenters formula when required.
'Sets comment text to reflect current status; changes font colour

Const Formula = "=RiskFreeRate+StockBeta*(MarketReturn-RiskFreeRate)"

    If ActiveCell.Column = 7 Then
        If ActiveCell.Row >= 7 And ActiveCell.Row <= 9 Then
            With Range("DiscountRate")
                .FormulaR1C1 = Formula
                .Font.ColorIndex = 10 'Green
                With .Comment
                    .Text Text:="Discount Rate is now per CAPM."
                    .Visible = False
                End With
            End With
        ElseIf ActiveCell.Row = 10 And ActiveCell.Column = 7 Then
            With ActiveCell
                .Font.ColorIndex = 6 'Yellow
                With .Comment
                    .Text Text:="Discount Rate is now user-defined."
                    .Visible = True
                    With .Shape
                        .Width = 150
                        .Height = 12
                    End With
                End With
            End With
            Cells(10, 7).Activate
        End If
    End If
End Sub

Whenever he reinputs a component of the CAPM rate the CAPM formula is
reentered for him.  I thought this was kinda neat, and the guy was
impressed.  It won't set the programming world on fire but it deals with a
common user/Excel interaction - IMNSHO nicely, I thought. ..."