Function that returns an array in VBA

[swajas]

Hello all,

I have a function that prints all my data in the sheet to a text file in an array format like following
[
['ID', 'Label', 'Longitude', 'Latitude', 'Country', 'Region', 'Ttl_wt_Flw_thru', 'Sum-of-BOL-Wt', 'Count of WDR_Ref', 'Ratio'],
['AEAUH', 'Abu Dhabi, United Arab Emirates', 54.3666667, 24.4666667, 'AE', 'EAME', 66, 30, 8432, 281.066666666667],
['AEDXB', 'Dubai, United Arab Emirates', 55.307485, 25.271139, 'AE', 'EAME', 682, 3, 8369, 2789.66666666667] ]

Now, my code for writing an array like this is
<code>
Function FillSourceArray()
Dim i As Long, j As Long, s As String
Dim Lastrow As Double
Dim S1 As String

'Opens file
FilePath = FilePath & "DataArray" & ".txt"
Open FilePath For Output As #1
'Read the Source Data
DataArray = Sheets("Nodes").Cells(1).CurrentRegion.Value
'Determine the Lastrow
Lastrow = Sheets("Nodes").Range("A999999").End(xlUp).Row
Print #1, "["
'This "for" loop reads all the data in excel sheet into a string
For i = 2 To UBound(DataArray)
s = "["
For j = 1 To UBound(DataArray, 2)
If IsNumeric(DataArray(i, j)) Then
s = s & DataArray(i, j) & ", "
Else
s = s & "'" & DataArray(i, j) & "', "
End If

Next
If i = Lastrow Then
Print #1, Replace(s & "]", ", ]", "]")
Else
Print #1, Replace(s & "]", ", ]", "],")
End If

Next

Print #1, "]"
Close #1
</code>

My task:::: to read the array into a string and return the value to FillSourceArray()....because I need to call this function from another module..where i want to print the array in this format..
Please help me on this
Thanks in advance

 

[Norie]

Read the array from where?

PS Why do you want to put the array in a string?

 

[swajas]

Thanks for looking at the issue..
I want to read the data in excel sheet and form it into an array(in the format shown above) and return the array to the function FillSourceArray()
Please help
Thanks

 

[Norie]

Why not pass the string the code is creating to the other sub instead of outputting it to a file?

 

[swajas]

Yes, exactly..i dont know how to do that..it might be very simple but i am new to vba..so, please modify my code ..
Thanks for helping me out..

 

[swajas]

Please help me with the function..Thanks

 

[swajas]

I want to read the string into an array,,redim it and finally return the array to the function in the above format..
Anybody, please help me in modifying the code