Posts by joelsmalley

  • Sort an array within CB code

    If anyone else is interested, this does what I wanted:


    Function SelectionSort(TempArray As Variant)
    Dim MaxVal As Variant
    Dim MaxIndex As Integer
    Dim i, j As Integer


    ' Step through the elements in the array starting with the
    ' last element in the array.
    For i = UBound(TempArray) To 1 Step -1


    ' Set MaxVal to the element in the array and save the
    ' index of this element as MaxIndex.
    MaxVal = TempArray(i)
    MaxIndex = i


    ' Loop through the remaining elements to see if any is
    ' larger than MaxVal. If it is then set this element
    ' to be the new MaxVal.
    For j = 0 To i
    If TempArray(j)(1) > MaxVal(1) Then
    MaxVal = TempArray(j)
    MaxIndex = j
    End If
    Next j


    ' If the index of the largest element is not i, then
    ' exchange this element with element i.
    If MaxIndex < i Then
    TempArray(MaxIndex) = TempArray(i)
    TempArray(i) = MaxVal
    End If
    Next i


    End Function


    Sub SelectionSortMyArray()
    Dim TheArray(3)
    ' Create the array.
    TheArray(0) = Array("Zero", 0)
    TheArray(1) = Array("Three", 3)
    TheArray(2) = Array("One", 1)
    TheArray(3) = Array("Two", 2)


    ' Sort the Array and display the values in order.
    SelectionSort TheArray

    End Sub


    regards, Joel

  • Sort an array within CB code

    I have created an array in VB code below:


    Sub tempMD()


    Dim ClientDNBuy(30)
    Dim nCount As Integer


    For Each MDClient In Range("Clients")


    MDColumnOffset = Application.WorksheetFunction.Match(MDClient, Range("BlotterClients"), 0)
    MDBuy = Range("Market_Price_Anchor").Offset(MDRowOffset + 1, MDColumnOffset - 1)
    MDStockRef = Range("Market_Price_Anchor").Offset(MDRowOffset + 1, MDColumnOffset + 1)
    MDDelta = Range("Market_Price_Anchor").Offset(MDRowOffset + 1, MDColumnOffset + 2)
    MDSwapRef = Range("Market_Price_Anchor").Offset(MDRowOffset + 1, MDColumnOffset + 3)
    MDOR = Range("Market_Price_Anchor").Offset(MDRowOffset + 1, MDColumnOffset + 4)
    MDEquityDN = ((TouchStock - MDStockRef) * MDConvRatio / MDParValue * MDParQuote * MDDelta)
    MDBondDN = (MDLiveSwap - MDSwapRef) * MDRhoPhi * 100


    ClientDNBuy(nCount) = Array(MDClient, MDBuy + MDEquityDN + MDBondDN)
    nCount = nCount + 1


    Next


    End Sub



    This creates an array 30 rows X 2 columns with client name next to a value. Is it possible to sort this array as if it were a range in the spreadsheet, without pasting it into the spreadsheet and sorting!


    Joel

  • EnterKeyBehaviour in userform

    In my userform, my textboxes are all set to Multiline False and EnterKeyBehaviour False yet pressing Enter does not tab throught the tab order - it has no effect at all, despite what it says in help section:


    "False Pressing ENTER moves the focus to the next object in the tab order (default).



    Remarks


    The EnterKeyBehavior and MultiLine properties are closely related. The values described above only apply if MultiLine is True. If MultiLine is False, pressing ENTER always moves the focus to the next control in the tab order regardless of the value of EnterKeyBehavior."


    I have tried other permutations of Multiline and EnterKeyBehaviour to no avail. Anybody any ideas please?

  • activecell

    The variable "RangeName" will have the property of your ActiveCell if it is named.


    Sub ActiveCellRangeName()
    Dim RangeName
    Set CellNames = ActiveWorkbook.Names
    For n = 1 To CellNames.Count
    If ActiveCell.Address = Names(n).RefersToRange.Address Then
    RangeName = CellNames(n).name
    End If
    Next


    End Sub


    Regards, Joel

  • Moving rows

    Sounds like you have "Move selection after Enter" checked in Tools/Options/Edit. I have mine unchecked. If you want to keep it checked, modify my code to trigger from activecell minus one row!

  • E-Mail

    This code in module will send your workbook to recipients whose email addresses are in range A1:A2 of the current sheet and whose subject is in B1:


    Sub sendmail()
    Dim Recipients
    Dim Subject
    Subject = Range("B1")
    Recipients = Range("A1:A2")
    ActiveWorkbook.sendmail Recipients:=Recipients, Subject:=Subject
    End Sub



    To determine who gets what, make the email addresses conditional on the content.


    You have to be more specific about what data you want moved about for the other part of your question...


    Joel

  • Moving rows

    try this instead:


    Private Sub Worksheet_Change(ByVal Target As Range)


    Dim r1 As Long


    If Sheets("Sheet3").Range("A1") <> "" And Sheets("Sheet3").Range("a2") = "" Then
    r1 = 1
    Else
    r1 = Sheets("Sheet3").Cells(1, 1).End(xlDown).Row
    If Sheets("Sheet3").Range("A1") = "" Then r1 = 0
    End If
    If ActiveCell.Column = 4 And ActiveCell.Row &gt; 1 And ActiveCell.Value <> 0 Then
    r1 = r1 + 1
    ActiveCell.EntireRow.Copy Sheets("Sheet3").Cells(r1, 1)


    End If
    End Sub



    I assume your date is in column "D", other wise change activecell.column number accordingly.

  • Moving rows

    This code (when pasted into sheet VB) will copy a row when data is changed in column 5 ("E") and paste it in Sheet3, position A1:


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Column = 5 Then
    ActiveCell.EntireRow.Copy Sheets("Sheet3").Range("A1")
    End If
    End Sub


    Joel

  • Calculating Bond Price using Excel

    Trial and error and the help files is all I ever had till I found this forum... Once you have found a function that works for you, look at related functions in the help files and see what else you can do. I've not discovered a difficiency in Excel yet...

  • Calculating Bond Price using Excel

    No problem. Ensure you have the Analysis Tool Pack added in.


    Other useful functions:


    ACCRINT - calculates accrued interest
    COUPDAYBS, COUPDAYS, COUPNCD, COUPPCD - coupon dates and days
    DURATION - duration
    MDURATION - modified duration
    ODDFPRICE, ODDLPRICE - if you have short/long first or last coupon period

  • Calculating Bond Price using Excel

    use PRICE function:


    PRICE(TODAY(),EDATE(TODAY(), 36),3%,4%,100,2,0)


    This is a bond which matures in 3 years, with a semi annual, 30/360 coupon of 3% and 4% yield (formula result 97.19928 looks intuitively correct).


    Also use YIELD() to find yield if you know the price. Use help on these functions for extra info on day count basis etc...


    Joel