0

My user-defined formula as shown below is working properly in a Function environment as long as this is embedded into a module within the workbook I am primarily using it. However, when I start to put it into an .xlam (AddIn) it is not working anymore. In the code below I indicated the line where it starts to crash. I suspect it is related to either .ThisCell or .Find, but was not able to isolate the issue. Any ideas?

Public Function zinsfuss(cashflows, dates) As Variant
'-----------------------------------------------------------------
Application.Calculation = xlCalculationAutomatic
Application.Volatile True
'-----------------------------------------------------------------
Dim arDates() As Variant
    If dates.Rows.Count > 1 Then
        arDates = Application.Transpose(dates)
        Else:
        ReDim arDates(1 To 1)
        arDates(1) = CDbl(dates)
    End If
    ReDim Preserve arDates(1 To UBound(arDates) + 1)
    arDates(UBound(arDates)) = CDbl(Date)
'-----------------------------------------------------------------
Dim arCF() As Variant
    If dates.Rows.Count > 1 Then
        arCF = Application.Transpose(cashflows)
        Else:
        ReDim arCF(1 To 1)
        arCF(1) = cashflows
    End If
    ReDim Preserve arCF(1 To UBound(arCF) + 1)
'starting with the next line the code stumbles.
    arCF(UBound(arCF)) = ThisWorkbook.Sheets(Application.ThisCell.Parent.Name).Cells.Find("Kurswert in Fondswährung", LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Value
'-----------------------------------------------------------------
    zinsfuss = WorksheetFunction.Xirr(arCF, arDates)
'-----------------------------------------------------------------
End Function
ezvk
  • 1
  • 3
  • 1
    You can pass in a cell on the worksheet as a parameter, and then use its `.Parent` to get a reference to the worksheet. Or maybe `Application.ThisCell.Parent` – BigBen Jul 14 '22 at 15:18
  • This seems to do the trick. Thank you. – ezvk Jul 14 '22 at 15:32
  • @BigBen Sorry for reaching out that directly to you. But may I kindly ask if you you have an idea on my edited problem (see above)? – ezvk Aug 11 '22 at 12:27
  • If you're using it in an .xlam, then `ThisWorkbook` refers to the .xlam file. You can get a reference to the parent worksheet just with `Application.ThisCell.Worksheet` and use that directly. – BigBen Aug 11 '22 at 12:43

0 Answers0