-5

While it is quite easy to define a two-dimensional data structure (data frame) in Python or R, is there any alternative concept to define such a structure in VBA Excel?

update:

The reason I asked such a question is that I wanted to load data from multiple CSV files (without importing those CSV files) into VBA code like two-dimensional arrays to do some manipulation and processing. Hence it is easy to import data from CSV files into an Excel sheet, I am thinking this method is not very efficient for my case. Since I am more familiar with R, I asked this question from an R programmer's perspective. I hope you get the idea.

Mohammad
  • 41
  • 7
  • Yes you can do that. – freeflow May 27 '23 at 13:31
  • 1
    What are the specific requirements that you place on such a data structure? An example with some dates would be very helpful as not all Excel users - myself included - are familiar with Python dataframes. @Mohammad – T.M. May 27 '23 at 14:43
  • Relatively simple with the New `MS365` formulas --> `=WRAPROWS(TOROW(A1:B5,,1),5)` Or, `=WRAPROWS(TOCOL(A1:B5,,1),5)` formula based on the example shown in `Answers` – Mayukh Bhattacharya May 27 '23 at 23:31
  • @T.M. Thank you for your response, I have added a few details in the body of the question. – Mohammad May 28 '23 at 09:34

1 Answers1

3

I don't know Python or R, but two-dimentions is the heart of excel. The two-dimentional sheets can exported to arrays and i think the "two-dimensional data structure" is a two-dimentional array.

Sub twoDimentions()
   Dim data() As Variant  'array to hold the two dimentional range
   
   data = Range("A1:B5")
   'the data(1,2) is 10

   'transpose the dimentions (rows=>columns, columns=>rows)
   data = Application.WorksheetFunction.Transpose(data)
   'the data(1,2) is 2
   'copy the transposed data at position A7
   'but first resize the A7 to the same dimentions limits of the data
   Range("A7").Resize(UBound(data, 1), UBound(data, 2)) = data
End Sub

enter image description here

In my example I created a 2D array starting from an area of the sheet. Of course, the opposite can also be done: that is, to create an array of arrays and then transfer the data to the sheet either by rows or by columns. I have posted this way in another question and refer you to this exact answer: https://stackoverflow.com/a/76348074/15794828

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ
  • 1,658
  • 1
  • 4
  • 14
  • 2
    As a possibly interesting addition to your answer, which deals with handling data fields and Excel ranges, a not so well-known alternative, how not too extensive 2D arrays could be generated directly from "flat" 1D arrays (see also caveats): [Write an array of arrays to a range](https://stackoverflow.com/questions/73058611/write-an-array-of-arrays-to-a-range-vba/73069413#73069413) – T.M. May 27 '23 at 15:03
  • Thanks for the tip @T.M, Read it and i'll give an answer to that question. – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ May 27 '23 at 16:43
  • Thanks for response; already read & 'vt answer last week @ΑΓΡΙΑ-ΠΕΣΤΡΟΦΑ – T.M. Jun 03 '23 at 16:49