bug in excel

amateur1902 · 4 mrt 2008

Hallo,

Ik zit met een probleem. Heb een vba programma geschreven ,met veel hulp van anderen, voor excel. Nu is het probleem op mijn computer werkt hij geweldig maar op een andere computer komt er een bug tevoor schijn. Weet iemand misschien waar dit aan kan liggen en wat ik er aan kan doen. De bug komt te voorschijn in het dik gedrukte gedeelte. Denk zelf dat het iets te maken heeft met het gebruik van punten en komma's maar ik kom er maar niet achter.

Alvast bedankt voor jullie hulp

Code:

Private Sub CommandButton1_Click()

    Set objDataControl = New BlpData
    Call objDataControl.Flush
    
     'Script weergave uit (niet zichtbaar voor gebruiker)
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
     
     '### START
    sngStart = Timer
         
    'Legen van cellen in Excel
    Dim rng As Range
    Set rng = Range("D18:R1000")
    Range(rng, rng).ClearContents
    
    'Opzetten van velden voor array
    arrayFields = Array("PX_LAST")
     
     ' Tickers tellen
    nr_comp = Range(Range("B18"), Range("B18").End(xlDown)).Rows.Count
    
    'Bepaald grote van array
    Dim arraySecurities() As String
    ReDim arraySecurities(nr_comp)
    
    'Leading Fund
    arraySecurities(0) = Range("B10").Value
   ' Range("D18").FormulaR1C1 = "=R[-8]C[-2]"
    
    'Peers (per peer wordt data binnen gehaald)
    With Range("B18")
        i = 1
        Do While i <= nr_comp
            arraySecurities(i) = .Cells(i, 1).Value
            i = i + 1
        Loop
    End With
     
     ' Berekening op basis van dagelijkse koersen
    objDataControl.Periodicity = bbDaily
     
     ' Bepaling data (data uit welke periode)
    startd = Range("D4").Value
    endd = Range("D5").Value
    
    'plaatsen data in excel
    Range("L15").ClearContents
    Range("N15").ClearContents
    
    Range("D5").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Range("N15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("D4").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Range("L15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    
    'Instellen datum
    Range("D4").Select
    ActiveCell.FormulaR1C1 = _
        "=DATE(  YEAR(  NOW()  )-R[1]C[3],   MONTH(  NOW()  )-RC[3],  DAY(  NOW()  )-R[-1]C[3]   )"
    
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
     
     '#### Bloomberg Connection ####
    objDataControl.GetHistoricalData arraySecurities, 1, arrayFields, _
    CDate(startd), _
    CDate(endd), _
    Results:=vtResult


    
     'Tel het aantal data
    nr_of_dates = UBound(vtResult)
     
     'Maak van multi dimentional array een single array (onafhankelijk)
    Dim arr_Id() As Variant
    ReDim arr_Id(nr_of_dates)
    For z = 0 To nr_of_dates
        arr_Id(z) = vtResult(z, 0, 1)
    Next
    
     'Opstellen array voor afhankelijke fondsen
    Dim arr_Dp() As Variant
    ReDim arr_Dp(nr_of_dates)
    
    'Berekening van correlatie
    Dim arrayCorrel() As Variant
    ReDim arrayCorrel(nr_comp)
    
    For a = 0 To nr_comp
        For b = 0 To nr_of_dates
            arr_Dp(b) = vtResult(b, a, 1)
        Next
         
        arrayCorrel(a) = Application.Correl(arr_Id, arr_Dp)
          
        ReDim arr_Dp(nr_of_dates) As Variant
    
    Next
     
     'Filter voor correlaties, niet groter dan 1 of kleiner dan ingestelde parameter
    nrCompz = UBound(arraySecurities)
    corr = Range("D8").Value
    For k = 1 To nrCompz
[B]        If arrayCorrel(k) <> "1" And arrayCorrel(k) > corr Then
            Range("D18").Offset(k, 0).Value = arraySecurities(k)
            Range("H18").Offset(k, 0).Value = arrayCorrel(k)[/B]
        End If
    Next k

     'Correlatiecoëfficiënt sorteren en invoegen in Excel
    Set rng = Range("D19")
    Range(rng, Range("H10000")).Sort Key1:=Range("H19"), Order1:=xlDescending, _
    Header:=xlNo, _
    OrderCustom:=1, _
    MatchCase:=False, _
    Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

'Vul formules in Excel

Range("D18").Select
ActiveCell.FormulaR1C1 = "=R[-8]C[-2]"
Range("D18").Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
    

    Range("H18").Select
    ActiveCell.FormulaR1C1 = "-"
    

nr_corr = Range(Range("D18"), Range("D18").End(xlDown)).Rows.Count + 17

Range("F18:F" & nr_corr).FormulaR1C1 = "=Proper(BDP(RC[-2]&"" equity"",""name""))"

Range("N18:N" & nr_corr).FormulaR1C1 = "=BDP(RC[-10]&"" equity"",""VOLUME_AVG_30D"")"

Range("P18:P" & nr_corr).FormulaR1C1 = "=BDP(RC[-12]&"" equity"",""last price"")"

Range("R18:R" & nr_corr).FormulaR1C1 = "=(RC[-2]/BDP(RC[-14]&"" equity"",""PX_CLOSE_1D""))-1"

Range("J19:J" & nr_corr).FormulaR1C1 = "=SUMPRODUCT((Data!R2C1:R2000C1=Interface!R18C6)*(Data!R2C3:R2000C3=Interface!R[0]C6)*(Data!R2C4:R2000C4=Data!R1C8)*(Data!R2C6:R2000C6))/SUMPRODUCT((Data!R2C1:R2000C1=Interface!R18C6)*(Data!R2C3:R2000C3=Interface!R[0]C6)*(Data!R2C4:R2000C4=Data!R1C8))"
    
Range("L19:L" & nr_corr).FormulaR1C1 = "=SUMPRODUCT((Data!R2C1:R2000C1=Interface!R18C6)*(Data!R2C3:R2000C3=Interface!R[0]C6)*(Data!R2C4:R2000C4=Data!R1C8)*(Data!R2C7:R2000C7))/SUMPRODUCT((Data!R2C1:R2000C1=Interface!R18C6)*(Data!R2C3:R2000C3=Interface!R[0]C6)*(Data!R2C4:R2000C4=Data!R1C8))"


     
     'Filteren benamingen
    Range("D18:D1149").Replace What:=" Equity", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("D18:D1149").Replace What:="Equity", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("D18:D1149").Replace What:=" EQUITY", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("D18:D1149").Replace What:="EQUITY", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("D18:D1149").Replace What:=" equity", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("D18:D1149").Replace What:="equity", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("D18:D1149").Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("D18:D1149").Replace What:="   ", Replacement:=" ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("D18:D1149").Replace What:="    ", Replacement:=" ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("D18:D1149").Replace What:="     ", Replacement:=" ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("D18:D1149").Replace What:="      ", Replacement:=" ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("D18:D1149").Replace What:="       ", Replacement:=" ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


        
'##########
            
'default variabelen tijdsduur

Range("G3").Select
ActiveCell.FormulaR1C1 = "0"

Range("G4").Select
ActiveCell.FormulaR1C1 = "0"
    
Range("G5").Select
ActiveCell.FormulaR1C1 = "2"

Range("A1").Select

'Default variabel correlatie
'Range("D8").Select
'ActiveCell.FormulaR1C1 = "0.60"


'recalculate
    With Application
        .Calculation = xlAutomatic
    End With
    
'replace
    Range("J18:J1000").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("L18:L1000").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Application.CutCopyMode = False
    
    Range("J18:J1000").Replace What:="#DIV/0!", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    
    Range("L18:L1000").Replace What:="#DIV/0!", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
        
        


    Range("A1").Select

     
     '### Stop Timer
    sngEnd = Timer
    sngElapsed = Format(sngEnd - sngStart, "Fixed")
            
     'Tijdsmelding aantal seconden proces snelheid
     Range("F8").Value = sngElapsed & " seconden"
     
    With Application
         
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
End Sub

Guus2005 · 17 mrt 2008

Hoe bedoel je "er komt een bug tevoorschijn"? Als je een foutmelding bedoelt: wat is het nummer en omschrijving van die foutmelding?

bug in excel

amateur1902

Gebruiker

Guus2005

Terugkerende gebruiker

Nieuwste berichten

Wij waarderen jouw privacy