basic loop question

[hfler]

Someone told me I can't have a loop within a VBA function that offsets to different cells from current cells, such as

Function test(startdate As Variant, enddate As Variant) As Variant

..................... 'removed  code
 
    If currmo > ActiveCell And Not IsDate(ActiveCell) Then
        pricejump = Value.Range("frontmth_last").End(xlDown) + price
        price = pricejump
    ElseIf currmo < ActiveCell Then
        pricejump = ActiveCell.Offset(0, 1) + price
        price = pricejump
            If currmo < WorksheetFunction.EDate(currmo, 1) Then
            currmojump = currmo
            Else:
            currmojump = WorksheetFunction.EDate(currmo, 1)
            End If
        currmo = currmojump
    Else:
        Range("front_fstnot").Offset(i, 0).Active
        currmo = ActiveCell

If anyone has any insight, would appreciate some help! Thanks!

 

[shg]

You can, but Excel will see no dependence on the active cell, so it won't update automatically when the selection changes.

Why would you want to?

 

[hfler]

It's okay if it doesn't update automatically, if I can recalculate the cell and have it update. If you're so inclined, I've pasted my entire code below. I should add I'm a bit of a beginner... Understand if you don't want to parse through this, but would appreciate any suggestions. Thank you!

Function test(startdate as variable, enddate as variable) as variable

Dim i As Double
Dim currmo As Double
Dim currmojump As Double
Dim price As Double
Dim pricejump As Double
Dim space As Double
space = -DateDiff("m", enddate, startdate) '+ 1 <<not necessary b/c i starts at 0

Range("front_fstnot").Select
currmo = startdate
For i = 0 To space
    If currmo > ActiveCell And Not IsDate(ActiveCell) Then
        pricejump = Value.Range("frontmth_last").End(xlDown) + price
        price = pricejump
    ElseIf currmo < ActiveCell Then
        pricejump = ActiveCell.Offset(0, 1) + price
        price = pricejump
            If currmo < WorksheetFunction.EDate(currmo, 1) Then
            currmojump = currmo
            Else:
            currmojump = WorksheetFunction.EDate(currmo, 1)
            End If
        currmo = currmojump
    Else:
        Range("front_fstnot").Offset(i, 0).Active
        currmo = ActiveCell
    End If
Next i
price = pricejump / (space + 1)
test = price
 
End function

 

[shg]

I can't tell what your code is supposed to do at all.

 

[Norie]

As far as I'm aware you can't do things like activating/selecting with a UDF.
Are you trying to do that?

 

[hfler]

Sorry, not familiar with "UDF" - but if that's a custom function in VBA, then yes, I'm trying to activate cells using that. Does that not work?

If not, is there a way to create a function that will run a macro if a cell / sheet is refreshed using F9?

 

[Norie]

UDF - user defined function.
If you are using what you posted on a worksheet that's what it is.
If you want code to run on calculation then you can use the worksheet Calculate event.
Can you explain what you are trying to do?