macro to put numbers in random order

One (non-macro) approach would be to use

=RAND() in column B, copy and paste values, and then:

EITHER: sort columns A & B using the values in column B.

OR use this formula in C1, copied down:
=INDEX($A$1:$A$40,MATCH(SMALL($B$1:$B$40,ROWS($C$1:C1)),$B$1:$B$40,0))

You're implying no repeats, but you haven't stated that.

Does it have to be a macro?
If not try this

Put your 40 numbers in A1:A40
in B1:B40 put =RAND()
in C1:C40 put =INDEX(A$1:A$40,MATCH(SMALL(B$1:B$40,ROW()),B$1:B$40,0))

Your output is in column C. 40 non-repeated random numbers.

UPDATE: Damn! 1 minute late :-(