Setting Chart Axis Properties via UDF - Questions

[jacobrcotton]

Hi Everyone,

I'm trying to set the X & Y Axes, Primary & Secondary mins and maxs (and other general chart properties) through a UDF, which i can then use as a simple formula within my workbook. I've run across some code and i'm trying to break it down using simple data, then build it back up so know exactly what its doing and why its breaking.

The bulk of my original code was jacked via Excel Off The Grid (credit). Code below, my questions are:

(1) This code works great for my Y-axis, but it doesn't work at all for my X-axis (the X-axis is also numeric, between 1 - 10). I haven't a clue why not.
(2) For some reason, when i enter my function into a worksheet cell, i get a #Value! error when i use an XorYAxis input of "X". But if i use "x", the function completes, but like in (1) above, the X-Axis isn't working.

My data is a simple table of X = 1->10, Y = X*10. So line 1 is X=1, Y = 10; X=2, Y=20,...,X=10, Y=100.

My slimmed down code:

Option Explicit

Function setChartAxis(sheetName As String, _
chartName As String, _
XorYAxis As String, _
PrimOrSec As String, _
MinOrMax As String, _
Value As Double)

'Create variables
Dim cht As Chart
Dim valueAsText As String

'Set the chart to be controlled by the function
Set cht = Application.Caller.Parent.Parent.Sheets(sheetName).ChartObjects(chartName).Chart

'Set X-axis Min&Max
If XorYAxis = "X" And PrimOrSec = "Prim" Then
    With cht.Axes(xlCategory, xlPrimary)
        If MinOrMax = "Min" Then .MinimumScale = Value
        If MinOrMax = "Max" Then .MaximumScale = Value
    End With
End If

'Set Y-axis Min&Max
If XorYAxis = "Y" And PrimOrSec = "Prim" Then
    With cht.Axes(xlValue, xlPrimary)
        If MinOrMax = "Min" Then .MinimumScale = Value
        If MinOrMax = "Max" Then .MaximumScale = Value
    End With
End If

setChartAxis = XorYAxis & " " & PrimOrSec & " " & MinOrMax & ": " & Value

End Function

(2i)

=setChartAxis("Sheet1","Chart 1","X","Prim","Max",40)

Returns: #Value!

(2ii)

=setChartAxis("Sheet1","Chart 1","x","Prim","Max",40)

Returns last line of code: "x Prim Max: 40"

 

[NdNoviceHlp]

A couple of suggestions...

Set cht = Application.Caller.Parent.Parent.Sheets(sheetName).ChartObjects(chartName).Chart
Set cht = Sheets(sheetName).ChartObjects(chartName).Chart

Value is an excel term....

Value As Double
Valu As Double

Not real sure what you're doing here but...

'Set X-axis Min&Max
If XorYAxis = "X" And PrimOrSec = "Prim" Then
    With cht.Axes(xlCategory, xlPrimary)
        If MinOrMax = "Min" Then .MinimumScale = Value
        If MinOrMax = "Max" Then .MaximumScale = Value
    End With
Else
'WHAT happens here?
End If

HTH. Dave

 

[jacobrcotton]

Thanks for the assist. Unfortunately, nothing changes.

(1) change the set cht is likely more efficient, but i didn't change that piece of the code from the original. Changing it does not make a difference in the current results.
(2) changing Value to Valu is an easy update, but does not change the results.
(3) ELSE nothing. It will end that IF Stmt and go to the next, which is "Y" and "Primary".

 

[NdNoviceHlp]

The "Else" part is that you still need to set both axis for both/all conditions.
eg.

'Set Y-axis Min&Max
If XorYAxis = "Y" And PrimOrSec = "Prim" Then
    'set y axis
    With cht.Axes(xlValue, xlPrimary)
        If MinOrMax = "Min" Then .MinimumScale = Valu
        If MinOrMax = "Max" Then .MaximumScale = Valu
    End With
    'set x axis
    With cht.Axes(xlCategory, xlPrimary)
        If MinOrMax = "Min" Then .MinimumScale = Valu
        If MinOrMax = "Max" Then .MaximumScale = Valu
    End With
End If

The Valu is the same for everything? Perhaps I'm not understanding how you intent to use this udf? Dave

 

[jacobrcotton]

I think i am having a fundamental misunderstanding. Which is great! because i can learn!

Valu is NOT the same for everything.

In this case, we can use the same FUNCTION to set the XAxis Primary Minimum to 0 (0 is the Valu), and we can use the same function to set the Yaxis Primary Maximum to 400.

 

[NdNoviceHlp]

Okay I think I understand what you're doing however there is no indication/code what happens when you want an axis to be secondary. This code works in straight VBA to adjust the axis but it seems you're trying to adjust it with a worksheet UDF? Anyways, this code trials OK with straight VBA. HTH. Dave

Function setChartAxis(sheetName As String, _
chartName As String, _
XorYAxis As String, _
PrimOrSec As String, _
MinOrMax As String, _
Valu As Double)

'Create variables
Dim cht As Chart
Dim valueAsText As String

'Set the chart to be controlled by the function
Set cht = Sheets(sheetName).ChartObjects(chartName).Chart

'Set X-axis Min&Max
If XorYAxis = "X" And PrimOrSec = "Prim" Then
With cht.Axes(xlCategory, xlPrimary)
If MinOrMax = "Min" Then .MinimumScale = Valu
If MinOrMax = "Max" Then .MaximumScale = Valu
End With
End If

' Y-axis Min&Max
If XorYAxis = "Y" And PrimOrSec = "Prim" Then
With cht.Axes(xlValue, xlPrimary)
If MinOrMax = "Min" Then .MinimumScale = Valu
If MinOrMax = "Max" Then .MaximumScale = Valu
End With
End If
End Function

To operate for Chart 1 on Sheet1....

'adjust X
Call setChartAxis("Sheet1", "Chart 1", "X", "Prim", "Max", 30)
Call setChartAxis("Sheet1", "Chart 1", "X", "Prim", "Min", 0)
'Adjust Y
Call setChartAxis("Sheet1", "Chart 1", "Y", "Prim", "Max", 20)
Call setChartAxis("Sheet1", "Chart 1", "Y", "Prim", "Min", 0)

 

[jacobrcotton]

Hey Dave, you're absolutely correct. I'm trying to adjust the axes using a UDF. And with your help, i think i have it working! My "final" code is below, and i've added an image with a very rudimentary set of a data, chart, and the UDFs as kind of a "proof of concept" before i implement this into something more complex.

I'm setting this to "Answered", and i appreciate your assistance!

NOTES:
(1) the new code on line 16, setting the CategoryType to xlTimeScale.
(2) in the two images attached, Image 01 shows a practical set of Min/Max scales. In Image 2, I put in wildly impractical scales "just to see what would happen." It turns out, it does exactly what i'm telling it to do!

Option Explicit

Function setChartAxis(sheetName As String, _
chartName As String, _
XorYAxis As String, _
PrimOrSec As String, _
MinOrMax As String, _
Valu As Double)

'Create variables
Dim cht As Chart
Dim valueAsText As String

'Set the chart to be controlled by the function
Set cht = Sheets(sheetName).ChartObjects(chartName).Chart
cht.Axes(xlCategory).CategoryType = xlTimeScale

'Set X-axis Min&Max
If XorYAxis = "X" And PrimOrSec = "Prim" Then
    With cht.Axes(xlCategory, xlPrimary)
        If MinOrMax = "Min" Then .MinimumScale = Valu
        If MinOrMax = "Max" Then .MaximumScale = Valu
    End With
End If

' Y-axis Min&Max
If XorYAxis = "Y" And PrimOrSec = "Prim" Then
    With cht.Axes(xlValue, xlPrimary)
        If MinOrMax = "Min" Then .MinimumScale = Valu
        If MinOrMax = "Max" Then .MaximumScale = Valu
    End With
End If
If XorYAxis = "Y" And PrimOrSec = "Sec" Then
    With cht.Axes(xlValue, xlSecondary)
        If MinOrMax = "Min" Then .MinimumScale = Valu
        If MinOrMax = "Max" Then .MaximumScale = Valu
    End With
End If

setChartAxis = XorYAxis & " " & PrimOrSec & " " & MinOrMax & ": " & Valu

End Function

A3 formula:

=setChartAxis("Sheet1","Chart 1","X","Prim","Min",B3)

 

[NdNoviceHlp]

Well done as I wasn't sure that it was possible. Thanks for posting your outcome. Dave