# Re: [R] converting an R function into VBA

From: Kenn Konstabel <lebatsnok_at_gmail.com>
Date: Sat, 21 Jun 2008 18:55:56 +0300

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 <yang_eric9_at_yahoo.com> 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_at_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_at_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. Received on Sat 21 Jun 2008 - 16:24:36 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Sat 21 Jun 2008 - 16:30:48 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.