Solved

VBA Test For Valid Number Format

Posted on 2016-09-21
6
95 Views
Last Modified: 2016-09-21
Hello Everyone,
I am looking for a function that when passed a number format as a string (e.g. "mm/dd/yyyy") returns True or False depending on whether the string is indeed a valid number format.  Obviously "mm/dd/yyyy" is valid but "1.1.2..3kklksjlkj" is not.  Does anyone have this already written up?   I'm assuming this will require the use of regular expressions and I have never swam in that end of the pool.

Many thanks!
Kyle

P.S.  Please do not post solutions using error checking.  I know that is an option.
0
Comment
Question by:kgerb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 9

Accepted Solution

by:
Shahid Thaika earned 500 total points
ID: 41808944
In VBA, you can use in-built functions such as...

IsNumeric("666")

or

IsDate("08/22/1985")

However, it will only work with proper formatting. i.e. use of ' / '.

If your text contains ' . ' due to some reason, you can do something like...

IsDate(Replace("08.22.1985", ".", "/"))
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41809391
valid number format
The different format string options depend on the data being formatted.  Your first example was not a number format, rather a date format.  Please explain your problem in greater detail.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41809394
"1.1.2..3kklksjlkj" is not
Tested this 'not valid' format string In the immediate window of a VBA application and got a result with no error message.
?format(789,"1.1.2..3kklksjlkj")
1789.1.2..3kklksjlkj

Open in new window

0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 12

Author Comment

by:kgerb
ID: 41809510
Hi Mark,
Thanks for your comment.  In this particular instance I am dealing with dates.  I should have been more specific in my original post.  You are correct, running this from the immediate window does not throw an error...
?Format("1/1/2000","1.1.2..3kklksjlkj")
136526.1.2..3kklksjlkj

Open in new window

However, entering this as a custom format in the Format Cells dialogue does produce an error.

At any rate, my little project is attached.  It's a simple calendar add-in that will let the user graphically pick a date and then enter it into the selected cell.  There is a text box to control the date format.  My goal is to validate the text entered into that box before I attempt to modify the cell format and write the date to the sheet.  Thus, the need to test a text string as being a valid date format.  Currently, I'm using Shahid's solution.  If I need to go with this I can, but I was hoping to gain a little more understanding of what is allowed and what it not.

Thanks,
Kyle
Calendar.xlam
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41809582
use the IsDate() function
0
 
LVL 12

Author Closing Comment

by:kgerb
ID: 41809586
Thanks Shahid!
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

728 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