Can't you just import data from Excel using RODBC, then use your function in
R, and then write the results to Excel again? It would be much less painful
than doing it in VBA...
Otherwise, look for MMult and Transpose and similar things in VB help, and
then ask some VB experts...
Kenn
On Sat, Jun 21, 2008 at 5:06 PM, Eric yang <[EMAIL PROTECTED]> wrote:
> Hi everyone,
>
> I want to convert an R function into VBA for calculating the eigenvectors
> and eigenvalues of a matrix using the "Power Method". The function is:
>
>
> PowerMethod <- function(x, tolerance) {
> my.mat <- var(x[,-1], na.method="available")
> matSize <- dim(my.mat)[1]
> eigenVec <- matrix(NA, nrow=matSize, ncol=matSize)
> eigenVal <- rep(NA, matSize)
> for(j in 1:matSize) {
> x <- rep(1, matSize)
> yk <- x + tolerance + 1
> while(all(x-yk<tolerance)) {
> yk <- my.mat%*%x
> beta <- yk[abs(yk)==max(abs(yk))]
> x <- (1/beta)*yk
> }
> eigenVec[,j] <- (1/sqrt(sum(x^2)))*x
> eigenVal[j] <- beta
> my.mat <- my.mat - eigenVal[j]*eigenVec[,j]%*%t(eigenVec[,j])
> }
> list(eigenVec, eigenVal)
> }
>
> I want to input a matrix from the excel spreadsheet along with a tolerance
> level (i.e. two inputs).
>
> The function then calculates the covariance matrix, call this M (m x m), of
> the input data. You then make an initial guess of the eigenvector, let's say
> this is a vector of 1's (m x 1) (call this x), you multiply the two together
> to get
>
> y=Mx
>
> You then calculate beta which is the element of y with the largest modulus.
> And, recalculate x as
>
> x=(1/beta)y
>
> and then calculate new y, y=Mx
>
> This is done iteratively until the difference between the old x and new x
> is less than the tolerance level.
>
> The normalised x, i.e. (1/sqrt(sum(x^2)))*x , call this v, is the first
> prinicipal component and the last value of beta is the associated
> eigenvalue.
>
> A new M is calculated as Mnew = M-beta*v*transpose(v)
>
> And, the whole procedure is repeated for Mnew to get the second prinicipal
> component and associated eigenvalue. The is done m times.
>
> I want to output all the eigenvectors (prinicipal components) and
> eigenvalues to some location in the spreadsheet.
>
>
> I would be extremely grateful if someone could assist me in converting this
> function to VBA.
>
> Thanks in advance.
> Eric
>
>
>
>
> [[alternative HTML version deleted]]
>
>
> ______________________________________________
> R-help@r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
>
[[alternative HTML version deleted]]
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
