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

 

[zelgar]

Try modify Sub SUMIF3D() to Sub SUMIF3D(Rollup)

 

[ceddins]

Hey zelgar. Thanks for replying. I tried your suggestion, and I got an "Object doesn't support this property or method" error when running it. I put Rollup in the parenthesis of the sub header Sub SUMIF3D(Rollup) which required me to also put it in parenthesis after it's called in the Import_Properties module (like... SUMIF3D(Rollup)).

 

[zelgar]

I'm not the best with subroutines, but I think the issue is that you have the variable Rollup defined in your main macro Import_Properties and are using it in your subroutine SUMIF3D. Maybe if you make the change in the Import_Properties macro to call the SUMIF3D macro/subroutine and include Rollup to indicate that variable is to be included
SUMIF3D Rollup

Sub SUMIF3D(Rollup)

Alternative, could you just move all of the code from the SUMIF3D macro into the Import_Properties macro and see if it works.

 

[SkipVought]

on a "rollup" tab

Hmmmm???

If your TAB is "rollup", then Option Explicit should be giving you an ERROR,

Compile error: Variable not defined

However, if you assigned that TAB, whatever its actual name is, a CodeName of "rollup" then your code will run.

Can we get clear on this?

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

 

[ceddins]

Hi SkipVought. I have a tab whose codename is Rollup. It should run, but it doesn't! For some reason it only works when I step through or press play from within the code window. I need it to work when the user presses the button.

 

[ceddins]

zelgar, I did try moving all of the SUMIF3D up to the main sub, and that didn't work either.

 

[SkipVought]

Would you please explain in detail exactly what happens when the button it hit.

Are you absolutely certain that the button runs this procedure?

Is this procedure in a MODULE and not a Worksheet Codesheet?

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

 

[ceddins]

When the button is clicked, the Import_Properties code runs. I right-clicked the button and assigned the Import_Properties macro to it. I know it is assigned correctly because the macro runs when I click the button; it just doesn't play the SUMIF3D sub as it should unless I play from within the module or step through the code. All of the code is in a module and not a worksheet codesheet.

 

[SkipVought]

...as it should..."

...meaning?

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

 

[ceddins]

I mean that all of the cells on the Rollup tab where the SUMIF3D should be putting values remain zero after the code is run which is what they are before the user clicks the button when no data has been imported yet.

 

[SkipVought]

So you mean that absolutely none of the Import_Properties procedure completed?

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

 

[Andrzejek]

When you put a break on the line:
[tt]Sub SUMIF3D()[/tt]
and run your code from the button, do you reach that line and your execution stops on that break?

If Yes, then your [tt]Sub SUMIF3D()[/tt] does not do anything
If No then you never reach this point of your code.

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.

 

[SkipVought]

That's what we're trying to determine. You've got to do some testing to discover exactly where the process stops.

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

 

[zelgar]

Do you really need the SUMIF3D as a separate macro/subroutine? If not, delete the SUMIF3D line and move all of the code from the SUMIF3D macro/subroutine into the Import_Properties macro and see if it works.

 

[ceddins]

SkipVought: All of the Import_Properties code executes and works when I click the button EXCEPT for the SUMIF3D portion of the code.
Andrzejek: when I put a break point on the SUMIF3D line and I click the button, code stops at the break point. Then, when I continue it from the break point, the SUMIF3D portion of the code executes correctly, updating the values on the Rollup tab.
zeglar: Yes, I could move all of the SUMIF3D stuff up into the Import_Properties module, but it still behaves exactly the same way when I do that. Side note: The reason it is a separate routine is because it used to be a user-defined function, but the same issue was occurring. I just tweaked a few parts of it, changed it to a sub, and called it from Import_Properties because I thought that would make it work when the button was clicked.

 

[Andrzejek]

Hmmm… Weird. So it behaves one way when just run it, and another way when you step thru the code. The good news is – now we know it goes to the Sub SUMIF3D

You can employ a simple/low tech approach and write some information into a simple text file inside the SUMIF3D Sub and - after you run it from your button – see what was going on:

Sub SUMIF3D()
Dim f As Long
Dim j As Long
Dim y As Long
Dim z As Variant
[blue]
Open "C:\Temp\MyTestFile.txt" For Output As #1
Print #1, "Start here."
[/blue]
Dim rngToCalculate As Range

Set rngToCalculate = Rollup.Range("Range_to_Calculate")[blue]
Print #1, "Set the rngToCalculate"[/blue]

For Each z In rngToCalculate
    j = 0[blue]
    Print #1, "j is " & j[/blue]
    
    For f = 1 To ThisWorkbook.Sheets.Count[blue]
        Print #1, "f is " & f[/blue]
        If Left(ThisWorkbook.Sheets(f).CodeName, 5) = "Sheet" Then
            
            For y = 4 To 23
                If ThisWorkbook.Sheets(f).Cells(4, y).Value = Rollup.Cells(4, z.Column).Value Then
                    
                    j = j + ThisWorkbook.Sheets(f).Cells(z.Row, y)
                    Exit For
                End If
            Next
        End If
    Next
    z.Value = j
Next
[blue]
Print #1, "I am done."
Close #1
[/blue]
End Sub

You may want to sprinkle a few more [tt]Print #1, "Blah, blah, blah"[/tt] to get more information

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.

 

[SkipVought]

Okay, for testing this called procedure by itself, it sure would be helpful if you could upload a copy of your workbook that at lease has some representative sheets/ranges, that could be used for testing.

I'd suggest that whatever pared-down version that you would upload, that you would first run the SUMIF3D procedure on it to ascertain that it still behaves as advertised.

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

 

[ceddins]

SkipVought, here is the rollup tool and 4 test files to import into the rollup tool. I've never uploaded a file to this site, so I hope I did it right

http://files.engineering.com/getfil...dc801a777f3&file=Rollup_Tool_Web_Version.xlsb

http://files.engineering.com/getfil...4-9683-baf2568c8d9a&file=PROPERTY_1_Test.xlsb

http://files.engineering.com/getfil...7-8355-67ddcebf5b95&file=PROPERTY_2_Test.xlsb

http://files.engineering.com/getfil...f-b7f5-f12dd63f0e30&file=PROPERTY_3_Test.xlsb

http://files.engineering.com/getfil...7-8e73-cf537b25371f&file=PROPERTY_4_Test.xlsb

Thanks for taking a look!

 

[SkipVought]

I was just looking for your workbook. Don't want to run the whole thing with all the imports, just SUMIF3D.

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