Hi,
I am unable to use the XY chart label er in my machine, i uninstalled and reinstalled the XY chart label and still it gives the error as compiler error.
I am able to use the same XY chart label on my another machine and also use it on the same excel data.
please find the codes
''' XY Chart Labeler 7.1 - Authored by Rob Bovey, Copyright © 1996-2014,
''' all rights reserved. May be redistributed for free, but may not
''' be sold without the author's explicit permission.
Option Explicit
''' **************************************************************************
''' UserForm Variable Declarations Follow
''' **************************************************************************
Private mbUserCancel As Boolean
Private mbEnableEvents As Boolean
Private mchtChart As Excel.Chart
Private mrngLabelRange As Excel.Range
Private msrsTargetSeries As Excel.Series
Private masrsSeriesArray() As Excel.Series
Private mlPositionArray() As Long
''' **************************************************************************
''' UserForm Property Procedures Follow
''' **************************************************************************
Public Property Set TargetChart(ByRef chtNewValue As Excel.Chart)
Set mchtChart = chtNewValue
End Property
Public Property Get UserCancel() As Boolean
UserCancel = mbUserCancel
End Property
Public Property Get LabelRange() As Excel.Range
Set LabelRange = mrngLabelRange
End Property
Public Property Get SeriesChoice() As Excel.Series
Set SeriesChoice = masrsSeriesArray(ddnSeries.ListIndex)
End Property
''' Exposes which label position the user selected.
Public Property Get Position() As Long
Position = mlPositionArray(ddnPosition.ListIndex)
End Property
Public Property Get FastLabel() As Boolean
FastLabel = CBool(chkFastLabel.Value)
End Property
''' **************************************************************************
''' UserForm Event Procedures Follow
''' **************************************************************************
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
''' Redirect all non-code form closes through the Cancel button procedure.
If CloseMode <> vbFormCode Then
cmdCancel.SetFocus ''' If the QueryClose event runs while the refEdit control has focus, we're hosed.
Cancel = True
cmdCancel_Click
End If
End Sub
Private Sub UserForm_Terminate()
''' Clean up our form-level object variables.
Set mchtChart = Nothing
Set mrngLabelRange = Nothing
Set msrsTargetSeries = Nothing
Erase masrsSeriesArray()
End Sub
Private Sub cmdOK_Click()
Const szSOURCE As String = "FAddLabels.cmdOK_Click"
On Error GoTo ErrorHandler
Set mrngLabelRange = rngGetLabelRange(refChooseRange.Text)
If mrngLabelRange Is Nothing Then
Err.Raise glHANDLED_ERROR
ElseIf Not bValidateNumLabels(masrsSeriesArray(ddnSeries.ListIndex)) Then
Err.Raise glHANDLED_ERROR
Else
mbUserCancel = False
Me.Hide
End If
ErrorExit:
ResetAppProperties
Exit Sub
ErrorHandler:
If Err.Number <> glHANDLED_ERROR Then Err.Description = Err.Description & " (" & szSOURCE & ")"
refChooseRange.SetFocus
If bCentralErrorHandler(True) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Sub
Private Sub cmdCancel_Click()
mbUserCancel = True
Me.Hide
End Sub
Private Sub cmdHelp_Click()
DisplayHTMLHelpTopic glHELP_ADD_LABELS
End Sub
Private Sub ddnSeries_Change()
Dim srsSelection As Excel.Series
If mbEnableEvents Then
Set srsSelection = masrsSeriesArray(ddnSeries.ListIndex)
''' Load the array of possible label positions for this series based on its chart type.
LoadPositionDropdown srsSelection
On Error Resume Next
srsSelection.Select
On Error GoTo 0
Application.ScreenUpdating = False
Application.ScreenUpdating = True
refChooseRange.SetFocus
End If
End Sub
Private Sub refChooseRange_Change()
If mbEnableEvents Then
If Len(refChooseRange.Text) = 0 Then
cmdOK.Enabled = False
Else
cmdOK.Enabled = True
End If
End If
End Sub
Private Sub ddnPosition_Change()
If mbEnableEvents Then If Len(refChooseRange.Text) = 0 Then refChooseRange.SetFocus
End Sub
''' **************************************************************************
''' UserForm Method Procedures Follow
''' **************************************************************************
Public Function Initialize(ByVal lSelectedSeries As Long) As Boolean
Const szSOURCE As String = "FAddLabels.Initialize()"
Dim bReturn As Boolean
Dim srsSeries As Excel.Series
Dim lCount As Long
Dim lIndex As Long
Dim lDefault As Long
Dim szName As String
On Error GoTo ErrorHandler
''' Assume success until an error is encountered.
bReturn = True
mbEnableEvents = False
''' We need to run a special form setup routine for Japanese versions.
If Application.International(xlCountryCode) = glJAPANESE Then EnableJapanese Me
Set mrngLabelRange = Nothing
lCount = 1
lIndex = 0
lDefault = 0
''' Set the dialog title.
ddnSeries.ControlTipText = gszTIP_SERIES_DDN
refChooseRange.ControlTipText = gszTIP_LABEL_RANGE
ddnPosition.ControlTipText = gszTIP_POSITION
''' Clear the old list.
ddnSeries.Clear
''' Fill the series selection dropdown with the names of each data series in the chart.
For Each srsSeries In mchtChart.SeriesCollection
''' Only add the names of series which can be labeled.
If bCanSeriesBeLabeled(srsSeries) Then
''' Determine if this series should be selected by default.
If lSelectedSeries > 0 Then
If lSelectedSeries = lCount Then
lDefault = lIndex
End If
End If
''' Load the series into the array.
ReDim Preserve masrsSeriesArray(0 To lIndex)
Set masrsSeriesArray(lIndex) = srsSeries
lIndex = lIndex + 1
''' If the series has a name, display it in the dropdown,
''' otherwise display its collection index number.
On Error Resume Next
szName = vbNullString
szName = srsSeries.Name
On Error GoTo ErrorHandler
If Len(szName) = 0 Then
ddnSeries.AddItem gszSERIES & CStr(lCount) & gszNO_NAME
Else
ddnSeries.AddItem srsSeries.Name
End If
End If
lCount = lCount + 1
Next srsSeries
''' If we've been given a selected data series, it will be selected as the default here.
ddnSeries.ListIndex = lDefault
''' Load the array of possible label positions for this series based on its chart type.
Set srsSeries = masrsSeriesArray(ddnSeries.ListIndex)
LoadPositionDropdown srsSeries
On Error Resume Next
srsSeries.Select
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.ScreenUpdating = True
cmdOK.Enabled = False
refChooseRange.Text = gszEMPTY_STRING
refChooseRange.SetFocus
ErrorExit:
mbEnableEvents = True
Initialize = bReturn
Exit Function
ErrorHandler:
If Err.Number <> glHANDLED_ERROR Then Err.Description = Err.Description & " (" & szSOURCE & ")"
bReturn = False
If bCentralErrorHandler(False) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function
''' **************************************************************************
''' UserForm Private Procedures Follow
''' **************************************************************************
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Comments: Ensures the number of cells selected matches the number of
''' data points in the series being labeled.
'''
''' Arguments: srsSeries [in] The series to examine.
'''
''' Returns: Boolean True if count matches, False otherwise.
'''
''' Date Developer Action
''' --------------------------------------------------------------------------
''' 02/16/07 Rob Bovey Created
'''
Private Function bValidateNumLabels(ByRef srsSeries As Excel.Series) As Boolean
Const szSOURCE As String = "FAddLabels.bValidateNumLabels()"
Dim bReturn As Boolean
Dim rngCell As Excel.Range
Dim rngArea As Excel.Range
Dim lPoints As Long
Dim lCells As Long
On Error GoTo ErrorHandler
''' Assume success until an error is encountered.
bReturn = True
''' Get the number of points in the selected data series.
lPoints = srsSeries.Points.Count
''' Get the number of cells selected.
lCells = 0
If mrngLabelRange.Areas.Count > 0 Then
For Each rngArea In mrngLabelRange.Areas
For Each rngCell In rngArea
If Not (rngCell.EntireRow.Hidden Or rngCell.EntireColumn.Hidden) Then lCells = lCells + 1
Next rngCell
Next rngArea
Else
For Each rngCell In mrngLabelRange
If Not (rngCell.EntireRow.Hidden Or rngCell.EntireColumn.Hidden) Then lCells = lCells + 1
Next rngCell
End If
''' Error out if the number of labels doesn't match number of data points.
If lCells <> lPoints Then Err.Raise glHANDLED_ERROR, , szSubstituteText(gszERR_NUM_LABELS, CStr(lCells), CStr(lPoints))
ErrorExit:
bValidateNumLabels = bReturn
Exit Function
ErrorHandler:
If Err.Number <> glHANDLED_ERROR Then Err.Description = Err.Description & " (" & szSOURCE & ")"
bReturn = False
If bCentralErrorHandler(False) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Comments: Loads the available DataLabel positioning options for the
''' specified series based on the chart type of that series.
'''
''' Arguments: srsSeries [in] The series to examine.
'''
''' Date Developer Action
''' --------------------------------------------------------------------------
''' 02/16/07 Rob Bovey Created
'''
Private Sub LoadPositionDropdown(ByRef srsSeries As Excel.Series)
Dim lIndex As Long
Dim szCurrentValue As String
Dim aszNameArray() As String
lIndex = 0
szCurrentValue = ddnPosition.Text
ddnPosition.Enabled = True
ddnPosition.BackColor = glCTL_COLOR_ENABLED
ddnPosition.Clear
Erase mlPositionArray()
''' Determine if the series type allows label positioning
If bCanLabelsBePositioned(srsSeries) Then
''' Determine what positioning options are available.
If Not bLoadPositionArrays(srsSeries.ChartType, mlPositionArray(), aszNameArray()) Then Err.Raise glHANDLED_ERROR
''' Load the position descriptions into the dropdown.
For lIndex = LBound(aszNameArray) To UBound(aszNameArray)
ddnPosition.AddItem aszNameArray(lIndex)
Next lIndex
ddnPosition.ListIndex = 0
On Error Resume Next
ddnPosition.Text = szCurrentValue
On Error GoTo 0
Else
''' Can't position labels for this series, disable the dropdown
ddnPosition.AddItem gszMSG_NO_POSITION
ddnPosition.ListIndex = 0
ddnPosition.Enabled = False
ddnPosition.BackColor = glCTL_COLOR_DISABLED
ReDim Preserve mlPositionArray(0 To lIndex)
mlPositionArray(lIndex) = glNO_POSITION
End If
End Sub
I am unable to use the XY chart label er in my machine, i uninstalled and reinstalled the XY chart label and still it gives the error as compiler error.
I am able to use the same XY chart label on my another machine and also use it on the same excel data.
please find the codes
''' XY Chart Labeler 7.1 - Authored by Rob Bovey, Copyright © 1996-2014,
''' all rights reserved. May be redistributed for free, but may not
''' be sold without the author's explicit permission.
Option Explicit
''' **************************************************************************
''' UserForm Variable Declarations Follow
''' **************************************************************************
Private mbUserCancel As Boolean
Private mbEnableEvents As Boolean
Private mchtChart As Excel.Chart
Private mrngLabelRange As Excel.Range
Private msrsTargetSeries As Excel.Series
Private masrsSeriesArray() As Excel.Series
Private mlPositionArray() As Long
''' **************************************************************************
''' UserForm Property Procedures Follow
''' **************************************************************************
Public Property Set TargetChart(ByRef chtNewValue As Excel.Chart)
Set mchtChart = chtNewValue
End Property
Public Property Get UserCancel() As Boolean
UserCancel = mbUserCancel
End Property
Public Property Get LabelRange() As Excel.Range
Set LabelRange = mrngLabelRange
End Property
Public Property Get SeriesChoice() As Excel.Series
Set SeriesChoice = masrsSeriesArray(ddnSeries.ListIndex)
End Property
''' Exposes which label position the user selected.
Public Property Get Position() As Long
Position = mlPositionArray(ddnPosition.ListIndex)
End Property
Public Property Get FastLabel() As Boolean
FastLabel = CBool(chkFastLabel.Value)
End Property
''' **************************************************************************
''' UserForm Event Procedures Follow
''' **************************************************************************
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
''' Redirect all non-code form closes through the Cancel button procedure.
If CloseMode <> vbFormCode Then
cmdCancel.SetFocus ''' If the QueryClose event runs while the refEdit control has focus, we're hosed.
Cancel = True
cmdCancel_Click
End If
End Sub
Private Sub UserForm_Terminate()
''' Clean up our form-level object variables.
Set mchtChart = Nothing
Set mrngLabelRange = Nothing
Set msrsTargetSeries = Nothing
Erase masrsSeriesArray()
End Sub
Private Sub cmdOK_Click()
Const szSOURCE As String = "FAddLabels.cmdOK_Click"
On Error GoTo ErrorHandler
Set mrngLabelRange = rngGetLabelRange(refChooseRange.Text)
If mrngLabelRange Is Nothing Then
Err.Raise glHANDLED_ERROR
ElseIf Not bValidateNumLabels(masrsSeriesArray(ddnSeries.ListIndex)) Then
Err.Raise glHANDLED_ERROR
Else
mbUserCancel = False
Me.Hide
End If
ErrorExit:
ResetAppProperties
Exit Sub
ErrorHandler:
If Err.Number <> glHANDLED_ERROR Then Err.Description = Err.Description & " (" & szSOURCE & ")"
refChooseRange.SetFocus
If bCentralErrorHandler(True) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Sub
Private Sub cmdCancel_Click()
mbUserCancel = True
Me.Hide
End Sub
Private Sub cmdHelp_Click()
DisplayHTMLHelpTopic glHELP_ADD_LABELS
End Sub
Private Sub ddnSeries_Change()
Dim srsSelection As Excel.Series
If mbEnableEvents Then
Set srsSelection = masrsSeriesArray(ddnSeries.ListIndex)
''' Load the array of possible label positions for this series based on its chart type.
LoadPositionDropdown srsSelection
On Error Resume Next
srsSelection.Select
On Error GoTo 0
Application.ScreenUpdating = False
Application.ScreenUpdating = True
refChooseRange.SetFocus
End If
End Sub
Private Sub refChooseRange_Change()
If mbEnableEvents Then
If Len(refChooseRange.Text) = 0 Then
cmdOK.Enabled = False
Else
cmdOK.Enabled = True
End If
End If
End Sub
Private Sub ddnPosition_Change()
If mbEnableEvents Then If Len(refChooseRange.Text) = 0 Then refChooseRange.SetFocus
End Sub
''' **************************************************************************
''' UserForm Method Procedures Follow
''' **************************************************************************
Public Function Initialize(ByVal lSelectedSeries As Long) As Boolean
Const szSOURCE As String = "FAddLabels.Initialize()"
Dim bReturn As Boolean
Dim srsSeries As Excel.Series
Dim lCount As Long
Dim lIndex As Long
Dim lDefault As Long
Dim szName As String
On Error GoTo ErrorHandler
''' Assume success until an error is encountered.
bReturn = True
mbEnableEvents = False
''' We need to run a special form setup routine for Japanese versions.
If Application.International(xlCountryCode) = glJAPANESE Then EnableJapanese Me
Set mrngLabelRange = Nothing
lCount = 1
lIndex = 0
lDefault = 0
''' Set the dialog title.
ddnSeries.ControlTipText = gszTIP_SERIES_DDN
refChooseRange.ControlTipText = gszTIP_LABEL_RANGE
ddnPosition.ControlTipText = gszTIP_POSITION
''' Clear the old list.
ddnSeries.Clear
''' Fill the series selection dropdown with the names of each data series in the chart.
For Each srsSeries In mchtChart.SeriesCollection
''' Only add the names of series which can be labeled.
If bCanSeriesBeLabeled(srsSeries) Then
''' Determine if this series should be selected by default.
If lSelectedSeries > 0 Then
If lSelectedSeries = lCount Then
lDefault = lIndex
End If
End If
''' Load the series into the array.
ReDim Preserve masrsSeriesArray(0 To lIndex)
Set masrsSeriesArray(lIndex) = srsSeries
lIndex = lIndex + 1
''' If the series has a name, display it in the dropdown,
''' otherwise display its collection index number.
On Error Resume Next
szName = vbNullString
szName = srsSeries.Name
On Error GoTo ErrorHandler
If Len(szName) = 0 Then
ddnSeries.AddItem gszSERIES & CStr(lCount) & gszNO_NAME
Else
ddnSeries.AddItem srsSeries.Name
End If
End If
lCount = lCount + 1
Next srsSeries
''' If we've been given a selected data series, it will be selected as the default here.
ddnSeries.ListIndex = lDefault
''' Load the array of possible label positions for this series based on its chart type.
Set srsSeries = masrsSeriesArray(ddnSeries.ListIndex)
LoadPositionDropdown srsSeries
On Error Resume Next
srsSeries.Select
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.ScreenUpdating = True
cmdOK.Enabled = False
refChooseRange.Text = gszEMPTY_STRING
refChooseRange.SetFocus
ErrorExit:
mbEnableEvents = True
Initialize = bReturn
Exit Function
ErrorHandler:
If Err.Number <> glHANDLED_ERROR Then Err.Description = Err.Description & " (" & szSOURCE & ")"
bReturn = False
If bCentralErrorHandler(False) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function
''' **************************************************************************
''' UserForm Private Procedures Follow
''' **************************************************************************
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Comments: Ensures the number of cells selected matches the number of
''' data points in the series being labeled.
'''
''' Arguments: srsSeries [in] The series to examine.
'''
''' Returns: Boolean True if count matches, False otherwise.
'''
''' Date Developer Action
''' --------------------------------------------------------------------------
''' 02/16/07 Rob Bovey Created
'''
Private Function bValidateNumLabels(ByRef srsSeries As Excel.Series) As Boolean
Const szSOURCE As String = "FAddLabels.bValidateNumLabels()"
Dim bReturn As Boolean
Dim rngCell As Excel.Range
Dim rngArea As Excel.Range
Dim lPoints As Long
Dim lCells As Long
On Error GoTo ErrorHandler
''' Assume success until an error is encountered.
bReturn = True
''' Get the number of points in the selected data series.
lPoints = srsSeries.Points.Count
''' Get the number of cells selected.
lCells = 0
If mrngLabelRange.Areas.Count > 0 Then
For Each rngArea In mrngLabelRange.Areas
For Each rngCell In rngArea
If Not (rngCell.EntireRow.Hidden Or rngCell.EntireColumn.Hidden) Then lCells = lCells + 1
Next rngCell
Next rngArea
Else
For Each rngCell In mrngLabelRange
If Not (rngCell.EntireRow.Hidden Or rngCell.EntireColumn.Hidden) Then lCells = lCells + 1
Next rngCell
End If
''' Error out if the number of labels doesn't match number of data points.
If lCells <> lPoints Then Err.Raise glHANDLED_ERROR, , szSubstituteText(gszERR_NUM_LABELS, CStr(lCells), CStr(lPoints))
ErrorExit:
bValidateNumLabels = bReturn
Exit Function
ErrorHandler:
If Err.Number <> glHANDLED_ERROR Then Err.Description = Err.Description & " (" & szSOURCE & ")"
bReturn = False
If bCentralErrorHandler(False) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Comments: Loads the available DataLabel positioning options for the
''' specified series based on the chart type of that series.
'''
''' Arguments: srsSeries [in] The series to examine.
'''
''' Date Developer Action
''' --------------------------------------------------------------------------
''' 02/16/07 Rob Bovey Created
'''
Private Sub LoadPositionDropdown(ByRef srsSeries As Excel.Series)
Dim lIndex As Long
Dim szCurrentValue As String
Dim aszNameArray() As String
lIndex = 0
szCurrentValue = ddnPosition.Text
ddnPosition.Enabled = True
ddnPosition.BackColor = glCTL_COLOR_ENABLED
ddnPosition.Clear
Erase mlPositionArray()
''' Determine if the series type allows label positioning
If bCanLabelsBePositioned(srsSeries) Then
''' Determine what positioning options are available.
If Not bLoadPositionArrays(srsSeries.ChartType, mlPositionArray(), aszNameArray()) Then Err.Raise glHANDLED_ERROR
''' Load the position descriptions into the dropdown.
For lIndex = LBound(aszNameArray) To UBound(aszNameArray)
ddnPosition.AddItem aszNameArray(lIndex)
Next lIndex
ddnPosition.ListIndex = 0
On Error Resume Next
ddnPosition.Text = szCurrentValue
On Error GoTo 0
Else
''' Can't position labels for this series, disable the dropdown
ddnPosition.AddItem gszMSG_NO_POSITION
ddnPosition.ListIndex = 0
ddnPosition.Enabled = False
ddnPosition.BackColor = glCTL_COLOR_DISABLED
ReDim Preserve mlPositionArray(0 To lIndex)
mlPositionArray(lIndex) = glNO_POSITION
End If
End Sub