Macro Distance between points

stanysurfer · Aug 30, 2010

Hey guys, somewhat of a noob at vba and need a little help with a small problem...
I have a series of points with x and y co-ordinates and I need to find the distance between all of the points and each other. The number of points will change and this needs to be taken into account in the vba code. I tried using an next statement with a loop, but really struggling. Also it is subject to the following formula:

gh = C0 + C * [1.5(h/a)-0.5*(h/a)^3] for h<a..
gh = C0 + C h>a
gh = o h = 0

Where gh "gamma h" is what i ultimately need to work out, after working out h, Co and C are values given, h is the distance between the points, which will need to be put in the formula and a is a given number (a distance). So a simple if statement could suffice for these equations depending on what the distance between points are..
For example if there are 4 points, the matrix will be 4 x 4 with 16 values of gh...

Any help at all would be greatly appreciated.
Thank you

bobsan42 · Aug 30, 2010

i'm actually a bit confused.
first - cannot understand what you really need - do you need to figure out the procedure and the loops to get all the distances?
second - (correct me if i'm wrong or missing something) but if you have 4 points then you only have 6 distances (not 16) and since gh is a function of h there will only be 6 values of gh.

stanysurfer · Aug 30, 2010

bobsan42 said:
i'm actually a bit confused.
first - cannot understand what you really need - do you need to figure out the procedure and the loops to get all the distances?
second - (correct me if i'm wrong or missing something) but if you have 4 points then you only have 6 distances (not 16) and since gh is a function of h there will only be 6 values of gh.

Sorry mate if i didnt explain it properly...
I need the procudure for the vba code so that i can find the gh values for any given number of points..I need to be able to run the macro and for the values to be entered into excel in a matrix form, 4 x 4 table kind f thing, with all the values for gh entered into it.. Also for 4 points it wont be 6 distances, for this application it is 16. This is because points and themselfs are also included, the gh values will obviously be "0" as the distance is also "0" but i still need them...Hope this is a better explanation...
Cheers

bobsan42 · Aug 31, 2010

OK it brought some light.
In order to provide a complete solution more details are needed, but in general for N number of points it should be something like this:

Code:

Function matrix_gh(param_A As Double, param_C As Double, param_C0 As Double)

Dim i As Long, j As Long, N As Long

'>>>... here a value for N must be assigned somehow ...

Dim arr_X(1 To N) As Double, arr_Y(1 To N) As Double
Dim arr_h(1 To N, 1 To N) As Double, arr_gh(1 To N, 1 To N) As Double

'>>>... here values for X & Y of the points must be assigned somehow ...

For i = 1 To N
    For j = 1 To N
        arr_h(i, j) = Sqr((arr_X(j) - arr_X(i)) ^ 2 + (arr_Y(j) - arr_Y(i)) ^ 2)
        Select Case arr_h(i, j)
        Case Is > param_A
            arr_gh(i, j) = param_C0 + param_C
        Case 0
            arr_gh(i, j) = 0
        Case Else
            arr_gh(i, j) = param_C0 + param_C * (1.5 * (arr_h(i, j) / param_A) - 0.5 * (arr_h(i, j) / param_A) ^ 3)
        End Select
    Next j
Next i
'>>>... here the arrays for h & gh must be put on a worksheet somewhere/ somehow - maybe smth like:
Range("B2").Resize(N, N) = arr_gh
End Function

this is all i can do with the info you posted - hope this helps.

bobsan42 · Aug 31, 2010

thought a bit further about it:

Code:

Function matrix_gh(param_A As Double, param_C As Double, param_C0 As Double, coordX As Range, coordY As Range) As Variant

Dim i As Long, j As Long, N As Long
If coordX.Rows.Count <> 1 And coordX.Columns.Count <> 1 Then GoTo ExitOnDataError
If coordY.Rows.Count <> 1 And coordY.Columns.Count <> 1 Then GoTo ExitOnDataError
If coordX.Cells.Count <> coordY.Cells.Count Then GoTo ExitOnDataError

N = coordX.Cells.Count

Dim arr_X(1 To N) As Double, arr_Y(1 To N) As Double
Dim arr_h(1 To N, 1 To N) As Double, arr_gh(1 To N, 1 To N) As Double
arr_X() = coordX.Value
arr_Y() = coordY.Value

For i = 1 To N
    For j = 1 To N
        arr_h(i, j) = Sqr((arr_X(j) - arr_X(i)) ^ 2 + (arr_Y(j) - arr_Y(i)) ^ 2)
        Select Case arr_h(i, j)
        Case Is > param_A
            arr_gh(i, j) = param_C0 + param_C
        Case 0
            arr_gh(i, j) = 0
        Case Else
            arr_gh(i, j) = param_C0 + param_C * (1.5 * (arr_h(i, j) / param_A) - 0.5 * (arr_h(i, j) / param_A) ^ 3)
        End Select
    Next j
Next i
matrix_gh() = arr_gh()

Exit Function

ExitOnDataError:
matrix_gh = "#VALUE!"
Exit Function

End Function

it is not tested
all that's left i think is to use a Subroutine to assign the result array to a range - SL:

Code:

Range("B2").Resize(N, N) = matrix_gh

mikerickson · Aug 31, 2010

Where are the current x,y values? On a worksheet?

Given two points (x1, y1) (x2, y2),
My understanding is that h[(x1, y1), (x2, y2)] is the distance between them Sqrt((x1-x2)^2 + (y1-y2)^2).

How is gh[(x1, y1), (x2, y2)] calculated.

bobsan42 · Aug 31, 2010

after h is calculated gh is a function of h (post #1):

gh = C0 + C * [1.5(h/a)-0.5*(h/a)^3] for h
gh = C0 + C for h>a
gh = 0 for h = 0

mikerickson · Aug 31, 2010

What are C, C0 and especially a?

bobsan42 · Aug 31, 2010

parameters (post #1):

Where gh "gamma h" is what i ultimately need to work out, after working out h, Co and C are values given, h is the distance between the points, which will need to be put in the formula and a is a given number (a distance). So a simple if statement could suffice for these equations depending on what the distance between points are..

stanysurfer · Aug 31, 2010

C, Co and a are just values, integers, say 5, 0.5, 200...
Thanx for what you have done bobsan, though i do not know what the subroutine is, i have entered what you did into vba and run the function and all i get is #VALUE.. I have read through the code, how do i get the arr_X() = coordX.Value
arr_Y() = coordY.Value

I have them entered into excel in two colums, x and y, but when i type in the function...=matrix_gh(F3:G6) nothing comes up, also that would only give a value to one cell, i am usure how i would get it into a matrix form.
It would be much easier if i could show you my excel spreadsheet, but i am unable to add attachments for some reason....
I need the table in the form below:
1 2 3 4
1
2
3
4

So for four points there will be 16 values for gh, obviously 11,22,33 & 44 will be zero though, as the h value is zero because they are the same point...
Once again, thanx for all the help guys

Macro Distance between points

stanysurfer

New Member

bobsan42

Well-known Member

stanysurfer

New Member

bobsan42

Well-known Member

bobsan42

Well-known Member

mikerickson

MrExcel MVP

bobsan42

Well-known Member

mikerickson

MrExcel MVP

bobsan42

Well-known Member

stanysurfer

New Member

Similar threads

Share this page

Macro Distance between points

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock