Results 1 to 2 of 2
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Salt Lake City, Utah, USA
    Thanked 5 Times in 5 Posts

    Addressing 3D Ranges in a Function

    Following this <A target="_blank" HREF=>SUMIFACROSS SHEETS</A> thread:

    Sub Select3D()
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    MsgBox Selection.Address(False, False, xlA1, True, "A1")
    End Sub

    selects a 3D range, BUT but how do I feed such a range directly in to a 3D Function in order to restrict a function to only selected sheets, where the sheets and ranges can be selected in point mode such that inRange3D is Sheet1:Sheet3!C8:K14? All the following seem to be invalid, where the --or-- separates alternative FUNCTION inputs I have tried:

    Function SheetsRange(inRange3D As Range) As Variant
    Function SheetsRange(inRange3D() As Range) As Variant
    Function SheetsRange(inRange3D As Object) As Variant
    Function SheetsRange(inRange3D() As Object) As Variant
    Function SheetsRange(inRange3D As Worksheets) As Variant
    Function SheetsRange(inRange3D() As Worksheets) As Variant
    MsgBox TypeName(inRange3D)
    End Function

    (Yes, it works as a string
    Function strSheetsRange(strInRange3D As String) As String
    strSheetsRange = Right(strInRange3D, Len(strInRange3D) - InStr(1, strInRange3D, "]"))
    End Function

    for =strSheetsRange("[3DFUNCTIONS.XLS]Sheet1:Sheet3!C8:K14"), but that's not the objective.)

    Excel handles 3D syntax in =SUM90 and a number of other functions, so I figure there must be a way ... <img src=/S/help.gif border=0 alt=help width=23 height=15> <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Springfield, Ohio, USA
    Thanked 1 Time in 1 Post

    Re: Addressing 3D Ranges in a Function

    VBA dosn't do 3D ranges and work-arounds like passing two ranges or a string are really ugly. Laurent Longre has a free
    addin that dramatically extends your ability to use 3D references. I don't know if it will extend VBA's utility, but if you do 3D, it is worth a download. It has a well documented help file and provides more than 20 useful functions. Check it out at <A target="_blank" HREF=></A>. Have fun! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts