UDF VBA Array Part output

[ShelleyBelly]

Hello all,

I've the below user defined function which works well, however i would like to extract only part of the output using vba, in the below case it is the second output glon2. I know how to do this in a spreadsheet as a CSE function however i don't intend on putting the results into a spreadsheet and instead want to use it inside VBA to pass to another function.

direct_ell = Array(glat2, glon2)

Many thanks in advance,

Tom

Function direct_ell(glat1, glon1, a, f, faz, s)

r = 1 - f
tu = r * Tan(glat1)
sf = Sin(faz)
cf = Cos(faz)
If (cf = 0#) Then
  b = 0#
Else
  b = 2# * Atn2(tu, cf)
End If
cu = 1# / Sqr(1 + tu * tu)
su = tu * cu
sa = cu * sf
c2a = 1 - sa * sa
x = 1# + Sqr(1# + c2a * (1# / (r * r) - 1#))
x = (x - 2#) / x
c = 1# - x
c = (x * x / 4# + 1#) / c
D = (0.375 * x * x - 1#) * x
tu = s / (r * a * c)
y = tu
Do
 sy = Sin(y)
 cy = Cos(y)
 cz = Cos(b + y)
 E = 2# * cz * cz - 1#
 c = y
 x = E * cy
 y = E + E - 1#
 y = (((sy * sy * 4# - 3#) * y * cz * D / 6# + x) * D / 4# - cz) * sy * D + tu
Loop While (Abs(y - c) > 0.00000000000005)
b = cu * cy * cf - su * sy
c = r * Sqr(sa * sa + b * b)
D = su * cy + cu * sy * cf
glat2 = Atn2(D, c)
c = cu * cy - su * sy * cf
x = Atn2(sy * sf, c)
c = ((-3# * c2a + 4#) * f + 4#) * c2a * f / 16#
D = ((E * cy * c + cz) * sy * c + y) * sa
glon2 = Modlon(glon1 + x - (1# - c) * D * f)
direct_ell = Array(glat2, glon2)
End Function

 

[StephenCrump]

Dim vResult As Variant, vLastElement As Variant

'set values for glat1 etc
vResult = direct_ell(glat1, glon1, a, f, faz, s)

vLastElement = vResult(UBound(vResult))

 

[ShelleyBelly]

Ahh brilliant,

Thankyou Stephen.

You don’t know of any other sites with a similar problem so I can better appreciate this problems?

Thanks a lot for his again,

Tom

Dim vResult As Variant, vLastElement As Variant

'set values for glat1 etc
vResult = direct_ell(glat1, glon1, a, f, faz, s)

vLastElement = vResult(UBound(vResult))

 

[StephenCrump]

You don’t know of any other sites with a similar problem so I can better appreciate this problems?

Sorry, I don't understand the question?

Are you looking for help with VBA generally, or with something specific to your function, which is assume is to do with latitude/longitude coordinates?

 

[ShelleyBelly]

Hi Stephen

It was the latter, anything to do with taking one part of an array output .

Cheers,

Tom

 

[StephenCrump]

Perhaps a simple Google of VBA arrays?

Chip Pearson's site is always a great resource: http://www.cpearson.com/Excel/VBAArrays.htm

Paul Kelly's site clearly explains the basics: https://excelmacromastery.com/excel-vba-array/

Also worth looking at: http://www.snb-vba.eu/VBA_Arrays_en.html