Build Range from array
Build Range from array
(OP)
Hello! I have a program embedded in an excel workbook that controls the taborder of a worksheet. The macro which controls the taborder is in the ThisWorkbook object, while the function telling the macro the order of the range is embedded in the Worksheet itself. The function began like this:
Public Function TabOrder() As Range
Set TabOrder = [A1,B1,C1]
End Function
The worksheet this function is in has both merged and unmerged cells, and I only wanted to capture the unmerged cells along with the first cell of each merged area. I tweaked the code thus:
Public Function TabOrder() As Range
Dim Tabulate() As Variant
InfoArr = Array("C3", "D5", "D7", "F7", "J2")
BOMArr = Array("B", "C", "D", "G") 'D merged w/ E & F
ArrBOM = Array("K", "L", "O", "P") 'L merged w/ M & N
For r = 0 To 5
v = v + 1
ReDim Preserve Tabulate(1 To v)
Set Tabulate(v) = InfoArr(r)
Next r
For s = 1 To 2
For t = 1 To 30
For u = 0 To 3
v = v + 1
ReDim Preserve Tabulate(1 To v)
If s = 1 Then
Set Tabulate(v) = BOMArr(u) & t + 10
ElseIf s = 2 Then
Set Tabulate(v) = ArrBOM(u) & t + 10
End If
Next u
Next t
Next s
Set TabOrder = [Tabulate]
End Function
However, when I try to set the TabOrder range, it errors out. Is there a way to put an array of cell references into a range? I haven't been able to find anything specific to this in VBA Help or online.
Thanks!!
Public Function TabOrder() As Range
Set TabOrder = [A1,B1,C1]
End Function
The worksheet this function is in has both merged and unmerged cells, and I only wanted to capture the unmerged cells along with the first cell of each merged area. I tweaked the code thus:
Public Function TabOrder() As Range
Dim Tabulate() As Variant
InfoArr = Array("C3", "D5", "D7", "F7", "J2")
BOMArr = Array("B", "C", "D", "G") 'D merged w/ E & F
ArrBOM = Array("K", "L", "O", "P") 'L merged w/ M & N
For r = 0 To 5
v = v + 1
ReDim Preserve Tabulate(1 To v)
Set Tabulate(v) = InfoArr(r)
Next r
For s = 1 To 2
For t = 1 To 30
For u = 0 To 3
v = v + 1
ReDim Preserve Tabulate(1 To v)
If s = 1 Then
Set Tabulate(v) = BOMArr(u) & t + 10
ElseIf s = 2 Then
Set Tabulate(v) = ArrBOM(u) & t + 10
End If
Next u
Next t
Next s
Set TabOrder = [Tabulate]
End Function
However, when I try to set the TabOrder range, it errors out. Is there a way to put an array of cell references into a range? I haven't been able to find anything specific to this in VBA Help or online.
Thanks!!





RE: Build Range from array
CODE
' Fill array
With Range("MyNamedRange")
.ClearContents
.Resize(NumArrayRows, NumArrayColumns).Name = "MyNamedRange"
End With
Range("MyNamedRange").Value2 = myarray
So in short, create a named range in the spreadsheet then:
Range("rangename").Value2 = array
will do what you want.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Build Range from array
Some things that jump out at me...
(1) You can't have 2 Public Procedures with the same name.
(2) You can't use the '[' or ']' brackets.
Here is some code that does works...maybe that will help
CODE
Public Sub TabOrder()
rngTabOrder = fTabOrder
End Sub
Private Function fTabOrder() As Variant
Dim sArrTabulate() As String 'Array of Cell Addresses
Dim rngArrTabulate() As Range 'Array of Ranges
Dim sTabulate As String 'Comma-Delimited Cell Addresses
Dim rngTabulate As Range 'Range of Cells
Dim r As Integer
Dim s As Integer
Dim t As Integer
Dim u As Integer
Dim v As Integer
Dim InfoArr As Variant
Dim BOMArr As Variant
Dim ArrBOM As Variant
'Set Variables Here
InfoArr = Array("C3", "D5", "D7", "F7", "J2")
BOMArr = Array("B", "C", "D", "G") 'D merged w/ E & F
ArrBOM = Array("K", "L", "O", "P") 'L merged w/ M & N
'Initial Values
sTabulate = ""
v = 0
For r = 0 To 4
v = v + 1
ReDim Preserve sArrTabulate(v)
sArrTabulate(v) = InfoArr(r)
ReDim Preserve rngArrTabulate(v)
Set rngArrTabulate(v) = Range(InfoArr(r))
If (sTabulate <> "") Then
sTabulate = sTabulate & "," & InfoArr(r)
Else
sTabulate = InfoArr(r)
End If
Next r
For s = 1 To 2
For t = 1 To 30
For u = 0 To 3
v = v + 1
ReDim Preserve sArrTabulate(v)
ReDim Preserve rngArrTabulate(v)
If s = 1 Then
sArrTabulate(v) = Replace(BOMArr(u) & Str(t + 10), " ", "")
Set rngArrTabulate(v) = Range(Replace(BOMArr(u) & Str(t + 10), " ", ""))
If (sTabulate <> "") Then
sTabulate = sTabulate & "," & Replace(BOMArr(u) & Str(t + 10), " ", "")
Else
sTabulate = InfoArr(r)
End If
ElseIf s = 2 Then
sArrTabulate(v) = Replace(ArrBOM(u) & Str(t + 10), " ", "")
Set rngArrTabulate(v) = Range(Replace(ArrBOM(u) & Str(t + 10), " ", ""))
If (sTabulate <> "") Then
sTabulate = sTabulate & "," & Replace(ArrBOM(u) & Str(t + 10), " ", "")
Else
sTabulate = InfoArr(r)
End If
End If
Next u
Next t
Next s
'Which one?
fTabOrder = sArrTabulate 'Array of Cell Addresses
fTabOrder = rngArrTabulate 'Array of Ranges
fTabOrder = sTabulate 'Comma-Delimited Cell Addresses
If v < 60 Then 'Below line unreliable if more than 60 cells need to be in the 'Range'.
Set fTabOrder = Range(sTabulate) 'Range of Cells
End If
End Function
RE: Build Range from array
Thanks for your input, I do appreciate it. I got the code originally by Googling "Excel Tab Order" and found the post "Excel Tab Order - special request!" on experts-exchange.com. (Will I be banned for mentioning another forum?) Anyway, you can see the original code in its context and entirety there. The problem was that the code did not allow for tabbing through merged cells. I contacted the originator of the code and he was gracious enough to help me fix my code to do what it needs to do.
brengine, just to give a little clarification, the two procedures have the same name because they are the same procedure, with the second being the modified version of the first. Also, excel vba code can use brackets when calling out a range--its like a shortcut I guess.
Hence, you will need to check out the code on the other forum to see what I am talking about. In the EnableTabbing Sub in the ThisWorkbook Object, the Save tab sequence was modified from:
' Save tab sequence
ReDim mTabOrder(1 To TabOrder.Cells.Count)
Index = 1
For Each Cell In TabOrder
Set mTabOrder(Index) = Cell
Index = Index + 1
Next Cell
To:
' Save tab sequence
mTabOrder = Array()
For Each Cell In TabOrder
If Cell.Address = Cell.MergeArea(1, 1).Address Then
ReDim Preserve mTabOrder(LBound(mTabOrder) To UBound(mTabOrder) + 1)
Set mTabOrder(UBound(mTabOrder)) = Cell
End If
Next Cell
And the TabOrder Function in the Worksheet was changed to:
Public Function TabOrder() As Range
Set TabOrder = [C3,D5,D7,F7,J2,B11:G40,K11:P40]
End Function
So whether or not the array has merged or unmerged cells in it, it will tab forwards and backwards appropriately. I hope that this answers your confusion and I again apologize for not being clearer to begin with. Thank you once again very much for trying to help me!!
smma