×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Build Range from array
2

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

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

thumbsup2medal IDS

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

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close