Coversion of R-code to VBA -- UDF Error

[Juggler_IN]

I have a code in R which I am trying to convert to Excel VBA.

The R-code is -

[FONT=Courier][SIZE=3][FONT=Courier][SIZE=3]draw.zeta<-function(nrep,alpha){
if (alpha<=1){stop("alpha must be greater than 1!\n")}
zeta<-numeric(nrep) ; for (i in 1:nrep){
index<-0 ; while (index<1){
u1<-runif(1) ; u2<-runif(1)
x<-floor(u1^(-1/(alpha-1))) ; t<-(1+1/x)^(alpha-1)
w<-x<(t/(t-1))*(2^(alpha-1)-1)/(2^(alpha-1)*u2)
zeta[i]<-x ; index<-sum(w)}}
zeta}
[/SIZE][/FONT][/SIZE][/FONT]

And, the corresponding VBA version, which is not executing, is -

Public Function Zipf(Optional ByVal alpha As Double = 3#) As Double
If alpha <= 1# Then Exit Function
Dim index As Double, u1 As Double, u2 As Double, x As Double, t As Double, w As Double
index = 0
While index < 1
u1 = RND()
u2 = RND()
x = WorksheetFunction.Floor(u1 ^ (-1 / (alpha - 1)), 1)
t = (1 + 1 / x) ^ (alpha - 1)
w = x < (t / (t - 1)) * (2 ^ (alpha - 1) - 1) / (2 ^ (alpha - 1) * u2)
Zipf = x
index = index + w
Wend
Zipf = x
End Function

I am not able to figure out the error.

 

[rlv01]

I can get it to run, if I clean it up a little

Public Function Zipf(ByVal alpha As Double) As Double
    Dim index As Double, u1 As Double, u2 As Double, x As Double, t As Double, w As Double

    If alpha > 1 Then
        index = 0
        While index < 1
            u1 = Rnd()
            u2 = Rnd()
            x = WorksheetFunction.Floor(u1 ^ (-1 / (alpha - 1)), 1)
            t = (1 + 1 / x) ^ (alpha - 1)
            w = x < (t / (t - 1)) * (2 ^ (alpha - 1) - 1) / (2 ^ (alpha - 1) * u2)
            index = index + w
        Wend
    Else
        x = 0    ' or whatever
    End If
    Zipf = x
End Function

Whether it runs correctly is another matter. I'm not familiar with R, but your R function appears to have two input parameters nrep, and alpha. But your VBA function only has alpha. Why? It seems like you are missing a loop of the form

for i = to to nrep

'do something

next i

This next line

w = X < (t / (t - 1)) * (2 ^ (alpha - 1) - 1) / (2 ^ (alpha - 1) * u2)

makes no sense to me and I suggest that you revist the conversion from R. In vba

X < (t / (t - 1))

is a boolean expression, i.e. X is less than (t / (t - 1)) so you are basically multiplying a boolean expression

w = Some boolean expression  * (2 ^ (alpha - 1) - 1) / (2 ^ (alpha - 1) * u2)

Is that really what you want? I suspect the boolean expression in that equation is going to flip back and forth from 0 and -1 depending on whether the condition is true or false.

 

[shg]

May we know what the function is supposed to calculate?

 

[Juggler_IN]

@SHG,

I am trying to generate Zipf Distribution (Zeta Distribution)
[url]https://en.wikipedia.org/wiki/Zeta_distribution[/URL]
https://en.wikipedia.org/wiki/Zipf's_law
@rlv01,
nrep is number of items to be generated. For Excel it would be like For I = 1 to nrep ... zipf udf ... next I.

PS: Was travelling hence a late revert.

 

[Juggler_IN]

@rlv01, the r-code is correct and has been taken from a published source.

 

[Juggler_IN]

@shg,

I am trying to generate Zipf Distribution (Zeta Distribution)
https://en.wikipedia.org/wiki/Zeta_distribution
https://en.wikipedia.org/wiki/Zipf%27s_law


Thanks
Thanks

 

[rlv01]

@rlv01, the r-code is correct and has been taken from a published source.

@rlv01,nrep is number of items to be generated. For Excel it would be like For I = 1 to nrep ... zipf udf ... n7ext I

Given the above, then shouldn't your VBA code contain an nrep loop?

 

[Juggler_IN]

I am calling zipf function in a SUB using a for next loop, which repeats itself for nrep times and populates the random number in a column beep times.