Function in VBA

[Civilr Star]

Can a function that references another sheet work? Say if I have 2 inputs that are referenced on sheet 1 and a range on sheet 2 and the function was to output the sum of the range bound by the 2 inputs; would that work?

 

[RoryA]

Yes, it can.

 

[Civilr Star]

I am trying to get a custom function to work in the =series() for a dynamic range for charting. I have 2 inputs (zoom and pan) that will be controlled on the sheet. The DBase is on the separate worksheet. Do you have an example of how I can do this?

Thanks!

 

[RoryA]

I don't really follow what you are after. Typically, you would use a dynamic named range for charting rather than putting custom functions in the SERIES formula.

 

[Civilr Star]

Ok,

Objective: To create a dynamic range for charting that uses 2 form control scroll bars (pan & zoom).

Inputs: Pan value (cell linked), Zoom value (cell linked), offset (user input), range of data (user input range of cells on another sheet)

Output: The range of data that is controlled by the pan and zoom displayed on a chart.

What I've done: I created some simple nested if statements to limit the data so that when the range of data exceeds the max or min number of values the zoom and pan values are restricted. Then put that back into the range as the output for the formula. I wanted to put this in a Function procedure so that I can call it using named ranges then put that directly into the series formula for the chart.

Current Output: #Value! and a reference error when the sheet recalculates.

What I think the Problem is: I dont think I am the input values are in the argument list are correct.

Code:
Function dynChart(P As Double, Z As Double, Off As Double, Rng As Range) As String

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Created: 03/07/2011 7:50a '
'Purpose: to create a function that will allow the use of dynamic Rang'
' e easily '
'Created by: Hans Tremmel '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim RowCnt As Double, Max As Double, Min As Double
Dim Ref As Double
Dim Temp As Range
Dim Msg As String

'Error Check: Check to make sure only 1 column in Rng Selected
'Error Check: that all values are accounted for
'Rng.Activate

'03/07 Tried to go with array way of do things. Reference 293 and try to pick it up tomorrow.
RowCnt = UBound(Rng())
x = Round(P / 100 * RowCnt, 0)
y = Round(Z / 100 * RowCnt, 0)
If x - y / 2 <= 0 Then
Min = 1
Max = y - x + 1
Else
If x + y / 2 >= RowCnt Then
Max = RowCnt
Min = RowCnt - y
Else
Min = x - y / 2
Max = x + y / 2
End If
End If

Min = Round(Min, 0)
Max = Round(Max, 0)
Worksheets(Rng.Worksheet.Name).Activate

Set Temp = Rng.Range(Cells(Min, 1), Cells(Max, 1)).Offset(0, Off)
Msg = Temp.Address
Msg = Temp.Worksheet.Name & "!" & Msg
dynChart = Msg

End Function

Thanks a lot for your help. I hope you can help me out!

 

[RoryA]

1. You cannot activate sheets in a function.
2. The SERIES formula expects a range, not a string.

 

[Civilr Star]

Thank you very much Rorya. I'll give that a try.