How is it this does not work? Function ParseLocation(strSourceText As String) As Variant() according to this, it should.
How in the world is this Argument is not optional?
Private Function ParseLocationText(strSourceText As String)
' Have already stepped through this and 'it works'
End Function
Function ParseLocation(strSourceText As String)
Dim Values(5) As Variant
Values = ParseLocationText(strSourceText)
ParseLocation = Values
End Function
I have tried with and without declaring Values to no avail.
Okay finally got at least some result, a step forward. Returns a Double() or Variant(). But that is only setting the first value in a single Cell. I want to populate a target Cell Range, preferably for the row from which the source test is drawn.
I did start the post out with the intention of leaning into the python side. Possibly looking at the Pythonista to help with that, I’m not sure.
Just from a BASIC perspective, how do I convey a cell range to the Function? Or a Sub, or whatever. And/or return with the same.
This is a step forward, and the Cells appear to be addressed properly by row and column, but the result is still displeasing, the values are not landing properly in the sheet.
Function ParseLocation(ByVal strSourceText As String, oDestRange As Range)
Dim Values() As Variant
Values = ParseLocationText(strSourceText)
Dim oSheet As Object
Dim oCell As Object
oSheet = ThisWorkbook.ActiveSheet
Dim I, Row, Column As Integer
For I = LBound(Values) To UBound(Values)
Row = oDestRange.Row
Column = oDestRange.Column + I
oCell = oSheet.Cells(Row, Column)
oCell.Value = Values(I)
Next I
ParseLocation = ArraySize(Values)
End Function
Note, I am also, FWIW, which appears to have some impact on the Sheet and Cell classes involved:
Option Explicit
Option VBASupport 1
Only the first and either the last value or returned size are populated.
| survey | x | y | z | pitch | yaw | roll | ||
|---|---|---|---|---|---|---|---|---|
| {X=1 Y=2 Z=|P=4 Y=5 R=6} | 1 | 6 | 6 |
Have verified the parsing is correct should be six values in all.
https://docs.libreoffice.org/sc/html/classScVbaWorksheet.html
https://docs.libreoffice.org/sc/html/classScVbaRange.html
This appears to work, but man getting there, one climb after the next.
Dim Values() As Variant
Dim Size As Integer
Values = ParseLocationText(strSourceText)
Size = ArraySize(Values)
Dim oSheet As Object
Dim oCell As Object
Dim oStartCell As Object
Dim oEndCell As Object
Dim oRange As Object
oSheet = ThisWorkbook.ActiveSheet
Dim Row As Integer
Dim StartColumn As Integer
Dim EndColumn As Integer
Row = oDestRange.Row
StartColumn = oDestRange.Column
EndColumn = StartColumn + Size - 1
oStartCell = oSheet.Cells(Row, StartColumn)
oEndCell = oSheet.Cells(Row, EndColumn)
oRange = oSheet.Range(oStartCell, oEndCell)
oRange.Value = Values
Again, somewhat if not entirely dependent upon: Option VBASupport 1.
Here instead of iterating I work back to the ranges, cells, and set the values to the array result. And voila, the range lands in the cells as expected.
And with that, still there may be some room for Pythonista analytics forward from this breakthrough.