VBA code n ot running properly in context of macro

[lipanook]

I have a big Excel add-in that I've made using VBA. It has lots of different subroutines and functions.

Part of it creates a set of charts, formats them and then positions them in a new worksheet so that they can be exported as a PDF.

When I run this subroutine, the charts do not position themselves correctly. But if I create a new macro exactly the same code in it that is used in the big Add-in, it works perfectly. So the code isn't working in the context of my add-in for some reason.

Has anyone got any ideas as to the kind of thing that could cause this problem, i.e. what I should look out for when debugging?

Thanks in advance.

 

[Andrew Poulsom]

What code are you using to position the charts?

 

[lipanook]

Sub positionCharts()
'This sub-routine positions the polar plots for exporting as PDFs


    Dim numberOfCharts As Integer
    Dim chartLeft As Integer
    Dim pageNumber As Integer
    Dim chartTop As Integer
    Dim Sheet As Object
    Dim i As Integer
    Dim multipleCheck As Integer
    
    
    For Each Sheet In ThisWorkbook.Sheets
    
        If Right(Sheet.Name, 5) = "plots" Then
            numberOfCharts = ActiveSheet.ChartObjects.Count
            
            For i = 1 To numberOfCharts
                
                ''''''''This is a pretty dirty way of selecting the chart
                ActiveSheet.ChartObjects("Chart " & i).Activate
                
                'calculate left position (0 from left if odd number, 222 from left if even)
                If i Mod 2 = 1 Then
                    chartLeft = 0
                Else
                    chartLeft = 222
                End If
                        
                'determine verticle position depending on page number and number that chart is on that page (6 charts per page)
                
                'pageNumber
                pageNumber = Int((i - 1) / 6)
                
                'determine number that chart is on that page
                multipleCheck = i - 1 Mod 6
            
                If multipleCheck = 0 Then
                    chartTop = 750 * pageNumber
                End If
            
                If multipleCheck = 1 Then
                    chartTop = 750 * pageNumber
                End If
            
                If multipleCheck = 2 Then
                    chartTop = 750 * pageNumber + 222
                End If
            
                If multipleCheck = 3 Then
                    chartTop = 750 * pageNumber + 222
                End If
            
                If multipleCheck = 4 Then
                    chartTop = 750 * pageNumber + 444
                End If
            
                If multipleCheck = 5 Then
                    chartTop = 750 * pageNumber + 444
                End If
            
                'Apply the chart position
                With ActiveChart
                    .Parent.Top = chartTop
                    .Parent.Left = chartLeft
                    
                End With
            
            Next i
        
        End If
        
    Next Sheet




End Sub

 

[Andrew Poulsom]

In an Add-In you need to use ActiveWorkbook rather than ThisWorkbook, which refers to the workbook that contains the running code.

As an aside you shouldn't use the names of VBA objects as variables, ie Sheet.

 

[lipanook]

Excellent. That is working now.

Many thanks.