Excel VBA Forms - Assign a date value to a cell

[kevinonearth]

Hi,

I'm creating a database (using excel forms) of contracts that my organisation will assign to different contractors.

For example I have

proposed start date (of contract)

Finish date

Total duration

Admin costs

Staff costs


I'm new to VBA, must I set out the ground rules at the very beginning, i.e. must I say that when entering in a date it must be in form: DD/MM/YYYY and how do I control the form so that will always be the case?


here is what I have at the moment:

https://dl.dropboxusercontent.com/u/90990975/LTI%20Input%20form.xlsm

 

[Luke M]

No, you don't have to set the rules at beginning. It is best practice to declare the types of variables you will be using such as:

[pre]

Dim MyString as String
Dim MyLong as Long
Dim MyDate as Date[/pre]
however, XL treats dates as just an integer. So, people can technically input any date format and XL will take that and convert to a number (like, 41375 for Apr 11, 2013). You can then control the format of what it looks like when you want to display the data using the Format method. E.g.,

NiceFormat = Format(MyDate,"dd/mm/yyyy")

or you can just set the cell formatting to display correctly.


Hope that helps.

 

[DaveTurton]

I have a lot of problems with UK dates and find myself having to format twice.


=format(mydate, "short date")

=format(mydate, "dd/mm/yyyy")