EXCEL VBA: getting the integer value of mm in today's date
EXCEL VBA: getting the integer value of mm in today's date
novice programmer here and it's my first question here.
I'm interested in doing some invoicing using excel, and a part of that is generating invoice numbers, e.g. 1870001-1879999, referring to the year 2018, the month of July, and the 1st to 9999th invoice of this month.
To do this, I've written a bit of code:
Private Sub Workbook_Open()
Dim MValue As String
Dim XValue As Integer
MValue = Format(Date, "mm/dd/yy")
LValue = Left(MValue, 2)
XValue = CInt(LValue)
Range("K1").Value = XValue
End Sub
This would give me "7" as an integer in the cell K1, which I can then multiply by 10000 and add to 1800000 to give the baseline value of 1870000.
I have two questions:
1) what's a better way to do this problem? My method seems incredibly long. (dates are stored awkwardly in excel)
2) why is it that:
Range("K1").Value = XValue * 10000
causes an error?
Thanks.
Rethink your invoice identifier before continuing. The date should be stored as 07 not 7 or Oct-Dec will be confused with Jan and you will have to determine the length of the invoice number (7 0r 8) in order to retrieve a certain month's invoices with wildcards.
– Jeeped
Jul 1 at 16:40
As a general rule, identifiers should be
String rather than a number. And then build the string up (Year2Char & Month2Char & Number4Char). This negates the need for any intricate maths and possible ordering issues when numbers strip/add leading zeros.– AJD
Jul 1 at 22:19
String
Year2Char
Month2Char
Number4Char
@Jeeped thanks so much! come October, I would've been crushed.
– KsL
Jul 2 at 1:23
@AJD I see your way creates a bulletproof way of generating the identifier, but how would I go about +1 to the invoice number each time if it's saved as a string? would the code first have to convert the string into a long integer first, then convert back to a string?
– KsL
Jul 2 at 1:26
2 Answers
2
Your problem is because Integer cannot handle the size of the number you are trying to pass. You will get an overflow error.
Declare as Long to avoid this.
Dim XValue As Long
CLng(LValue)
What you have written could be re-written as:
Range("K1").Value = Month(Date) * 10000
You can use Day and Month functions. Here's one of the possible solutions:
Day
Month
Sub G()
Dim dt As Date, d, m, s
dt = Now
d = Day(dt): m = Month(dt)
s = d & m & GenerateID()
End Sub
Function GenerateID()
GenerateID = "SOMETHING"
End Function
sorry I don't understand the GenerateID="SOMETHING" part, how do I get this to actually be sequential invoice numbers?
– KsL
Jul 2 at 1:32
You could store this number somewhere in the book or in invisible name.
– JohnyL
Jul 2 at 8:29
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Use Long rather than Integer btw and so Dim XValue As Long and CLng(LValue). Your error is an overflow one.
– QHarr
Jul 1 at 14:51