Inherited VBA no clue

[dmheller]

I got a file that was handed down to me. It does a calculation and i think thats it.
In AR8, i have this equation
=DeltaOhms(S8,T8,U8,1)
In S8,T8,U8 i have 3 numbers that i go out and measure. They go A-B, B-C, C-A the 1 is the phase.
i have this function in the vba script. Can anyone dumb this down for me to a simple equation?? I tried to just do the phase 1 equation but it isnt just plug in numbers and get an answer, something else i think it going on. Any help would be great.
thanks
Also, if it helps inputs in
S,T,U are

40.8mΩ​

41.7mΩ​

42.1mΩ​

The output in AR8 is

32.0mΩ​

Function DeltaOhms(AtoB As Double, BtoC As Double, CtoA As Double, Phase As Double) As Variant 'add on function to convert terminal resistance to element resistance in a delta circuit

Dim A As Variant
Dim y As Variant
Dim x As Variant

ReDim y(1 To 3, 1 To 1) 'fill "y" array
y(1, 1) = AtoB
y(2, 1) = BtoC
y(3, 1) = CtoA

ReDim A(1 To 3, 1 To 3) 'fill "A" array
A(1, 1) = 1
A(2, 1) = 0
A(3, 1) = 1
A(1, 2) = 0
A(2, 2) = 1
A(3, 2) = 1
A(1, 3) = 1
A(2, 3) = 1
A(3, 3) = 0

x = WorksheetFunction.MMult(WorksheetFunction.MInverse(A), y) 'calculate intersections

'define outputs
A = x(1, 1)
B = x(2, 1)
C = x(3, 1)

'calculate result for selected element and divide by 2
If Phase = 1 Then DeltaOhms = ((A * B + A * C + B * C) / C) / 2
If Phase = 2 Then DeltaOhms = ((A * B + A * C + B * C) / A) / 2
If Phase = 3 Then DeltaOhms = ((A * B + A * C + B * C) / B) / 2

End Function

 

[Rick Rothstein]

Are the values in S, T and U real numbers custom formatted to show the "mΩ" at the end or are the values Text typed in exactly as shown?

Also, what row are the values in S, T and U in... Row 8 ?

 

[dmheller]

Are the values in S, T and U real numbers custom formatted to show the "mΩ" at the end or are the values Text typed in exactly as shown?

Also, what row are the values in S, T and U in... Row 8 ?

They are numbers with a custom format.
Yes, row 8

 

[Rick Rothstein]

Okay, the only possible way I can see to do this with a formula is if you used a few helper cells. If you are willing to do that, tell me where the last used column is on your worksheet (I would put the helper cells past that). You could hide those columns if you did not want to expose the helper columns to any of your users.

While I wait for you to answer the above question, I thought you might find it interesting that the function you posted can be replace by this one...

Function DeltaOhms(AtoB As Double, BtoC As Double, CtoA As Double, Phase As Variant)
  Dim x As Variant
  x = WorksheetFunction.MMult(WorksheetFunction.MInverse([{1,0,1;0,1,1;1,1,0}]), Application.Transpose(Array(AtoB, BtoC, CtoA)))
  DeltaOhms = (x(1, 1) * x(2, 1) + x(1, 1) * x(3, 1) + x(2, 1) * x(3, 1)) / x(Mid("312", Phase, 1), 1) / 2
End Function

 

[dmheller]

Rick,
thanks. I guess i should reword the original question . I am looking for an equation that is not in VBA but is just the math. So just using S8,T8,and, U8. I want to understand what the vba is doing. I dont want to use deltaohms or a vba script.

 

[Rick Rothstein]

First off, there is no simple equation that will show you what is going on. Whenever you have matrices involved in calculations, that is a way of showing long sets of calculations in a more compact way. With that said, I went through the various matrix calculations involved in your original function and found there were simplifications that could be made in the code. Once I went through them all, I came up with this set of straightforward calculations that eliminated all of the matrix mumbo-jumbo. There are two lines of active code that show you the pattern used to create the elements of the one-dimensional (zero-based) Arr array variable (originally this was the two-dimensional one-based 'x" arr in your original function where the second element of the 'x' array was always 1). The second line uses those elements in the same mathematical calculation that the elements of the 'x' array from your original function was put through. Hopefully in this simplified form, you can see whatever it is you wanted to see in equation form. Just to note, I have no idea of how the code I produced applies to your real world situation... all I did was simplify the overly complicated original function you posted. With that said, here is the "simplified" DeltaOhms function for you to use and study...

Function DeltaOhms(AtoB As Double, BtoC As Double, CtoA As Double, Phase As Variant)
  Dim Arr As Variant
  Arr = Array(AtoB - BtoC + CtoA, -AtoB + BtoC + CtoA, AtoB + BtoC - CtoA)
  DeltaOhms = (Arr(0) * Arr(1) + Arr(0) * Arr(2) + Arr(1) * Arr(2)) / Arr(Mid("201", Phase, 1)) / 4
End Function

 

[Rick Rothstein]

If you think it will help you to see the equations with all values fully substituted, here are the equations for each individual Phase...

DeltaOhmsPhase1 = ((AtoB - BtoC + CtoA) * (-AtoB + BtoC + CtoA) + (AtoB - BtoC + CtoA) * (AtoB + BtoC - CtoA) + (-AtoB + BtoC + CtoA) * (AtoB + BtoC - CtoA)) / (AtoB + BtoC - CtoA) / 4

DeltaOhmsPhase2 = ((AtoB - BtoC + CtoA) * (-AtoB + BtoC + CtoA) + (AtoB - BtoC + CtoA) * (AtoB + BtoC - CtoA) + (-AtoB + BtoC + CtoA) * (AtoB + BtoC - CtoA)) / (AtoB - BtoC + CtoA) / 4

DeltaOhmsPhase3 = ((AtoB - BtoC + CtoA) * (-AtoB + BtoC + CtoA) + (AtoB - BtoC + CtoA) * (AtoB + BtoC - CtoA) + (-AtoB + BtoC + CtoA) * (AtoB + BtoC - CtoA)) / (-AtoB + BtoC + CtoA) / 4

 

[Eric W]

Using a bit of algebra, you can do this with just a worksheet function:

Book1
	S	T	U	V	W	X
1	AtoB	BtoC	CtoA	Phase	DeltaOhms	DeltaOhms
2	40.8	41.7	42.1	1	32.01287	32.01287
Sheet3

Cell Formulas
Range	Formula
W2	W2	=deltaohms(S2,T2,U2,V2)
X2	X2	=((S2*T2+S2*U2+T2*U2)-SUMSQ(S2:U2)/2)/(SUM(S2:U2)-2*INDEX(S2:U2,4-V2))/2

However, these type of formulas tend to have a pattern to them, and by algebraically reducing them, much of that pattern is lost. As Rick indicated in his latest post, there might be value in keeping the pattern in order to (relatively) easily see if the formula is correct. I tested the formula on several cases and it appears to work correctly. If that is your primary goal, then this might work for you.

 

[Eric W]

A slightly shorter version of the X2 formula:

=(SUM(S2:U2)^2/4-SUMSQ(S2:U2)/2)/(SUM(S2:U2)-2*INDEX(S2:U2,4-V2))