×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Build Range from array2

## 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!!

### RE: Build Range from array

I'm totally confused by what the macros are doing, and how they are doing it, but in answer to the question:

#### CODE

ReDim myarray(1 To NumArrayRows, 1 To NumArrayColumns)

' 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

I don't really understand what you want. Could you explain in simpler terms...something like "What are the Inputs" and "What are the Outputs"...Is one of these functions supposed to be calling the other?

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 rngTabOrder As Variant

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

(OP)
IDS and brengine,

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
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

IDS

brengine

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!