> So i want to put  the values  of A1:A5000 into an array

Try this...

Dim myARRAY as Variant
myARRAY = MyXLSheet.Range("A1:A5000")

...where MyXLSheet was already defined (let me know if you need help
with this part). Hope this helps.