Parameter passed incorrectly (empty)

[DJAXE]

Fairly strange event - hopefully I can explain it properly.

I have a fairly hefty Excel workbook with quite a bit of code in it - one particular event I keep seeing is that a parameter doesn't get passed correctly.

For example the line:
RowLockDown "ACCT", "equals", 6299

Calls the subroutine "RowLockDown" which is declared as follows:

Sub RowLockDown(ByVal sDim as string, byval sOp as string, byval vCode as variant)

However, occasionally (ie, not every time), when the RowLockDown routine executes the sDim (first) parameter is equal to a null string.

Indeed, if I try to populate sDim using the Immediate Window in runtime, it loses the value as soon as I advance the code.

Very peculiar.

I've been through a large number of processes trying to clean this code up such as
- running code cleaner (many times)
- ensuring no modules are too big
- removing all public variables
amongst others.

Anyone ever seen this before and have any suggestions?

 

[jkpieterse]

Where have you set the break point; on the sub's declaration line itself?
Do you experience the same problem if you just add a MsgBox sDim as the first command in the sub?

 

[DJAXE]

On the break-point - I know where you're going with that question: The parameter only shows as populated after the Sub line has passed. In this case, I didn't have a breakpoint set, the code ran until it had an error (which happened when a line of code tried to use the parameter variable and it was empty when it shouldn't have been), I then stepped it back a bit.

When the error came I didn't think to give a Msgbox call a try.

It's very intermittant (I can't get it to happen even if I want to).

 

[jkpieterse]

Sounds like your code needs cleaning, have you used Rob Bovey's code cleaner on the file yet? www.appspro.com

 

[DJAXE]

Yeah. As noted in first post, yes, the Code Cleaner (wonderful program) has been used.

 

[jkpieterse]

Sorry about that.

I'd put a check in the routine that stops the code as soon as the argument is a null string and check the call stack to see how the routine was called. Are you absolutely sure the argument isn't passed correctly (calling sub/function does show a non-null string)?

Any implicit conversions in your code (passing a variant to the routine for example)?

What calls the proc, a normal routine, an event routine, ... , Excel cells (UDF)?

 

[DJAXE]

No problems.

The problem with leaving a check is that this particular thing hardly ever happens. In most cases (more than 99%), the parameter is passed correctly and no problems arise.

Also note that I've seen this happen with other routines, but this is the most recent example. There doesn't seem to be much of a pattern to when it happens or doesn't happen.

To answer your question, in this particular case the call stack is
- Excel button click event
- Other subroutine
- RowLockdown

The call is exactly as I wrote it in the first post - the parameters are populated explicitly by strings rather than from variables.

I should point out that this workbook will also crash Excel (as in, Excel has stopped working and so on and so forth) - again intermittently. My theory is that there is some problem in the code that Excel can sometimes handle and sometimes not. In this case, I figured I'd go with a symptom that others may have seen before rather than just say "my workbook is crashing!"

 

[Norie]

Why not pass it as a variant?

You can check if it's Null in the RowLockDown sub and take appropriate action/inaction.

Oops, think Jan already suggested something like that.

PS How/when were you trying to set the value in the Immediate window?

 

[jkpieterse]

The call stack you describe is a case where the argument has a null string?

If your workbook is causing crashes of Excel, I suspect a corruption of the file.
What Excel version is this?

 

[DJAXE]

My apologies for not using code tags in my first post.

Norie's questions:

* The variant parameter is declared as such because it can be either a string or a number and the formatting is important.

* Checking for a Null in RowLockDown is more a workaround than a fix -and I'd need to store the value somewhere else so that I can retrieve it within the RowLockDown subroutine. The RLD procedure is used by multiple routines and passes different parameter values depending on the situation.

* As for the immediate window, I was trying to just type

sDim="ACCT"

During Runtime. If I then checked the value of the variable, it showed correctly, but as soon as I hit F5 or F8 to advance the code, the variable was cleared again.

jkpieterse's questions:
* Yes, the call stack I listed is where the argument produces a null string (again - not all the time).

* The chance of a corruption is not beyond the bounds of reasoning, I have considered it. One of my previous actions was to transfer all sheets and code to a new workbook.

* We're on Excel 2003 SP3.

Today I think I had a bit of a breakthrough. I discovered that the VBA version on some machines was different from others. Some reported VBA version 6.5.1053, others 6.5.1024 - the earlier version had problems (crashes, errors), the later did not.

It appears that there was an error which was patched by MS, but I can't find a reference to it on the 'net.

The thing which bugs me (no pun intended) is that there's obviously something in my code which is causing an error, I just can't figure out what it is.