[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Date Increment

Posted on 2000-01-26
6
Medium Priority
?
244 Views
Last Modified: 2010-05-02
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?
0
Comment
Question by:kristinca
6 Comments
 
LVL 12

Expert Comment

by:mark2150
ID: 2390498
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
 
LVL 10

Expert Comment

by:caraf_g
ID: 2390522
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
 
LVL 10

Expert Comment

by:caraf_g
ID: 2390525
<g> mark and I are thinking along the same lines.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 14

Expert Comment

by:mcrider
ID: 2390544
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
 
LVL 3

Accepted Solution

by:
Informative earned 800 total points
ID: 2390566
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
 

Author Comment

by:kristinca
ID: 2390612
Thank you very much!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question