Some help needed on arrays

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,222
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi people

Can anyone of you lovely people help me understand why and when to use transpose with arrays vbvba and what people mean by you can only redim 2nd dimention


just a small data set with some examples would really be appreciated

thank you
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Does this help?

Code:
    Dim av As Variant
    
    ReDim av(1 To 10, 1 To 5)
    

    ' this won't work with the Preserve keyword:
    ReDim Preserve av(1 To 20, 1 To 5)
    

    ' this will, but it's awkward:
    av = WorksheetFunction.Transpose(av)
    ReDim Preserve av(1 To 5, 1 To 20)
    av = WorksheetFunction.Transpose(av)
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,222
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Sorry I don't quite get that explanation :(
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

It shows that you can't change the first dimension of a 2D array, and then how you can work around that by transposing, changing the second dimension, and then transposing again.

Look at the array in the Locals window while stepping through the code.
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,222
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi


can you redim 1st dim without the preserve keyword or is it a case of you can't redim 1st dim full stop with or without preserve?


thanks


ps I know sometimes we use transpose with the join function


How would you transpose a 2d to 1d to use join ?


i just wanted an example to when we use transpose with join function


thanks
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

It shows that you can't change the first dimension of a 2D array, ..
Sorry, that should read 'anything but the first'
can you redim 1st dim without the preserve keyword ...
Yes, sure, any and all dimensions.

ps I know sometimes we use transpose with the join function. How would you transpose a 2d to 1d to use join? I just wanted an example to when we use transpose with join function
I think you mean Split. Step through this:

Code:
    Dim v as variant
    

    ' v is (0 to 2) (Split always returns a 0-based array)
     v = Split("a b c")
    

    ' which writes as a row vector:
    Range("A1:C1").Value = v
    Cells.ClearContents
    

    ' this writes three copies:
    Range("A1:C3").Value = v
    Cells.ClearContents
   

    ' this converts v to (1 to 3, 1 to 1)
    v = WorksheetFunction.Transpose(v)
    

    ' which writes as a column vector:
    Range("A1:A3").Value = v
    Cells.ClearContents
    

    ' this writes three copies:
    Range("A1:C3").Value = v
    Cells.ClearContents
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,222
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

If I had a 2d or 3d array and I had (dim arr (1 to 2, 1 to 3, 1 to 6) and I did ubound(arr) what result do I get because I did not specify dimension ie ubound(arr,2)
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,222
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Would really appreciate your help SHG
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You will get 2. If you don't specify which dimension UBound refers to, it defaults to the first one.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,652
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top