Solved: Calling subroutine

[jwise]

This one is difficult to explain, but here goes. All of the involved routines are in the same object repository. The code snippet and debugger trace information shows that the code is going through the loop to call Ximp_PNL. The first executable statement in this routine is a MsgBox displaying that Ximp_PNL has been called. The subroutine is never being called, i.e. the display statement is not being executed. This routine moves data on "Sheet1" and the data is not being moved. I can not figure why the sub is not being called. Here is the problem code:


ans = MsgBox("Import- Shall I import data into the primary worksheet", _
vbYesNoCancel)

If ans = vbYes Then
If impType = "GPR" Then Call imp_GPR(shName)
If impType = "PNL" Then
rc = MsgBox("***Debug*** IN LOOP")
Call Ximp_PNL(shName)
rc = MsgBox("***Debug*** Past imp_PNL Call")
End If
If impType = "PRE" Then Call imp_PRE(shName)
End If


When I run the code I get the "Debug in Loop" and "Past imp" messages as expected, but I do not get the message from the sub ("imp_PNL entered"). Here is the relevant snippet from it:

Dim wbRecap As Workbook
Dim wsImport As Worksheet
Dim wsData As Worksheet

rc = MsgBox("***Debug*** imp_PNL Entered")

Set wbRecap = ThisWorkbook
Set wsData = wbRecap.Worksheets("Sheet1")
Set wsImport = wbRecap.Worksheets(wsName)


You may notice that some of the statements refer to the sub as Ximp_PNL and some as imp_PNL. The routine was renamed in an effort to determine if there was something "left-over" in the "compiler". I found no difference.

[mdmackillop]

I don't see the problem.
Are you using Option Explicit?
What is your "object repository"?

[jwise]

I did put "Option Explicit" and it found one error: the variable "zname" was not defined. I did that and tested it, receiving the same results.

There are three of these subs that I am trying to call; imp_GPR, impPNL, and imp_PRE. Only one will be called. All of the test data was against PNL. I decided that since I had made changes to the PNL code to handle this, that maybe somehow PNL was being looked for in its former location. I had it in the "Sheet1" object originally. When I put the new logic in, this subroutine (and its brothers GPR and PRE) were all moved to the frmImport object. frmImport is the name of a user form. This form asks the user to choose which of the data types is involved (GPR, PNL, or PRE). Before I had to choose the correct subroutine, name the worksheet where the data was imported a certain name, and then invoke the correct "imp" routine. Now I just give it the file name I want to import and tell it which of the three choices. It dynamically names the imported data as worksheet XXXyy where XXX is GPR, PNL, or PRE, and yy is a number from 01 to 99 depending on the number of imports of that kind of data I've done. This is usually a max of three to five.

The imp_XXX routines had to be modified when they were moved because they now needed the name of the worksheet to use to update Sheet1. This routine puts out messages informing me of how many rows were in the updates and how many were actually done. I watched it run on the debugger, and the call to the subs goes yellow, but I get no message indicating it has been entered.

[jwise]

I've tried lots of unsuccessful stuff. Most of this was putting extra subroutine calls where I claim the call is being skipped. These extra calls (to subs whose only statement is a display message) are ALSO being ignored.

After thinking about this, it has to be something about the location of the code. All the code is located in the same library as the "click event" which was on my user form (an object called frmImport). Apparently, this code is in the wrong place or I've somehow confused VB.

Any ideas? This is my speculation, not confirmed fact. I'm trying to figure out how to confirm it.

[mdmackillop]

Have you used the same name twice?

[rory]

Does your project compile OK? If you have put those routines in a form module and are calling them from somewhere else, I wouldn't really expect them to work. They don't, from what you have posted, belong in a userform module or a worksheet module, but in a normal module.

[jwise]

Q1. Have you used the same name twice?
A1- I do not understand this question.

Q2. Does it compile OK?
Q2- I have seen no errors of any sort. The only problem is the total failure to call ANY subroutine in the last executing module. I am trying to find the doc on how to post code now.

I took the code and data and removed all that was not relevant. Then I tested it again, and it still exhibits the same behavior. Specifically, the code snippet has displays and subroutine calls. The displays execute, but the subroutines are never entered. If the code worked properly, there would be no displays (all diagnostic) and only the one subroutine call (to Ximp_PNL.

I have uploaded the workbook/spreadsheet. I will upload the data file in a later post, but I will show its only line in this post as well.

To run the macro, use ALT-F8 and select Import (the only macro). Before you run this you will need this data file, and you will need to know its location. A user form will display asking you to choose one of three choices (GPR, PRE, or PNL). Choose PNL and click PROCESS.

The file gather routine will ask you to navigate to the data location. It expects the file to have a filetype of "DAT". After you select this file, you will see diagnostic messages. Then you will be asked if you want to update the spreadsheet. Click on "Yes". The macro will display diagnostic messages and exit. You will notice an additional worksheet, which will be named "PNLxx" where xx is 01-99, depending on how many times you do this. If it had worked, the "Overland" row (data row 2) will be updated (TOI and TGI fields only). I get no updates, and never see the diagnostic message that Ximp_PNL was entered.

The data file has this one line:
"Overland" 08/25/2007 75,456.10 77,037.71

The 75K number should replace 0 as TOI, and 77K should replace TGI. I named the data file "PNLTEST.DAT", but any name will work.

[jwise]

VBAX does not allow a filetype of "DAT", so I changed the name to PNLTEST.doc. The macro will work with this filename, but it will work better if you rename this to PNLTEST.DAT. You can look at this file with notepad; it only has one line which was listed in the previous post.

TIA

[rory]

Your code does not compile, but never mind.
In the middle of your cmdImport routine you have an Unload Me statement. Trying to unload the form in which the code is running is not wise! Try replacing that with Me.Hide instead.

[jwise]

Rory,

Thanks for the tip. When I moved the unload to the end of the routine, it worked.

I do not understand why it works, but it does work.

I also did the compile, and I found 1 error in each of three routines, all being that "rc" was not defined. I added the necessary Dim statements. I did not think about this, so thanks for pointing this out as well. It's got to be one of those "good habits".


To recap this adventure, if you have code like this:

...
Unload Me
...
Call sub1
...
End Sub


Assuming that sub1 is in the "user form" object (or anyplace else), VBA will not be able to find it. Maybe this is Excel 2003 only.


So the code SHOULD look like this:

...
Call Sub1
...
Unload Me
End Sub


The really nasty thing about this is that tracing in the debuigger, it looked like the call was made to the subroutine. I should have known better since the debugger did not switch to the code in the subroutine. In my defense, I've used lots of debuggers that would allow you to "skip" single stepping through subroutines (very handy for already "debugged" code).
So I probably confused that behavior with VB's actions. There was no message of any sort, and it took me several iterations before I ever realized that the sub was being skipped altogether. In fact, I modified this sub to use a "display message" to conform its invocation, and only then realized that the call was being skipped.

Thanks to all responders.