IsDate Check allowing different local date formats

Question

I have a problem with VBA Code for an Excel macro in a file that is used both by users using the local date format dd.mm.yyyy as well as other users using the local format dd/mm/yyyy. This file contains a sheet where we need to import/copy paste data and then modify it via VBA code. The following part causes issues

While IsDate(Sheets(sSheet).Range("C" & i).Value) = True
    Sheets(sSheet).Range("A" & i).Value = Variable1
    Sheets(sSheet).Range("B" & i).Value = Variable2
    i = i + 1
Wend

The data we need to import has the dates in the format dd.mm.yyyy so the IsDate function returns false when users with the local format dd/mm/yyyy try to run this code. This causes problems.

Is there any elegant way to fix this so that IsDate returns the value True when users with the local format dd/mm/yyyy try to run the code? Can you somehow force a certain region/date setting for that check in VBA?

Many thanks!

Answer 1

You could replace those dots:

While IsDate(Replace(Sheets(sSheet).Range("C" & i).Value, ".", "/") = True
    Sheets(sSheet).Range("A" & i).Value = Variable1
    Sheets(sSheet).Range("B" & i).Value = Variable2
    i = i + 1
Wend