Problem with CanShrinkLines module

[silvastein]

Any thoughts on the following problem?

I LOVE the CanShrinkLines function, which works like a charm until --
it just doesn't. In one DB it is happy as can be, but in another it
will work for a report and then arbitrarily give a #Name? error or ask
for a CanShrinkLines parameter (and then #Error) in my report and
divert to the debugger. I won't have changed a thing on the fields
where it's working when this happens. It appears to single out this
line in the debugger:


If Not IsNull(arrLines(x)) And arrLines(x) <> "" Then


Sometimes giving the message that the line contains no data. For
reference, the Module can be found at
http://office.microsoft.com/en-us/assistance/HA011494721033.aspx and
looks like this:

Public Function CanShrinkLines(ParamArray arrLines())
' Pass this function the lines to be combined
' For example: strAddress =
' CanShrinkLines(Name, Address1, Address2, City, St, Zip)
Dim x As Integer, strLine As String
For x = 0 To UBound(arrLines)
If Not IsNull(arrLines(x)) And arrLines(x) <> "" Then
strLine = strLine & arrLines(x) & vbCrLf
End If
Next
CanShrinkLines = strLine
End Function


Any suggestions would be greatly appreciated.

Many thanks.
JS

 

[Robert Morley]

Unfortunately, VBA doesn't use short-cut evaluation (as far as I know), so
probably what's happening is that one of your arrLines(x) is actually NULL,
and the comparison to "" is causing an error. Try either of the following
instead:

If Nz(arrLines(x),"") <> "" Then
--- or ---
If (arrLines(x) & "") <> "" Then

I'm not sure which is faster in VBA, but they should both work.



Rob

 

[silvastein]

Rob,

Thank you for identifying the problem. I think you're absolutely right
about the arrLines(x) being null. Unfortunately I can't get the two
other options to work in place of the problem line. (I should say I'm
not a very experienced with VBA). Also, as bugs are wont to do, I'm
having difficulty troubleshooting because it breaks at different times
in different ways.

Once I get an error, I have to delete the module and re-create it and
then delete all report fields with the CanShrinkLines and re-create
them. Then the functions works (usually) until POOF, it suddenly
doesn't.

Not sure if I'm hopeless or should keep at it. Any thoughts
appreciated.

JS

 

[Robert Morley]

That sounds like a corrupt VBA project. Try decompiling it and then see
what happens. To decompile:

Click on "Start", then "Run", then enter...

"<Full Path to MSAccess.exe>" /decompile "YourDBName.mdb"
(for example) "C:\Program Files\Microsoft Office\Office10\MSAccess.exe"
/decompile "E:\MyStuff\MyDB.mdb"

....and click OK.

That should prevent the "POOF"'s, as well as having to re-create everything
every time. After that, give the code another try and see what happens.



Rob