VBA code only works when stepped through or played from within the VBE window 3

[ceddins]

Hi all,

I've been wrestling with Excel for a few days on this one, and I'm hoping someone can end my misery.

I have a sub (Import_Properties) that is played when the user clicks a button. After the user selects the files to import, the code transfers the data to the main workbook. After each file's data is imported, a sub runs that sums up all of these imported tabs on a "rollup" tab. However, I cannot get the code to run all the way through UNLESS I press play from inside the module OR I put in break points and step through the code.

How can I get this to work correctly by clicking the button that is attached to the Import_Properties sub?!?

Thanks for taking the time to read through,
-Clint

Option Explicit
Option Base 1

Sub Import_Properties()
Dim FilePath As Variant
Dim FileName As String
Dim i As Long
Dim x As Long
Dim sht As Worksheet
Dim wsImportTo As Worksheet

    Application.ScreenUpdating = False
    
    MsgBox "Please nagivate to and select ALL of the property files at the same time." & vbCrLf & _
    "Use Ctrl and/or Shift to choose multiple files at once.", vbOKOnly, "Select Files to Import"
    
    FilePath = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select All of the Property Files", MultiSelect:=True)
    If IsArray(FilePath) = False Then
        Exit Sub
    End If
    
    'delete all the imported sheets in this file
    Application.DisplayAlerts = False
    For i = ThisWorkbook.Sheets.Count To 1 Step -1
        If Left(ThisWorkbook.Sheets(i).CodeName, 5) = "Sheet" Then
            ThisWorkbook.Sheets(i).Delete
        End If
    Next
    Application.DisplayAlerts = True
    
    'cycle through each selected file
    For i = LBound(FilePath) To UBound(FilePath)
        'check to make sure the file isn't already open
        For x = 1 To Workbooks.Count
            If Workbooks(x).Name = GetFileName(CStr(FilePath(i))) Then
                MsgBox "Please close " & GetFileName(CStr(FilePath(i))) & " and try your import again.", vbExclamation, "File Open"
                GoTo ExitSubOnError
            End If
        Next
        'open the file
        Workbooks.Open GetFileName(CStr(FilePath(i)))
        'look for the data sheet
        For Each sht In Workbooks(GetFileName(CStr(FilePath(i)))).Worksheets
            If sht.Name = "Need Date Summary" Then
                'add a new sheet to this file. This is where the imported data will go
                Set wsImportTo = ThisWorkbook.Sheets.Add(, Rollup)
                'rename the sheet to the MARSHA code
                On Error Resume Next
                wsImportTo.Name = sht.Range("G2")
                On Error GoTo 0
                'transfer the data
                wsImportTo.Range("B2:W29").Value = sht.Range("B2:W29").Value
                Exit For
            End If
        Next
        'close the file
        Workbooks(GetFileName(CStr(FilePath(i)))).Close False
    Next
    
    SUMIF3D
    
ExitSubOnError:

    Rollup.Activate

    Application.ScreenUpdating = True

End Sub
Private Function GetFileName(FilePath As String) As String
    Dim PathArray
    
    PathArray = Split(FilePath, Application.PathSeparator)
    GetFileName = PathArray(UBound(PathArray))
    
End Function

Sub SUMIF3D()
'This sub is used to sum all of the individual property tabs and put the values on the 'Rollup' tab
Dim f As Long
Dim j As Long
Dim y As Long
Dim z As Variant

Dim rngToCalculate As Range

    Set rngToCalculate = Rollup.Range("Range_to_Calculate")

    'cycle through each cell in the range to calculate
    For Each z In rngToCalculate
        j = 0
        'cycle through all sheets in this workbook
        For f = 1 To ThisWorkbook.Sheets.Count
            'look for sheets whose codename starts with "Sheet"
            If Left(ThisWorkbook.Sheets(f).CodeName, 5) = "Sheet" Then
                'if found, cycle through the years row (columns 4 to 23) to match the year in the column of the caller cell
                For y = 4 To 23
                    If ThisWorkbook.Sheets(f).Cells(4, y).Value = Rollup.Cells(4, z.Column).Value Then
                        'if there's a match, add the value to J
                        j = j + ThisWorkbook.Sheets(f).Cells(z.Row, y)
                        Exit For
                    End If
                Next
            End If
        Next
        'put the value in the current cell
        z.Value = j
    Next

End Sub

 

[ceddins]

Sorry about that; the workbook with the SUMIF3D is the first link.

 

[SkipVought]

ceddins, I asked for and expected a workbook containing the worksheets with all the imported data against which the SUMIF3D would run, since it seems from your previous answers that this is the procedure that will not run to completion.

The workbook that you uploaded contains only two sheets. I don't want to have to upload all your other files and load them.

If you care not to do that, then I suppose that I am finished.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ceddins]

SkipVought, I didn't realize that is what you were asking me; I am truly sorry for my misunderstanding! I am very grateful for you continuing to lend your time to help me. This link has the Rollup file with 4 imported tabs. Please let me know how else I can assist.

 

[ceddins]

SkipVought, I want to add - in case I was unclear - that the SUMIF3D procedure DOES run on it's own, but it DOES NOT run when it is called from within the Import_Properties sub. That is the issue. I am trying to figure out why it will not run from within the Import_Properties sub. The Import_Properties sub is attached to the button the user clicks. The SUMIF3D sub is called from within the Import_Properties sub.

 

[SkipVought]

Well I can't get your project to compile. I have a missing reference to Microsoft Forms 2.0 Object Library.

I have access to Forms controls in my Developer tab, so I fear that my Office 2013 does not have this library (fm20.dll)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Your code works for me (I have non-US/UK office, so I had to change the default beginning of worksheet's code name in the code). How did you know that the SUMIF3D procedure does not start? Have you tested it as Andy suggested? You will know what conditions fail.
You do not need the code in source excel files that executes automatically when you open them by code, at least you can disable it with [tt]Application.AutomationSecurity = msoAutomationSecurityForceDisable[/tt] before opening rge files.

Skip, excel 2016 32-bits uses fm20.dll, so I guess that 2013 too. Don't know 64-bit version. Add userform to vba project and you can find the path in references. This project does not use this library, so, if it's possible, reference can be removed.

combo

 

[ceddins]

Hi, Combo, so thankful for your input. I know the SUMIF3D code does not execute because the Rollup tab values all remain zero after clicking the button and importing the files. However, it WILL execute if I do one of the following: 1) I go into the SUMIF3D after I've clicked the button, imported the files, and the code has stopped, and I play the SUMIF3D sub from the VBE window, the values populate on the Rollup tab; 2) I place a breakpoint at the "For Each z In rngToCalculate" line in SUMIF3D and click the button; 3) I place a breakpoint at the "SUMIF3D" line in the Import_Properties sub. I call the SUMIF3D from the Import_Properties sub, so why would SUMIF3D not execute when called from the Import_Properties sub, but it will execute when played from the VBE window or if I place breakpoints. I'm used to creating projects and testing the code before releasing to users, so yes, I've done some testing. This is why I've come to you experts - because, after testing, I'm unable to determine why SUMIF3D does not execute when called from within Import_Properties sub.

 

[SkipVought]

Try removing SUMIF3D from Import_Properties.

Then run both from a third procedure

Sum Main()
  Import_Properties
  SUMIF3D 
End Sub

See if this works for you.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

SUMIF3D may execute but none of conditions are true when started from worksheet. To test it, add for debugging:

- in Import_Properties() sub:

        Next
        'close the file
        Workbooks(GetFileName(CStr(FilePath(i)))).Close False
    Next

    [b][COLOR=#204A87]Msgbox "Will call SUMIF3D"[/color][/b]    
    SUMIF3D
    
ExitSubOnError:

    Rollup.Activate

- in SUMIF3D() sub:

Sub SUMIF3D()
'This sub is used to sum all of the individual property tabs and put the values on the 'Rollup' tab
Dim f As Long
Dim j As Long
Dim y As Long
Dim z As Variant

Dim rngToCalculate As Range

    [b][COLOR=#204A87]MsgBox "We started SUMIF3D()"[/color][/b]
    Set rngToCalculate = Rollup.Range("Range_to_Calculate")

And, as in my previous post, disable code in workbooks with source data you open, you will have less code that interfere with your project.
The code either first jumps to error handler due to error or none of conditions is true in SUMIF3D, I can't find other possibility.

combo

 

[ceddins]

SkipVought, good idea calling them both from a 3rd sub, but that did not work either.
combo, I added the msgbox code exactly where you've placed it, and this time the SUMIF3D sub DID execute. Does it have something to do with Excel being able to stop before entering the SUMIF3D procedure? This is one commonality across all scenarios where the sub executes. Also, great idea about disabling the code of the source data, sorry I missed that previously. Even though the SUMIF3D didn't execute when I added the automation security stuff, I will keep that bit of code in there.

 

[Andrzejek]

So I guess you did not try my trick with writing some data into simple text file trying to find out what is executed and what is not...?
You know you do go into that SUMIF3D sub, but it does not do what you hope it should be doing. Well, that text file would tell you what is going on in there...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ceddins]

Andrzejek, sorry I did not try this previously, it's a fantastic trick. I was able to drill down to the problem with your .txt file method by printing the variable values in the SUMIF3D sub. When y did not print at all, I knew that the If statement before y is introduced was never equaling "true":

'look for sheets whose codename starts with "Sheet"
If Left(ThisWorkbook.Sheets(f).CodeName, 5) = "Sheet" Then
    For y = 4 To 23
.....

Apparently the codenames for the sheets that get added are blank, or "". So, I inserted this line and now the SUMIF3D sub executes and puts the values on the 'Rollup' tab when the button is clicked.
SUMIF3D code that works:

Sub SUMIF3D()
'This sub is used to sum all of the individual property tabs and put the values on the 'Rollup' tab
Dim f As Long
Dim j As Long
Dim y As Long
Dim z As Variant

Dim rngToCalculate As Range

    Set rngToCalculate = Rollup.Range("Range_to_Calculate")

    'cycle through each cell in the range to calculate
    For Each z In rngToCalculate
        j = 0
        'cycle through all sheets in this workbook
        For f = 1 To ThisWorkbook.Sheets.Count
            'look for sheets whose codename starts with "Sheet" or is blank ""
            If Left(ThisWorkbook.Sheets(f).CodeName, 5) = "Sheet" Or Left(ThisWorkbook.Sheets(f).CodeName, 5) = "" Then
                'if found, cycle through the years row (columns 4 to 23) to match the year in the column of the caller cell
                For y = 4 To 23
                    If ThisWorkbook.Sheets(f).Cells(4, y).Value = Rollup.Cells(4, z.Column).Value Then
                        'if there's a match, add the value to J
                        j = j + ThisWorkbook.Sheets(f).Cells(z.Row, y)
                        Exit For
                    End If
                Next
            End If
        Next
        'put the value in the current cell
        z.Value = j
    Next

End Sub

Does anyone know why the newly added sheets' codenames are "" while the SUMIF3D sub is running and are the standard "Sheet#" when the code has finished?

 

[combo]

Again, don't guess, test instead. First, add a loop in the SUMIF3D() to get code names:

For f=1 to ThisWorkbook.Sheets.Count
    Msgbox "Sheet " & f & ":" & ThisWorkbook.Sheets(f).CodeName
Next f

or add this thfo to Andy's test.

combo

 

[ceddins]

combo, I did not mention in my previous post, but that's exactly why I did. I printed the codename to the .txt file just like you've written it. That's how I know the codename for each imported sheet is blank, or "". That is what leads me to my question: Does anyone know why the newly added sheets' codenames are "" while the SUMIF3D sub is running and are the standard "Sheet#" when the code has finished?

 

[zelgar]

To get the part of the macro to delete all the imported sheets in this file, you'll need to add the bold text in the code for the Import_Properties macro:

If Left(ThisWorkbook.Sheets(i).CodeName, 5) = "Sheet" Or Left(ThisWorkbook.Sheets(i).CodeName, 5) = "" Then

 

[Andrzejek]

Well, it pays to follow suggestions from TT people.
Every line of text - written by somebody who wants to help you - matters.



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[combo]

Back to your original question:

However, I cannot get the code to run all the way through UNLESS I press play from inside the module OR I put in break points and step through the code.

I get a sheet without code name when I'm adding sheet to a workbook with code and protected vba project (excel 2016). After unprotecting added sheets are not visible in the project explorer, until sheets are edited, but this depends on compilings settings. With background compile set, after unprotecting "compile VBAProject" is available, hitting it reveals one (active) or more sheets in project explorer.
Seems that excel is a bit lazy with updating information in vba project, zelgar's tip will pick this issue.


combo

 

[ceddins]

zelgar, great catch! I've added the code to delete the sheets. Thank you.
Andrzejek, well said. Never again will I take lightly any advice from the wise wizards of TT!
combo, thanks for elaborating so I have an understanding of why Excel is behaving the way it is.

All of you: Thank you for spending time from your day to help me out. I'm grateful for people like you and forums like this.
Have a great weekend!

 

[zelgar]

No problem, I noticed that it wouldn't delete the sheets when I ran the macro multiple times without opening the VB editor. When you fixed the main problem, I thought I'd see if it would work for the deleting the other sheets as well and it did