Build Range from array 2

[smma]

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

 

[IDS]

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

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/

 

[brengine]

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

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

 

[smma]

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
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,K1140]
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