Custom ExchangeRate function reference another workbook

[MarkoBox]

Idea is to make a custom function which i will install as an addin which will have a single argument (date) and fetch from a custom list exchange rate on that date. Basicly a vlookup.
So far i have gotten this far:

Please Login or Register  to view this content.

Sheet "ExRate" contains two collumns, A collumn with a date B with Rate.

Problem is date format, usually im typing them with dots so 25.02.2017 but the exrate sheet uses 25/02/2017 format which this setup cannot compare. I have noticed that data-> text to collumns has xlMDYFormat. MDY Date which manually works great for converting between the two date styles, but i failed replicating it in vba.
How do i sanitize input so it can recognize 25.02.2017 as well as 25/02/2017 and also 25.2.2017?
My solution so far is to just apend a new list with various date formats, but i would like to do it in code.
Thanks in advance.

[AB33]

Dot separator is a text not a date, so what you have is NOT DATE, but text-25.02.2017.

[MarkoBox]

Im trying to find out how to convert it to date? As i said if i type it with dots (25.02.2017) and then use text to columns to convert it to date format it works fine. So how to do it with vba, i tried CDate() but it doesnt work.

[kev_]

welcome to the forum

Please Login or Register  to view this content.

[AB33]

Why are you using a dot to type( Enter) dates? I could understand if the dates are imported in to excel.

[MarkoBox]

Thanks kev, i cant seem to get it to work it trows me expected array error despite me declaring all variables. I also found this solution, which seems to solve it:

Please Login or Register  to view this content.

Any advice on how to make it faster? I was thinking of putting it into array but my vba knowledge is limmited for it.

[MarkoBox]

Why are you using a dot to type( Enter) dates? I could understand if the dates are imported in to excel.

On num keyboard .(del) key is much more easier to reach than slash, so everyone at work, including me type dates like that during manual entry.

[kev_]

suggestion to speed it up:
1. use a macro to convert all historic dates from 28.02.2016
PLUS to ensure future typing is in correct date format
2. "change event" macro that converts new dates when you edit a date cell