+ Reply to Thread
Results 1 to 5 of 5

UDF - How to define a variable range?

Hybrid View

  1. #1
    Registered User
    Join Date
    12-06-2012
    Location
    Brasil
    MS-Off Ver
    Excel 2010
    Posts
    12

    UDF - How to define a variable range?

    I'm trying to write a UDF that calculates the slope between 2 variable ranges. But it stucks when I declare the ranges
    Whats wrong with this?


    Function varc(ticker)
    
    Dim rang1, rang2 As Range
    
    Set cart = Sheets("CARTEIRA")
    Set ret = Sheets("Retorno")
    'ticker = "ITUB4"
    
    uLinha1 = cart.Range("A2").End(xlDown).Row
    uLinha2 = ret.Range("A1").End(xlDown).Row
    
    a = Application.WorksheetFunction.Match(ticker, ret.Range("1:1"), 0)
    
    rang1 = cart.Range(Cells(uLinha1 - 20, 31), Cells(uLinha1, 31))   <=======
    rang2 = ret.Range(Cells(uLinha2 - 20, a), Cells(uLinha2, a)) <========
    
    slp = Application.WorksheetFunction.Slope(rang2, rang1)
    partc = ActiveCell.Offset(0, -4)
    
    varc = slp * partc
    
    End Function
    Thanks!

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,416

    Re: UDF - How to define a variable range?

    Because you want rang1 and rang2 to be object/range variables, I believe you need to use the Set statement to assign them (note that only rang2 is dimensioned as a range, rang1 is dimensioned as a variant).

    If this were my UDF, I'd try to pass the desired ranges to the function through the argument list.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    12-06-2012
    Location
    Brasil
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: UDF - How to define a variable range?

    Just the rang2 = ret.Range(Cells(uLinha2 - 20, a), Cells(uLinha2, a)) is not working... i don't understand why

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: UDF - How to define a variable range?

    Try this.
    Option Explicit
    
    Function varc(ticker)
    Dim cart As Worksheet
    Dim ret As Worksheet
    Dim rang1 As Range
    Dim rang2 As Range
    Dim uLinha1 As Long
    Dim uLinha2 As Long
    Dim a As Variant
    Dim slp As Variant
    Dim partc As Variant
    
        Set cart = Sheets("CARTEIRA")
        Set ret = Sheets("Retorno")
        'ticker = "ITUB4"
    
        uLinha1 = cart.Range("A2").End(xlDown).Row
        uLinha2 = ret.Range("A1").End(xlDown).Row
    
        a = Application.Match(ticker, ret.Range("1:1"), 0)
    
        With cart
            Set rang1 = .Range(.Cells(uLinha1 - 20, 31), .Cells(uLinha1, 31))
        End With
        With ret
            Set rang2 = .Range(.Cells(uLinha2 - 20, a), .Cells(uLinha2, a))
        End With
    
        slp = Application.Slope(rang2, rang1)
        partc = ActiveCell.Offset(0, -4)
    
        varc = slp * partc
    
    End Function
    If posting code please use code tags, see here.

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: UDF - How to define a variable range?

    you should really make that function volatile if you don't follow MrShorty's advice (which would be better!)
    Josie

    if at first you don't succeed try doing it the way your wife told you to

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1