VBA Investment Returns - How to find last day of a month?

[Stallone]

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(2018, 12 + 1, 0), DateValues, 2) 
Last3112 = Application.WorksheetFunction.Vlookup(Application.WorksheetFunction.Date(2017, 12 + 1, 0), DateValues, 2) 
Return2018 = This3112 / Last3112 - 1 
End Function

[Paul_Hossler]

1. Welcome to the forums

2. Take a minute to read the FAQs and some other suggestions in my sig

3. I added CODE tags to your post -- you can use the [#] icon to enter them and just paste your macro between to set it off and to format it nicely

4. Try this

Option Explicit


Function Return2018(DateValues As Range, TheYear As Long)
    Dim This3112 As Double
    Dim Last3112 As Double
    
    With Application.WorksheetFunction
        This3112 = .VLookup(CLng(DateSerial(TheYear, 12 + 1, 0)), DateValues, 2)
        Last3112 = .VLookup(CLng(DateSerial(TheYear - 1, 12 + 1, 0)), DateValues, 2)
    
        Return2018 = This3112 / Last3112
    End With
End Function



Sub test()
    MsgBox Return2018(ActiveSheet.Range("A1:B788"), 2018)
End Sub

[Stallone]

Hi Paul,

awesome, thats exactly the Piece of elegant solution I was looing for, many thanks!