I'm not sure if this belongs in Excel or VB, but I think Excel people may be better equipped to answer it.
I've got a sub in VB that exports data into an Excel spreadsheet then builds a chart based on that data. Excel is building the chart fine, but the chart is meant to look exactly like an older legacy report from Access so each data series has to be formatted.
What I'm trying to do is change the series marker to xlX, which works in VBA...works in VB when I hard code it...but doesn't work when I pass it to this sub:
Public Sub FormatSHChart(xlSheetF As Excel.Worksheet, xlChart As Chart, chartNum As Integer, _
seriesNum, colIndex As Integer, bgColor, fgColor As Integer, _
Marker As String, markerSize As Integer)
.ColorIndex = colIndex
.Weight = xlMedium
.LineStyle = xlContinuous
.MarkerBackgroundColorIndex = bgColor
.MarkerForegroundColorIndex = fgColor
.MarkerStyle = Marker
.markerSize = markerSize
After further rummaging, Yes xlX does exist as a member of the XlErrorBarDirection Class. Its value is -4168.
There is also a constant xlMarkerStyleX which is a member of the XlMarkerStyle Class (suprise, suprise), which also has a value of -4168.
It would seem the cause of your error (as suggested) is the variable type decraration, as I also get this error using a string varable declaration, but works OK with integer declared variable when either of the above constants are passed as an argument.
To find the values of the consants either
1) put msgbox xlX in code or
2) use the object browser (type in the constant in the search box), this also list all other contant (or objects etc) in the Class.