Date Increment

I am troubleshooting another person's code for an Excel spreadsheet.  The code for selecting a year on a drop down box is as follows:


Sub UBOX()
    cyr = Year(Date - 43)
    cmo = Month(Date - 43)
    Application.ScreenUpdating = True
    On Error Resume Next
    With FWcap
        .bxcyr.Clear
        For count1 = cyr To 1994 Step -1
            .bxcyr.AddItem count1
        Next
        .bxcmo.Clear
        For count1 = 12 To 1 Step -1
            .bxcmo.AddItem count1
        Next
        .bxcyr.Text = cyr
        .bxcmo.Text = cmo
    End With
    With FWcap
        .Show
        If FWcap.Tag = vbCancel Then
           Application.StatusBar = False
           Exit Sub
        End If
        cyr = .bxcyr.Value
        cmo = .bxcmo.Value
    End With
    On Error GoTo 0
    UPDATE cmo, cyr
End Sub

It starts at 1994 and only goes to 1999.  I wanted it to go to at least the year 2000.  What is going on that it is not also making other years above 1999 available?
kristincaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mark2150Commented:
You're subtracting "43" from the current date to derive cyr. Since the current year is less than 43 days old, this pushes the date back into last year.

M
0
caraf_gCommented:
Right.

What's this code doing?
    cyr = Year(Date - 43)
    cmo = Month(Date - 43)
   
What type of variable is Date?

In VB, Date is a function, returning the date as type Date. Doing Date-43 gives you 43 days ago?

What happens if you set the system date on your PC to 29 February 2000 or later and try again. Does it still not give you the year 2000?

If it does, then there's your problem. For some reason it won't let you choose the current year unless you're at least 43 days into it.

Really weird. Can you find out what the "-43" is supposed to do?

Thanks

Pino

0
caraf_gCommented:
<g> mark and I are thinking along the same lines.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mcriderCommented:
I think you'd be better off using the DateAdd Function...  You can use it to add or subtract a time interval to/from a date...

Dim MyDate As Date
MyDate = DateAdd("D",30,now) 'Adds 30 days from today

MyDate = DateAdd("D",-30,now) 'Subtracts 30 days from today



The first argument of DateAdd is the time interval:

yyyy      Year
q      Quarter
m      Month
y      Day of year
d      Day
w      Weekday
ww      Week
h      Hour
n      Minute
s      Second

The second argument is the actual time measurment, and the 3rd argument is a date.


Hope this helps!


Cheers!
0
InformativeCommented:
Yes this is because CYR is being set to something like this -

Do "ALT-F11" in Excel then "Ctrl-G" and type this exactly

?Date-43

you will get something like

12/19/99

I would also start by going through and change every instance of

App.Screenupdating =  True/False

to

App.screenupdating=AllowScreenOff

there is nothing more annoying than trying to debug or step through code which is trying to turn the screen off on you and this way you can set ALLOWSCREENOFF to false and it wont turn it off ever.

Date should be a built in system VBA function as in Date() and always returns TODAYS date.

just change this

For count1 = cyr To 1994 Step -1

to this
For count1 = cyr+5 To 1994 Step -1

or this
For count1 = 2010 To 1994 Step -1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kristincaAuthor Commented:
Thank you very much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.