This forum tends to be pretty strict about putting code inside of code tags (see the forum rules link for instructions on how to use code tags).

This
X ^ [1,2,3,4,5,6]
is something VBA does not know how to do. As part of an array function, Excel knows how to take an array, raise each element to a power, and output an array based on the two input arrays. VBA, on the other hand, does not know how to do this.

In order to do this in VBA, you need to build the entire X input matrix as a 2 dimensional array:

x(1,1)=z(1)^1,x(1,2)=z(1)^2,x(1,3)=z(1)^3,...
x(2,1)=z(2)^1,x(2,2)=z(2)^2,...
...
Another error to note while you are testing -- you can't fit a 6th order polynomial with only 3 data points. You will need at least 7 data points to fit a 6th order polynomial.