Wanting to create an array of coordinates in excel VBA

Question

I am wanting to create a variable in excel, that is an array of coordinates, such that I can reference the arrays of x or y coordinate values, using .x and .y

eg

dim coords as variant
coords.x = array(1,2,3,4,5) (or something similar)

I have been trying to research coordinate arrays on google, for ages yesterday, an hour this morning, and gotten absolutely nowhere. Also been trying with statements a property statements, and also gotten nowhere.

could anyone tell me how to do it please, if it can be done.

Thanks

How will it be used? Do you want a single data structure that holds both the x and y coordinates? — Alex P
– Alex P, Commented Feb 13, 2016 at 10:54
I think yes; primarily I just want a variable holding a list of coordinates, for example it could have 100 pairs, so that I can reference an individual value such as coords(98).x etc; or I might want to manipulate the list in pairs, coords(98) = coords(05) thereby changing both the x and y values of a pair in one go. I could do this with a multidimensional array in a less straightforward manner, but so that I don't get lost deep in my routines and manipulate my x's when wanting to manipulate my y's, I would to create variables that are clearly defined as coordinates with .x and .y properties. — user2115136
– user2115136, Commented Feb 13, 2016 at 11:14

Alex P · Accepted Answer · 2016-02-13 11:23:19Z

1

Here is one option using a collection:

Sub Cooordindates()
    Dim Coords As Collection

    Set Coords = New Collection

    Coords.Add Array(1, 2, 3, 4, 5), "x"
    Coords.Add Array(2, 4, 6, 8, 10), "y"

    Debug.Print Coords.Item("x")(0), Coords.Item("y")(0) '~~> Prints: 1    2
End Sub

The .Add takes an Item and Key. Here you specify your array and key
You can access the array in x and y using indexing

edited Feb 13, 2016 at 11:23

answered Feb 13, 2016 at 11:04

Alex P

12.5k7 gold badges55 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2115136 Over a year ago

thanks, this is good for creating a list of x y coordinates, but when I try to manipulate the values, nothing happens, for example placing Coords.Item("x")(0) = Coords.Item("y")(1) before debug.print to change the print to 4, 2

Porcupine911 Over a year ago

You can't directly edit the value of an item in a collection. See this answer for help.

Guest · Accepted Answer · 2018-05-17 13:43:34Z

'To determine if a multi-dimension array is allocated (or empty)
'Works for any-dimension arrays, even one-dimension arrays
Public Function isArrayAllocated(ByVal aArray As Variant) As Boolean

On Error Resume Next
isArrayAllocated = IsArray(aArray) And Not IsError(LBound(aArray, 1)) And LBound(aArray, 1) <= UBound(aArray, 1)
Err.Clear: On Error GoTo 0

End Function

'To determine the number of dimensions of an array
'Returns -1 if there is an error
Public Function nbrDimensions(ByVal aArray As Variant) As Long
Dim x As Long, tmpVal As Long

If Not IsArray(aArray) Then
    nbrDimensions = -1
    Exit Function
End If

On Error GoTo finalDimension
For x = 1 To 65536 'Maximum number of dimensions (size limit) for an array that will work with worksheets under Excel VBA
    tmpVal = LBound(aArray, x)
Next x

finalDimension:
nbrDimensions = x - 1
Err.Clear: On Error GoTo 0

End Function

'*****************************************************************************************************************************
'To return an array containing al the coordinates from a specified two-dimension array that have the searched item as value
'Returns an empty array if there is an error or no data
'Returns coordinates in the form of x,y
'*****************************************************************************************************************************
Public Function makeArrayFoundXYIn2DimArray(ByVal itemSearched As Variant, ByVal aArray As Variant) As Variant
Dim tmpArr As Variant, x As Long, y As Long, z As Long

tmpArr = Array()
If IsArray(aArray) Then
    If isArrayAllocated(aArray) And nbrDimensions(aArray) = 2 Then
        z = 0
        For x = LBound(aArray, 1) To UBound(aArray, 1)
            For y = LBound(aArray, 2) To UBound(aArray, 2)
                If itemSearched = aArray(x, y) Then
                    If z = 0 Then
                        ReDim tmpArr(0 To 0)
                    Else
                        ReDim Preserve tmpArr(0 To UBound(tmpArr) + 1)
                    End If
                    tmpArr(z) = CStr(x) + "," + CStr(y)
                    z = z + 1
                End If
            Next y
        Next x
    End If
End If
makeArrayFoundXYIn2DimArray = tmpArr
Erase tmpArr

End Function

Collectives™ on Stack Overflow

Wanting to create an array of coordinates in excel VBA

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related