INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

CODE --> VBA

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 

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

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

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

(OP)
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)).

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

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.

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

Quote:

on a "rollup" tab

Hmmmm???

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

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

"...as it should..."

...meaning?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

If Yes, then your Sub SUMIF3D() 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.

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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.

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

(OP)
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.

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

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:

CODE

Sub SUMIF3D()
Dim f As Long
Dim j As Long
Dim y As Long
Dim z As Variant

Open "C:\Temp\MyTestFile.txt" For Output As #1
Print #1, "Start here."

Dim rngToCalculate As Range

Set rngToCalculate = Rollup.Range("Range_to_Calculate")
Print #1, "Set the rngToCalculate"

For Each z In rngToCalculate
    j = 0
    Print #1, "j is " & j
    
    For f = 1 To ThisWorkbook.Sheets.Count
        Print #1, "f is " & f
        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

Print #1, "I am done."
Close #1

End Sub 

You may want to sprinkle a few more Print #1, "Blah, blah, blah" 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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

(OP)
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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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 Application.AutomationSecurity = msoAutomationSecurityForceDisable 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

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

(OP)
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.

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

Try removing SUMIF3D from Import_Properties.

Then run both from a third procedure

CODE

Sum Main()
  Import_Properties
  SUMIF3D 
End Sub 
See if this works for you.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

- in Import_Properties() sub:

CODE -->

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

    Msgbox "Will call SUMIF3D"    
    SUMIF3D
    
ExitSubOnError:

    Rollup.Activate 

- in SUMIF3D() sub:

CODE -->

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

    MsgBox "We started SUMIF3D()"
    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

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

(OP)
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.

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

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.

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

(OP)
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":

CODE --> VBA

'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:

CODE --> VBA

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?

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

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

CODE -->

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

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

(OP)
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?

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

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

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

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

thumbsup

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.

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

Back to your original question:

Quote (ceddins)

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

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

(OP)
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! thumbsup2
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! thanks2

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

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 smile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close