[Excel 2007] Macro werkend 2003, geeft foutmelding in 2007

[Tudclub]

Hallo,

Ik zit een probleem wat ik graag bij jullie wil voorleggen. Het gaat om het volgende:

Via mijn studie heb ik een excelsheet (.xls bestand 97-2003) met ingebouwde macro gekregen. In de sheet kun je een aantal variabelen invoeren, met een 'knop' (waar de macro achter zit) gaat ie vervolgens berekeningen maken. In Excel 2003 werkt de macro nog gewoon (nog getest op een andere computer). Echter, in Excel 2007 krijg ik een foutmelding zodra ik met mijn muis over de 'knop' beweeg. Ik krijg de volgende melding:

Compileerfout: Lid bestaat al in een objectmodule waar van deze objectmodule is afgeleid

Wat ik heb gedaan:

• Beveiliging: "Alle macro's inschakelen (wordt niet aanbevolen omdat mogelijk schadelijke programmacode kan worden uitgevoerd)" + aangevinkt "Toegang tot het objectmodel van het VBA-project vertrouwen"
• Bestand opgeslagen als Excel 2007 (.xlsx) en Excelbestand 2007 met ingeschakelde macro's (.xlsm), beide zonder resultaat
• Verschillende forums nagezocht over dit probleem maar nog geen passende oplossing kunnen vinden

Ik krijg de volgende code te zien. Het gaat fout in rij 101 geeft ie aan in Microsoft Visual Basic.

code:

Option Explicit
Private Sub CommandButton1_Click()
Dim n As Long 'iteration
Dim m As Integer 'number of stocks
Dim rfr As Single 'risk-free rate
Dim sum As Single
Dim iRow As Long, iCol As Long
Dim i As Long, j As Long, k As Long
Dim index1 As Long, index2 As Long
n = Range("C3").Value
rfr = Range("C4").Value
iCol = 3
Do While Cells(6, iCol).Value <> ""
    iCol = iCol + 1
Loop
m = iCol - 3
ReDim w(n, m) As Single 'weights
ReDim r(m) As Single 'returns of stocks
ReDim r_portf(n) As Single 'returns of portffolios
ReDim Cov(m, m) As Single 'covariance matrix
ReDim SD_portf(n) As Single 'variances of portffolios
ReDim Sharpe(n) As Single ' Sharpe indices
ReDim Mtemp1(n) As Single, Mtemp2(n) As Single 'temporary matrices
For i = 1 To m
    r(i) = Cells(6, i + 2).Value
    For j = 1 To m
        If j >= i Then
            Cov(i, j) = Cells(8 + i, 2 + j).Value
        Else
            Cov(i, j) = Cells(8 + j, 2 + i).Value
        End If
    Next j
Next i
Randomize
For i = 1 To n
    sum = 0
    For j = 1 To m
        w(i, j) = Rnd
        sum = sum + w(i, j)
    Next j
    For j = 1 To m
        w(i, j) = w(i, j) / sum
        r_portf(i) = r_portf(i) + w(i, j) * r(j)
    Next j
    For j = 1 To m
        For k = 1 To m
            SD_portf(i) = SD_portf(i) + w(i, j) * w(i, k) * Cov(j, k)
        Next k
    Next j
    SD_portf(i) = Sqr(SD_portf(i))
    Mtemp1(i) = SD_portf(i)
    Sharpe(i) = (r_portf(i) - rfr) / SD_portf(i)
    Mtemp2(i) = Sharpe(i)
Next i
Range("C22:O23").ClearContents
Range("T:V").ClearContents
Range("T1").Value = "portffolio #"
Range("U1").Value = "SD"
Range("V1").Value = "Mean Return"
For iRow = 1 To n
    Cells(iRow + 1, 20).Value = iRow
    Cells(iRow + 1, 21).Value = SD_portf(iRow)
    Cells(iRow + 1, 22).Value = r_portf(iRow)
Next iRow
Range("Z2").Value = Range("D4").Value
'getting the number of the portfolio that has the minimum standard deviation of all portfolios
index1 = Sort(Mtemp1, n, 0) '(matrix, size, min-max indicator)
'getting the number of the portfolio that has the maximum Sharpe index value of all portfolios
index2 = Sort(Mtemp2, n, 1) '(matrix, size, min-max indicator)
For iCol = 1 To m
    Cells(22, iCol + 2).Value = w(index1, iCol)
    Cells(23, iCol + 2).Value = w(index2, iCol)
Next iCol
Range("M22").Value = r_portf(index1)
Range("M23").Value = r_portf(index2)
Range("N22").Value = SD_portf(index1)
Range("N23").Value = SD_portf(index2)
Range("O22").Value = Sharpe(index1)
Range("O23").Value = Sharpe(index2)
Range("X2").Value = 0
Range("Y2").Value = Range("C4").Value
Range("X3").Value = Range("N23").Value
Range("Y3").Value = Range("M23").Value
Range("X4").Value = Mtemp1(n)
Range("Y4").Value = Range("Y2").Value + (Range("Y3").Value - Range("Y2").Value) _
/ (Range("X3").Value - Range("X2").Value) * Mtemp1(n)
End Sub

Private Sub CommandButton2_Click()
Range("C3").Value = " "
Range("C4").Value = " "
Range("C6:L6").ClearContents
Range("C9:L18").ClearContents
Range("C22:L23").ClearContents
Range("T:V").ClearContents
Range("R1").Value = "portffolio #"
Range("S1").Value = "SD"
Range("T1").Value = "Mean Return"
End Sub

Private Function Sort(matrix() As Single, number As Long, indicator As Boolean)
'(matrix, size, min-max indicator)
'ascending as indicator = 0, decending as indicator = 1
Dim temp1 As Single, temp2 As Long
Dim i As Long, j As Long
ReDim index(number) As Long
For i = 1 To number
    index(i) = i
Next i
For i = 2 To number
    temp1 = matrix(i)
    temp2 = index(i)
    For j = i - 1 To 1 Step -1
        If indicator = 0 Then
            If matrix(j) <= temp1 Then
                Exit For
            End If
        Else
            If matrix(j) >= temp1 Then
                Exit For
            End If
        End If
        matrix(j + 1) = matrix(j)
        index(j + 1) = index(j)
    Next j
    matrix(j + 1) = temp1
    index(j + 1) = temp2
Next i
Sort = index(1)
End Function

Systeem:
- Windows XP Professional SP3
- Office 2007 SP2

Ik ben onbekend met VBA of het programmeren van macro's. Mogelijk dat het een simpel probleem is in de code maar ik kan dit hier niet uithalen. Ik hoop dat jullie met verder kunnen helpen. Mocht het noodzakelijk zijn, kan ik de excelsheet uploaden.

Alvast erg bedankt voor jullie hulp.

[F_J_K]

Voor wat het waard is: je wilt dit soort foutmeldingen altijd in het Engels (krijgen en) zoeken: [google=compile error - member already exists in an object module from which this object module is derived] werkt een stuk beter dan zoeken op de NL vertaling ervan

Maar goed: het probleem is waarschijnlijk dat je een gereserveerde term gebruikt. Sort is (sinds OFF2007) een object van een worksheet, ik neem aan dat het daar fout gaat. Hernoem Sort eens naar een term die waarschijnlijk niet standaard is, bijvoorbeeld MatrixSorteer. Dan natuurlijk ook overal aanpassen waar je de functie aanroept, zoals op regel 67.

offtopic:
Een Excel-bestand met macro zal altijd (.xls of) .xlxm en geen .xlsx moeten heten om te kunnen werken.

[Tudclub]

Dank voor de snelle reactie F_J_K!

Over de eerste tip: ik had inderdaad ook al in de Engelse versie van de foutmelding gezocht. Maar liep daar ook mee vast.

Over de twee tip: perfect! Ik heb de term aangepast van Sort naar MatrixSorteer op de plaatsen waar nodig en hij doet het! Ik ben blij dat het zo simpel was haha. Ontzettend bedankt voor de hulp, heel nuttig!

Topic kan gesloten worden wat mij betreft.

[F_J_K]

Nog welkom trouwens! Topics die zijn opgelost worden hier niet gesloten, sluiten gebeurt in principe alleen bij topics die niet aan de nettiquette voldoen zoals die hier geldt. En dat is hier dus niet nodig: het topic zakt vanzelf wel weg.