VB Personal Workbook Error where Macro runs every time I open Excel

Question

I have a macro that I found on the net and have been using to allow me to export all chart objects within the active workbook. It seems to work just fine when I have it in its own normal workbook.

However, I want this to be a generic function that can be used on any given workbook so I have placed this code inside of my personal excel workbook. After doing this I notice that the code now runs every time I open my excel application and I basically get a ton of windows opening that are empty ".png" files.

How can I prevent this code from running every time the application opens? Is it an excel setting or something inherently wrong with the macro code?

I have other Macros in my personal workbook that appear to be working normally (only running when selected through the Tools > VB > Macro menu) so I feel like there is something going wrong with the code. Any help would be greatly appreciated.

'the main problematic function'
    Sub ExportAllPossibleCharts()
        Dim i As Integer, exportCount As Integer
        Dim fileNum As String, fileBase As String
        Dim sheetObj As Worksheet
        Dim chartObj As Chart


    fileBase = ActiveWorkbook.FullName
    fileBase = Replace(fileBase, ".xlsx", "")
    exportCount = 0

    'First, export all charts that are in their own sheets'

    For Each chartObj In ActiveWorkbook.Charts
        fileNum = NiceFileNumber(exportCount)
        exportCount = exportCount + 1

        'Do the export'
        chartObj.Export fileBase & "_chart" & fileNum & ".png"
    Next

    'Then, export all charts that are embedded inside normal sheets'
    For Each sheetObj In ActiveWorkbook.Worksheets
        For i = 1 To sheetObj.ChartObjects.Count
            fileNum = NiceFileNumber(exportCount)
            exportCount = exportCount + 1

            'Do the export'
            sheetObj.ChartObjects(i).Activate
            ActiveChart.Export fileBase & "_chart" & fileNum & ".png"
        Next i
    Next
End Sub


'small nicety to ensure two-digits for better file sorting'
Function NiceFileNumber(num As Integer) As String
    If num < 10 Then
        NiceFileNumber = "0" & num
    Else
        NiceFileNumber = num
    End If
End Function

where in VBA project ( i mean which object module ) is this code placed? Can you locate the Workbook_Open event from ThisWorkbook object module and provide us the code? — user2140173
The code is currently living in Module 1. How would I locate those events to send you the code? — user3091348
Ok I located the "ThisWorkbook object" there is currently no code in this object that I can see — user3091348
if there is nothing to start this code how does it start that's the first question that you need to solve :) — user2140173
I have identified the cause of the problem. Apparently when I was testing the macro I created a few files in the personal workbook directory. /Users/MyAccount/Library/Application Support/Microsoft/Office/ExceL After removing those files and replacing the personal workbook it appears to no longer be an issue. Thank you for the assistance. — user3091348

Christian Sauer Christian Sauer · Accepted Answer · 2013-12-12T08:34:54

I would not place this macro in a "run everytime you open excel" location. That's just not your intend. Instead, I would save this macro in a addin and add a simple button into the Office-Ribbon. This way, your macro should show up, but don't do anything until you are clicking the button. Building a Button for a macro is not so complicated, it consists of the following steps: 1. Store your macro as addin (*.xlam). You will redirected to a folder in %Appdata% and thats ok. Excel stores all addins there. 1a: Beware: Don't throw away your normal xlsm file - it is difficult to edit a addin, so I usually edit the macro and simply save again. 1b: Beware: Make sure ysou delete uneccessary sheets from the macro before saving it as addin. If you forget this, your addin will become big and slowdown excel startup considerably. 2: Close all Excel instances and fire up a xml editor for office like "Custom UI Editor For Microsoft Office" 3: Insert a Office 2007 custom ui part into the file 4: Insert the following xml to the file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab id="Best" label="Best Tools">
        <group id="MyGroup" visible="true" label="GroupLabel"> 
          <button id="MyMacroID" imageMso="TableIndexes" size="large" 
            label="Export" 
            onAction="DoMacro" /> 
 </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

This will call you macro, provided you have a "Public Sub DoMacro (obj as variant)" in your addin. 5. activate your addin in the excel-options (this is a little different four excel-versions, so you have to look up it yourself.

Additional Information: More general Information about Ribbon-Xml can be found here: http://gregmaxey.mvps.org/word_tip_pages/customize_ribbon_main.html

The image for the button (imageMso="TableIndexes" in xml) can be customized to a large number of buttons, download the file from microsoft to see which are available: http://www.microsoft.com/en-us/download/details.aspx?id=11675

VB Personal Workbook Error where Macro runs every time I open Excel

1 Answers