CODEWITHSUNDEEP
excelvba
Lazy Yoyo
Lazy Yoyo

Reputation: 9

How to update an old vba code to work on excel?

I have this code from an old book that does not work in my Excel. It is supposed to calculate the slope and intercept of a Deming regression from a range of numbers, x and y

Function Deming(XValues, YValues)
Dim MeanX(), MeanY()
Ncells = XValues.Count
ReDim MeanX(Ncells / 2), MeanY(Ncells / 2)

For x = 2 To Ncells Step 2
    MeanX(x / 2) = (XValues(x - 1) + XValues(x)) / 2
    MeanY(x / 2) = (YValues(x - 1) + YValues(x)) / 2
    SumX = SumX + MeanX(x / 2): SumY = SumY + MeanY(x / 2)
    SumX2 = SumX2 + (MeanX(x / 2)) ^ 2
    SumY2 = SumY2 + (MeanY(x / 2)) ^ 2
    SumXY = SumXY + MeanX(x / 2) * MeanY(x / 2)
    SumDeltaX2 = SumDeltaX2 + (XValues(x - 1) - XValues(x)) ^ 2
    SumDeltaY2 = SumDeltaY2 + (YValues(x - 1) - YValues(x)) ^ 2
Next

XBar = SumX / N: YBar = SumY / N
Sx2 = (N * SumX2 - SumX ^ 2) / (N * (N - 1))
Sy2 = (N * SumY2 - SumY ^ 2) / (N * (N - 1))
Sdx2 = SumDeltaX2 / (2 * N)
Sdy2 = SumDeltaY2 / (2 * N)
rPearson = (N * SumXY - SumX * SumY) / Sqr((N * SumX2 - SumX ^ 2) * (N * 
SumY2 - SumY ^ 2))


lambda = Sdx2 / Sdy2
U = (Sy2 - Sx2 / lambda) / (2 * rPearson * Sqr(Sx2) * Sqr(Sy2))
Slope = U + Sqr(U ^ 2 + 1 / lambda)
Intercept = YBar - Slope * XBar

Deming = Array(Slope, Intercept)
End Function

Does this have a bad syntax or not?

Upvotes: 0

Views: 203

Answers (1)

Vityata
Vityata

Reputation: 43585

First this is not old code, this is simply bad code.

Anything in VBA, which does not compile, when someone writes Option Explicit on the top of the Module/Worksheet is a bad syntax. This is a rule of a thumb. And in the case of the code, if this one is pasted to the editor the following line is red:

rPearson = (N * SumXY - SumX * SumY) / Sqr((N * SumX2 - SumX ^ 2) * (N * 
SumY2 - SumY ^ 2))

This is because it should be in 1 line, and not in 2.

So, concerning the question - how to update it - as a first step, make sure the code compiles with Option Explicit on top (Option Explicit statement). So, write Option Explicit and then go to Debug>Compile on the VBEditor's ribbon. VBeditor will highlight the problem. The first one is that Ncell is not defined:

enter image description here

Then find a way to define it, e.g. write Dim Ncells as Variant or as anything else you may consider useful on the top of the highligted line. It could be that just declaring a variable is not enough, as there is a calculation XBar = SumX / N in the code. There, N should be declared and assigned to a value. If it is only declared, it will be 0, and then a division by 0 will be an error. Thus, probably something like this should be written, depending on the logic: Dim N as Double: N = 1

Upvotes: 2

Related Questions