UDF - How to define a variable range?

[irisqueiroz]

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?

[pre]

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

[/pre]
Thanks!

 

[Luke M]

First, you really should define all your variable (the uLinha1, uLinha2, a, slp, partc, varc). It's good practice, and helps make sure VB assigns things like you want it to.


Now, as to why it won't work. Taking a close look at this line

rang1 = cart.Range(Cells(uLinha1 - 20, 31), Cells(uLinha1, 31))


Note that we start of saying to look at the cart.Range object. This lets us know what sheet we're working on. Hoever, and this is the key, the Cells objects inside don't have a parent worksheet. So, VB is assuming you want to use the active sheet as parent. Since the activesheet cells aren't necessarily on the cart worksheet, you error out.

Alternative way to write your code:

[pre]Function varc(ticker)

Dim rang1 As Range, rang2 As Range
Dim uLinha1 As Long, uLinha2 As Long
Dim cart As Worksheet
Dim ret As Worksheet
Dim a As Long

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)

With cart
rang1 = .Range(.Cells(uLinha1 - 20, 31), .Cells(uLinha1, 31)) '  <=======
End With
With ret
rang2 = .Range(.Cells(uLinha2 - 20, a), .Cells(uLinha2, a)) ' <========
End With

slp = Application.WorksheetFunction.Slope(rang2, rang1)
partc = ActiveCell.Offset(0, -4)

varc = slp * partc

End Function

[/pre]

 

[irisqueiroz]

Thank you!


It worked

 

[Luke M]

Hurrah, glad I could help. =)

 

[SirJB7]

Hi, irisqueiroz!

Adding to Luke M's suggestion, I'd set Excel to require variable declaration: from the VBA editor (Alt-F11), Tools, Options, 1st tab Editor, and check Require Variable Declaration check box. This will automatically add an "Option Explicit" first line to every module, worksheet or user form code section. It applies only to new objects so each time you modify one created before this setting you'd have to manually add it, which is advisable.

Regards!