Stallone
01-31-2019, 03:21 AM
Hi, I have a really simple problem, but just ca not get the solution in VBA. I want to calculate the return for a calendar year and determine a function for it, so do not select the range directly in VBA. I
n column A the time series is Date, in column B the values of the investment.
Date Series contains only working days. End of the month and end of the year deviates from the last calendar day.
In Excel this would be feasible with
SV1 = VLOOKUP (DATE (2018, 12 + 1, 0); A2:B1046, 2)
SV2 = VLOOKUP (DATE (2017; 12 + 1; 0); A2: B1046; 2)
Return 2018 = SV1 / SV2 -1
Problem in VBA is that there does not seem to be the date function. My idea was the following code, but that does not work. Anyone have an idea how to choose the last day of a month from a range?
Function Return2018 (DateValues As Range)
Dim This3112 As Double
Dim Last3112 As Double
This3112 = Application.WorksheetFunction.VLookup(Application.WorksheetFunction.Date(20 18, 12 + 1, 0), DateValues, 2)
Last3112 = Application.WorksheetFunction.Vlookup(Application.WorksheetFunction.Date(20 17, 12 + 1, 0), DateValues, 2)
Return2018 = This3112 / Last3112 - 1
End Function
n column A the time series is Date, in column B the values of the investment.
Date Series contains only working days. End of the month and end of the year deviates from the last calendar day.
In Excel this would be feasible with
SV1 = VLOOKUP (DATE (2018, 12 + 1, 0); A2:B1046, 2)
SV2 = VLOOKUP (DATE (2017; 12 + 1; 0); A2: B1046; 2)
Return 2018 = SV1 / SV2 -1
Problem in VBA is that there does not seem to be the date function. My idea was the following code, but that does not work. Anyone have an idea how to choose the last day of a month from a range?
Function Return2018 (DateValues As Range)
Dim This3112 As Double
Dim Last3112 As Double
This3112 = Application.WorksheetFunction.VLookup(Application.WorksheetFunction.Date(20 18, 12 + 1, 0), DateValues, 2)
Last3112 = Application.WorksheetFunction.Vlookup(Application.WorksheetFunction.Date(20 17, 12 + 1, 0), DateValues, 2)
Return2018 = This3112 / Last3112 - 1
End Function