I need to fix my VBA code because it is giving me a time and I need a date

Question

0.00/5 (No votes)

See more:

The program shown below is a VBA excel program designed to calculate an approximate retirement date for an employee database. I was given the valuation date = Hoy, the Age of each employee in months = EdadMeses, and the number of fees each employee has Paid = Coutas. Starting at age 55 and 1 month (660 months) an employee can retire if and only if they fulfill the number of fees required which are 395 when the employee turns 661 months the fee requirement is now 394, and so on. Assuming that each upcoming month every employee will pay one fee I want to calculate the earliest retirement date for each employee. My questions are: When I run the program the answer it gives me is a time. How can I fix the coding so that the macro gives me a DATE. I used the formula DateAdd("m", (number of fees requirements) - (number of fees that the employee has), valuation date). However, If anyone has a better way to do it or has an understanding of why the formula is giving me a time it would be really helpful.

What I have tried:

VB

Sub If_And_Else()

Dim EdadMeses As String
Dim Cuotas As String

Dim Jubilacion As Date
Dim FechaAprox As Date

EdadMeses = Range("E2").Value
Cuotas = Range("B2").Value

IntervalType = "m"
Hoy = #6/28/2022#
Restante = Coutas
Jubilacion = Range("G2")


If EdadMeses > 779 Then
Jubilacion = Hoy
ElseIf EdadMeses = 660 And Cuotas <= 395 Then
Jubilacion = DateAdd("m", 395 - Restante, Hoy)

ElseIf EdadMeses = 661 And Cuotas <= 394 Then
Jubilacion = DateAdd("m", 394 - Restante, Hoy)

ElseIf EdadMeses = 662 And Cuotas <= 393 Then
Jubilacion = DateAdd("m", 393 - Restante, Hoy)

ElseIf EdadMeses = 663 And Cuotas <= 392 Then
Jubilacion = DateAdd("m", 392 - Restante, Hoy)

ElseIf EdadMeses = 664 And Cuotas <= 391 Then
Jubilacion = DateAdd("m", 391 - Restante, Hoy)

End If

End Sub

Posted 28-Jun-22 18:37pm

fabiola Risi

Updated 29-Jun-22 4:37am

PIEBALDconsult

v2

Add a Solution

Comments

Richard MacCutchan 29-Jun-22 3:18am

What do you mean by, the answer it gives me is a time?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-06-29T04:37:00

Your code does not produce a "time" for me, it produces a Date. Therefore the problem is likely to be either with your data or your environment.

I can get it to output an integer if I put Jubilacion into a cell that is formatted as General or I can get it to display as a time if I change the format of the cell to Time. I can also get it as a Time if the Range G2 contains a time and E2 < 779 and B2 > 391

Impossible to tell what you are doing as your code discards the results and does nothing with them. I can only suggest that you use the debugging tools available (e.g. Watch window, Immediate window) to work out what is going wrong.

Other things that may be causing you problems/you could be tidier with:-

1. See this code

VB

Restante = Coutas

I suspect that is meant to match the variable you have declared and should read

VB

Restante = Cuotas

You can avoid runtime errors like that by always using

VB

Option Explicit

at the start of your modules. You can get the IDE to do this by default by ensuring Tools, Options, Editor, Require Variable Declaration is ticked.

2. You have not declared the variables IntervalType, Hoy, or Restante meaning they are all of type Variant. You have declared Cuotas as a String when it is quite clearly meant to be an Integer. VBA is quite forgiving but I have found through bitter experience that it is best to explicitly state which type (and make sure it is the correct type!) throughout any calculations, and not to assume defaults either.

3. Your hard-coded value Hoy = #6/28/2022# assumes the date is in US format - it is better practice to use less ambiguous date formats e.g. use

VB

Hoy = #2022-06-28#

Incidentally, and this is probably no more than personal preference, I dislike the use of # to define dates in VBA and would use

VB

Hoy = CDate("2022-06-28")

or

VB

Hoy = DateSerial(Year:=2022, Month:=6, Day:=28)

or

VB

Hoy = DateValue("2022-06-28")

4. You create a variable IntervalType, assign it a value of "m" but then don't use it in any of your calculations. Code Tidily and many potential errors simply go away!