Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

ExcelChart UDF - Help & Support

Excel Chart Help Support

  • Please log in to reply
111 replies to this topic

#1 water

water

    ?

  • MVPs
  • 15,362 posts

Posted 02 February 2012 - 08:49 AM

This is the "General Help and Support" thread for the ExcelChart UDF.
The UDF itself can be downloaded here.

So if you have any questions, suggestions or errors please post here.
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki







#2 maba

maba

    Seeker

  • Active Members
  • 38 posts

Posted 05 February 2012 - 07:54 PM

Hi Guys,
is there the possibilty to manage "error bars" with this great UDF?
For me error bars are the better solution to achive this:

Posted Image

A scatter chart with a vertical error bar and a label: a "dynamic" vertical bar.
Suggestions?

Thanks!

#3 water

water

    ?

  • MVPs
  • 15,362 posts

Posted 05 February 2012 - 08:53 PM

Hi maba,

not yet.
Can you create the vertical lines with Excel manually? If yes, you could create a macro and we can have alook at the VBA code and can try to translate it to AutoIt.
What version of Excel do you use?
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#4 maba

maba

    Seeker

  • Active Members
  • 38 posts

Posted 05 February 2012 - 09:18 PM

:)
Something like this:
AutoIt         
Sub ErrorBar() ' ' ErrorBar Macro ' '     ActiveSheet.ChartObjects("Grafico 4").Activate     ActiveChart.SeriesCollection(2).Select     ActiveChart.SeriesCollection.NewSeries     ActiveChart.SeriesCollection(11).Name = "=""Error"""     ActiveChart.SeriesCollection(11).XValues = "='Tabelle gestione grafico'!$H$9"     ActiveChart.SeriesCollection(11).Values = "='Tabelle gestione grafico'!$I$9"     ActiveSheet.ChartObjects("Grafico 4").Activate     ActiveChart.SeriesCollection(11).Select     ActiveChart.SeriesCollection(8).HasErrorBars = True     ActiveSheet.ChartObjects("Grafico 4").Activate     ActiveChart.SeriesCollection(11).ErrorBars.Select     ActiveChart.SeriesCollection(11).ErrorBar Direction:=xlY, Include:= _         xlMinusValues, Type:=xlFixedValue, Amount:=1     ActiveChart.SeriesCollection(11).ErrorBars.EndStyle = xlNoCap     ActiveChart.SeriesCollection(11).ErrorBar Direction:=xlY, Include:= _         xlMinusValues, Type:=xlPercent, Amount:=5     ActiveChart.SeriesCollection(11).ErrorBar Direction:=xlY, Include:= _         xlMinusValues, Type:=xlPercent, Amount:=100     ActiveSheet.ChartObjects("Grafico 4").Activate     ActiveChart.SeriesCollection(11).ErrorBars.Select     ActiveSheet.ChartObjects("Grafico 4").Activate     ActiveChart.SeriesCollection(11).Select     ActiveSheet.ChartObjects("Grafico 4").Activate     ActiveChart.SeriesCollection(11).ApplyDataLabels     ActiveSheet.ChartObjects("Grafico 4").Activate     ActiveChart.SeriesCollection(11).DataLabels.Select     Selection.Position = xlLabelPositionCenter End Sub

I think that I can use this for now, should it work.. right?

At least Office 2007!

#5 water

water

    ?

  • MVPs
  • 15,362 posts

Posted 05 February 2012 - 09:36 PM

If you translate it to AutoIt it should work.
ActiveChart.SeriesCollection(11).ErrorBar Direction:=xlY, Include:=xlMinusValues, Type:=xlPercent, Amount:=100
should become something like:
ActiveChart.SeriesCollection(11).ErrorBar($xlY, $xlMinusValues, $xlPercent, 100)
(but I'm not 100% sure - I will have to test it tomorrow)
Can you post a screenshot of the resulting Excel chart? So we can test that we get the same result when we put the error bars into a function of the UDF.
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#6 maba

maba

    Seeker

  • Active Members
  • 38 posts

Posted 05 February 2012 - 10:29 PM

This
Sub Macro6()     Range("A5:B5").Select     ActiveSheet.Shapes.AddChart.Select     ActiveChart.SetSourceData Source:=Range("'Foglio1'!$A$5:$B$5")     ActiveChart.ChartType = xlXYScatter     ActiveChart.SeriesCollection(1).Select     ActiveChart.SeriesCollection(1).HasErrorBars = True     ActiveChart.SeriesCollection(1).ErrorBars.Select     ActiveChart.SeriesCollection(1).ErrorBars.EndStyle = xlNoCap     ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _         xlMinusValues, Type:=xlFixedValue, Amount:=1     ActiveChart.ChartArea.Select     ActiveChart.SeriesCollection(1).ApplyDataLabels End Sub

generates
Posted Image
but macro does not:
  • apply the styles
  • center the label
  • delete the horizontal error bar
Posted Image

Thanks

#7 water

water

    ?

  • MVPs
  • 15,362 posts

Posted 06 February 2012 - 05:16 PM

Not perfect, but I hope it's a good start:
AutoIt         
#include <ExcelChart.au3> ; ***************************************************************************** ; Create example environment ; ***************************************************************************** Global $aExcel = _XLChart_Example(True, 0, -1, -1, -1) If @error = 2 Then Exit MsgBox(16, "Excel Chart Example Script", "The installed Excel version is not supported by this UDF!" & @CRLF & "Version must be >= 12 (Excel 2007).") If @error <> 0 Then Exit MsgBox(16, "Excel Chart Example Script", "Error " & @error & " returned by _XLChart_Example on line " & @ScriptLineNumber) $iXLC_Debug = 2 Global $iTop _ExcelWriteCell($aExcel[0], "", "B2:B5")                                            ; Clear all cells but one _XLChart_SeriesSet($aExcel[1] .SeriesCollection(1), $xlLine)                        ; Remove markers $aExcel[1].SeriesCollection(1).HasErrorBars = True                                    ; Activate Error bars $aExcel[1].SeriesCollection(1).ErrorBars.EndStyle = $xlNoCap                        ; No cap $aExcel[1].SeriesCollection(1).ErrorBar($xlY, $xlMinusValues, $xlPercent, 100)        ; Set error bars $aExcel[1].SeriesCollection(1).ApplyDataLabels()                                    ; Appl $aExcel[1].SeriesCollection(1).DataLabels.Position = 0                                ; Set the data labels above _XLChart_LineSet($aExcel[1].SeriesCollection(1).ErrorBars, 1, 0x4A7EBB)                ; Set line color for the error bar _XLChart_LineSet($aExcel[1].SeriesCollection(1).DataLabels, 2, 0x4A7EBB)            ; Set line color of the data label $iTop = $aExcel[1].SeriesCollection(1).Points(5).DataLabel.Top $aExcel[1].SeriesCollection(1).Points(5).DataLabel.Top = $iTop + 5                    ; Reposition the data label $aExcel[1].SeriesCollection(1).Points(5).DataLabel.Text = "Test"                    ; Set text of a point of the data label

UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#8 maba

maba

    Seeker

  • Active Members
  • 38 posts

Posted 06 February 2012 - 08:02 PM

Ok, as is I have:

>Running AU3Check (1.54.22.0)  from:C:Program Files (x86)AutoIt3 Z:AutoITsamplesErrorBar.au3(15,93) : WARNING: $xlNoCap: possibly used before declaration. $aExcel[1].SeriesCollection(1).ErrorBars.EndStyle = $xlNoCap                        ; No cap ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ Z:AutoITsamplesErrorBar.au3(16,45) : WARNING: $xlY: possibly used before declaration. $aExcel[1].SeriesCollection(1).ErrorBar($xlY, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^


If I declare the vars, script works but I've two "Type mismatch" debug info.. If not styles aren't applied.
Probably not important now.

Bars does not cross X axis, in fact Display -> Direction is set to Both and not Negative and Error Amount is set to Fixed value: 5.0 and not Percentage: 100. Why? ;)

Label is not set correctly.. next step :)

Edited by maba, 06 February 2012 - 08:11 PM.


#9 water

water

    ?

  • MVPs
  • 15,362 posts

Posted 06 February 2012 - 08:39 PM

Sorry, forgot to replace this two constants with their values.
$xlNoCap = 2 and $xlY = 1
I will post a screenshot of what I got with Excel 2010 tomorrow.
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#10 maba

maba

    Seeker

  • Active Members
  • 38 posts

Posted 06 February 2012 - 08:49 PM

Ok, is the right way :)

Posted Image

A little problem with the label.. but it is good. On Excel 2007.

Edited by maba, 06 February 2012 - 08:50 PM.


#11 water

water

    ?

  • MVPs
  • 15,362 posts

Posted 06 February 2012 - 08:59 PM

The positioning of the text is not perfect in Excel 2010 too. We could either play around with changing the "left" property or to change the datalabels.position to center. But in this case the line will end in the center of the box. If we use a filling for the box then the line will be invisible and the text will be centered automatically.
I will check how much of the errorbar properties can be set by a function.
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#12 maba

maba

    Seeker

  • Active Members
  • 38 posts

Posted 06 February 2012 - 09:12 PM

Yes, box filling is perfect for me.
Thanks for your time!

#13 water

water

    ?

  • MVPs
  • 15,362 posts

Posted 06 February 2012 - 09:14 PM

I'm going to post such an example tomorrow.
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#14 water

water

    ?

  • MVPs
  • 15,362 posts

Posted 07 February 2012 - 09:26 AM

Hi maba,

here is the enhanced version:
AutoIt         
#AutoIt3Wrapper_UseX64=n #AutoIt3Wrapper_AU3Check_Parameters= -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 #AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y #include-once #include <ExcelChart.au3> ; ***************************************************************************** ; Create example environment ; ***************************************************************************** Global $aExcel = _XLChart_Example(True, 0, -1, -1, -1) If @error = 2 Then Exit MsgBox(16, "Excel Chart Example Script", "The installed Excel version is not supported by this UDF!" & @CRLF & "Version must be >= 12 (Excel 2007).") If @error <> 0 Then Exit MsgBox(16, "Excel Chart Example Script", "Error " & @error & " returned by _XLChart_Example on line " & @ScriptLineNumber) $iXLC_Debug = 2 Global $iTop _ExcelWriteCell($aExcel[0], "", "B4:B5")                                            ; Clear all cells but one $aExcel[1].SeriesCollection(1).HasErrorBars = True                                    ; Activate Error bars $aExcel[1].SeriesCollection(1).ErrorBars.EndStyle = 2                                ; No cap: $xlNoCap $aExcel[1].SeriesCollection(1).ErrorBar(1, $xlMinusValues, $xlPercent, 100)            ; Set error bars: ($xlY, $xlMinusValues, $xlPercent, 100) $aExcel[1].SeriesCollection(1).ApplyDataLabels()                                    ; Apply the datalabels $aExcel[1].SeriesCollection(1).DataLabels.Position = -4108                            ; Set the data labels centered: $xlLabelPositionCenter _XLChart_LineSet($aExcel[1].SeriesCollection(1), Default, Default, False, Default, Default, $xlSheetHidden) ; Set series 1 to invisible $aExcel[1].Legend.LegendEntries.Item(1).Delete()                                     ; Remove the legend entry for dataseries 1 _XLChart_LineSet($aExcel[1].SeriesCollection(1).ErrorBars, 1, 0x4A7EBB)                ; Set line color for the error bar _XLChart_LineSet($aExcel[1].SeriesCollection(1).DataLabels, 1, 0)                    ; Set line color of the data label _XLChart_FillSet($aExcel[1].SeriesCollection(1).DataLabels, 0x4A7EBB)                ; Fill the datalabel $aExcel[1].SeriesCollection(1).Points(5).DataLabel.Text = "Test3"                    ; Set text of a point of the data label $aExcel[1].SeriesCollection(1).Points(2).DataLabel.Text = "Test2" $aExcel[1].SeriesCollection(1).Points(1).DataLabel.Text = "Test1" & @LF & "Next line"

UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#15 maba

maba

    Seeker

  • Active Members
  • 38 posts

Posted 07 February 2012 - 10:25 AM

Yep! It works well!
When I'll finish my charts I'll post a full example.

THANKS!

#16 water

water

    ?

  • MVPs
  • 15,362 posts

Posted 07 February 2012 - 10:26 AM

I'm just writing the _XLChart_ErrorBarSet function. The next version of the UDF will have this function.
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#17 maba

maba

    Seeker

  • Active Members
  • 38 posts

Posted 07 February 2012 - 12:03 PM

One thing: error bars series is always in "background": both SeriesCollection(1) and SeriesCollection(n) are always set behind other series.

#18 water

water

    ?

  • MVPs
  • 15,362 posts

Posted 07 February 2012 - 01:02 PM

You can change the plotorder of a data series within a chartgroup. It didn't work for the example - the markers of data series 2 always overwrite the errorbar.
Maybe this helps for your chart:
$aExcel[1].ChartGroups(1).SeriesCollection(2).PlotOrder = 1

UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#19 maba

maba

    Seeker

  • Active Members
  • 38 posts

Posted 08 February 2012 - 10:05 AM

:) no way to set errorbar PlotOrder for now.

#20 water

water

    ?

  • MVPs
  • 15,362 posts

Posted 08 February 2012 - 03:54 PM

Just for the records. When the error bars are moved to the secondary value axis they overwrite even the markers.
An example will follow!
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki





Also tagged with one or more of these keywords: Excel, Chart, Help, Support

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users