PDA

View Full Version : [SOLVED] VBA Investment Returns - How to find last day of a month?



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

Paul_Hossler
01-31-2019, 08:08 AM
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
02-01-2019, 12:06 AM
Hi Paul,

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