Forum Discussion

junsh.grace's avatar
junsh.grace
Copper Contributor
Jun 27, 2018

Excel recorded macro to VBA to call from a different software

Hello,   I have no functional knowledge of VBA but needs to call a VBA from a different software (namely SAS) to create chart on multiple excel worksheets with specific formats.   I've recorded a...
Macros & VBA
Need Help
Matt Mickle's avatar
Matt Mickle
Bronze Contributor
Jun 29, 2018

I did name them manually before hand.  I wasn't sure if you had more than one chart on each worksheet.  So I wanted to make sure I could name them based on an easy convention.  This also prevents the need to cycle through all of the shapes in the workbook looking for the chart.

 

The line I commented out was done so because it serves no purpose.  I was fiddling around testing how to work with charts, because it's been a while and during the testing I determined this wasn't necessary.

 

I could incorporate naming the chart in the file you are correct.  I just wanted the most direct way to access the chart objects without having to cycle thorough any others....

 

junsh.grace's avatar
junsh.grace
Copper Contributor
Jul 03, 2018

By the way, I greatly appreciate all your help and time.  It's been quite challenging for someone like me without much knowledge about EXCEL macro and its intricacies.    

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jul 06, 2018

    Junsh-

     

    You're quite welcome.  Why don't you try using some code like this that will allow you to specify which chart is on which sheet based on a few arrays.  The arrays correspond to the sheet names and the chart names.  Please see attached .xlsm example file for reference. :

     

    Sub WorksheetLoop2()

    'Declare variables
    Dim intLp As Integer
    Dim sht As Worksheet
    Dim shtArr As Variant
    Dim chtArr As Variant
    
    'Build arrays that correspond to each other
    'i.e. Sheet1 has myCht1 on it....Sheet2 has myCht2 on it
    shtArr = Array("Sheet1", "Sheet2", "Sheet3")
    chtArr = Array("myCht1", "myCht2", "myCht3")
    
    'Loop through all of the worksheets in the above array
    'Please not that array indexes start at 0 not 1
    'This means that Sheet1 and myCht1 will be found using a 0...
    For intLp = 0 To 2
         
        Sheets(shtArr(intLp)).Activate
        Set sht = ActiveSheet
        sht.ChartObjects(chtArr(intLp)).Activate
        With ActiveChart
            .ChartType = xlColumnClustered
            .SetSourceData Source:=sht.Range("B5:M5")
            .SeriesCollection(1).XValues = sht.Range("B1:M2")
            .SeriesCollection(1).Select
            .PlotArea.Select
            .SeriesCollection(1).Select
            .SeriesCollection(1).ApplyDataLabels
            .SeriesCollection(1).DataLabels.Select
            .SeriesCollection(1).Select
        End With

    Next

End Sub
    • junsh.grace's avatar
      junsh.grace
      Copper Contributor
      Jul 06, 2018

      Thanks Matt again for your help.  I tried your macro after removing the charts from the sheet and got the error message:

      The item with the specified name wasn't found.

       

      As I wasn't sure if the error was due to missing chart objects, I've added an empty chart, renamed it for all three sheets but still got the same error.

       

      Does the line "sht.ChartObjects(chtArr(intLp)).Activate" add an empty object or activate the chart?  Hmm, I don't know why it doesn't produce the results as intended.  Do we need to "add" chart objects before activating them?  If the same logic of activating sheet can be applied to activating chart objects, I believe we should start with existing chart objects before activating them (because the three sheets named properly exist before running the macro).  Would you like to shed some light?

       

      Thanks!

       

      Junsh

      • junsh.grace's avatar
        junsh.grace
        Copper Contributor
        Jul 06, 2018

        Hi Matt,

         

        I think the following worked!  Thanks so much for your suggestion to include the arrays!

         

        Sub WorksheetLoop2()

            'Declare variables
            Dim intLp As Integer
            Dim sht As Worksheet
            Dim shtArr As Variant
            Dim chtArr As Variant
            
            'Build arrays that correspond to each other
            'i.e. Sheet1 has myCht1 on it....Sheet2 has myCht2 on it
            shtArr = Array("mySheet1", "mySheet2", "mySheet3", "mySheet4", "mySheet5", "mySheet6", "mySheet7", "mySheet8", "mySheet9", "mySheet10", "mySheet11", "mySheet12", "mySheet13")
            chtArr = Array("myCht1", "myCht2", "myCht3", "myCht4", "myCht5", "myCht6", "myCht7", "myCht8", "myCht9", "myCht10", "myCht11", "myCht12", "myCht13")
            
            'Loop through all of the worksheets in the above array
            'Please not that array indexes start at 0 not 1
            'This means that Sheet1 and myCht1 will be found using a 0...
            'Added by Jungwon
            For intLp = 0 To 12
                 
                Sheets(shtArr(intLp)).Activate
                Set sht = ActiveSheet
                sht.Shapes.AddChart.Select   'Added by JS
                sht.Shapes(1).Name = chtArr(intLp)   'Added by JS
                sht.ChartObjects(chtArr(intLp)).Activate
                With ActiveChart
                    .ChartType = xlColumnClustered
                    .SetSourceData Source:=sht.Range("B5:M5")
                    .SeriesCollection(1).XValues = sht.Range("B1:M2")
                    .SeriesCollection(1).Select
                    .PlotArea.Select
                    .SeriesCollection(1).Select
                    .SeriesCollection(1).ApplyDataLabels
                    .SeriesCollection(1).DataLabels.Select
                    .SeriesCollection(1).Select
                End With

            Next

        End Sub

Resources