@GoodyGoody ...
So, is there a way of checking an Excel field's datatype in VBA?
Now, my users are likely to be fairly non-techie so I want as foolproof a way of doing this as possible.
Taking those two quotes together, the answer to your question has to be "No" because a cell in Excel has no format until you actually try to use it. Your users are "non-techie" so can't be called upon to be reliable with the cues and clues you would need. There is such a thing as a VBA test for some string being a Date or being numeric, but it won't help you much. The link below leads to a list of functions that might be helpful. What you want (test for type xxxx) is called an Inspection function and is categorized as such in this list so page down to find that category.
Office VBA reference topic
docs.microsoft.com
For spreadsheets, the usage defines the interpretation to a large degree. Technically, every cell of a spreadsheet is an independent entity. IF you have it defined with some formula that references another cell, then the referencing cell is dependent on the referenced cell based on the function, not based on the cell itself. Cells have no dependency; functions do.
Cells in Excel are always and only text strings. Oh, you can format them by interpreting the text as digits in a number, but remember that they were originally entered as text.
You CAN set a columnar format of something numeric and then promptly put a text column heading in place in that column without having to change anything. What that means in practical terms is that you have to be prepared to do some really serious analysis on a cell by cell basis because your non-techie people cannot be relied upon to always do the right thing. Which is why perhaps you should arrange for data entry in an Access Form, which CAN be smart enough to look at text patterns at data entry time.