The goal is to format the incoming text date from (eg "9302011" to #09/30/2011#) but the code fails at the first lines of the CInt(left..) The code is generating "93" as the month.
Please evaluate the code attached to the Worksheet which is attached.
Why isn't the Excel VBA code working?
I appreciate you reviewing the code and provide feedback for improvement & correction.
In the mean time, have a great Sunday. Thank you very much for the support. – E.L.
ANSWER: Excel Date Conversion Theory
Great question. My solution will work with both Excel worksheet functions and Excel VBA.
The "trick" here is that January – September (months 1-9) are 5 characters long while October through December (months 10-12) are 6 characters long.
You could handle this two ways:
- Use logic to check if the text string is 5 or 6 characters long and process accordingly
- Reformat the text string to be 6 characters long (add a leading zero) and then process
Solution #1 – Check Date String Length
The first way to solve this problem is to use logic to check to see if the text string is 5 or 6 characters long. So the code using Excel VBA would be:
Here is the same solution using Excel worksheet formulas:
Solution #2 – Reformat Date String With Leading Zeros
Another technique you can use is to convert the string into a Long datatype and then convert that back into a string. The Excel VBA code would look like:
Using this technique we have a consistently formatted string that is always going to be six characters long.
Here is this solution using Excel worksheet functions:
Solution #2 With More Compact Code…
If you wanted to make the code more compact you could re-write it as:
The risk that you run with the more compact code is that the type conversion of sPromiseDate into a Long datatype would fail and you would have a run time error that would be more tricky to handle than with the solution that uses more lines of code.
For example, if someone had "Fat Finger Syndrome" and entered the date text as "90F2011" this would not be able to be converted into a number and the line of code would create a run-time error. This run-time error would be easier to handle if you had a two step process versus a one step process.
This is a general rule: the more compact the code (fewer lines) the more difficult it is to implement error handling. The more lines of code the easier it is to implement error handling.