Office SP3 killed my VBA program!

[Julian Milano]

I have the following code:

1 If tmpDateTimeStamp <> "" Then
2 tmpFilename = Mid(OriginalFileName, FolderPos + 1, (Pos1 - 1) -
FolderPos)
3 Else
4 tmpFilename = Mid(OriginalFileName, FolderPos + 1, (ExtPos - 1) -
FolderPos)
5 End If
6 tmpExt = Mid(OriginalFileName, ExtPos + 1, LenFileName)

After applying Office SP3, I got the following results:

Before execution of line #4, variable values were:
==> tmpFilename = "H:\Documents\MS Office\dj_asr_vendor_sales_4685652.dat"
==> OriginalFileName = "H:\Documents\MS
Office\dj_asr_vendor_sales_4685652.dat"

After execution of line #4, variable values were:
==> tmpFilename = "dj_asr_vendor_sales_4685652.dat"
==> OriginalFileName = "dj_asr_vendor_sales_4685652.dat"

Why???

Furthermore, if I type the following in the immediate window,

tmpFilename="XXX"

and the var "OriginalFileName" changed to "XXX" too, immediately.
.............................................................................
..............................................................
Here's the calling code (The first var is a populated var, the others are
populated by the sub):

Call DisectFileName(vDataFileName, vDataFolder, vDataFileName, vDataDTStamp,
vDataExt)

And the sub:

Sub DisectFileName(OriginalFileName As String, tmpFolder As String, _
tmpFilename As String, tmpDateTimeStamp As String, tmpExt As String)
Dim FolderDelimiter As String, ExtDelimiter As String, StampDelimiter As
String
Dim LenFileName As Integer, i As Integer
Dim ExtPos As Integer, FolderPos As Integer, Pos1 As Integer, Pos2 As
Integer

FolderDelimiter = "\"
ExtDelimiter = "."
StampDelimiter = "_"
Pos1 = 0
Pos2 = 0
LenFileName = Len(OriginalFileName)
For i = LenFileName To 1 Step -1
If ExtPos = 0 Then If Mid(OriginalFileName, i, 1) = ExtDelimiter
Then ExtPos = i
If FolderPos = 0 Then If Mid(OriginalFileName, i, 1) =
FolderDelimiter Then FolderPos = i
If Pos1 = 0 Then If Pos2 <> 0 And Mid(OriginalFileName, i, 1) =
StampDelimiter Then _
Pos1 = i
If Pos2 = 0 Then If Mid(OriginalFileName, i, 1) = StampDelimiter
Then Pos2 = i
If ExtPos <> 0 And FolderPos <> 0 Then Exit For
Next i
tmpDateTimeStamp = FindDateTimeStamp(OriginalFileName, Pos1, Pos2,
ExtPos)
tmpFolder = Mid(OriginalFileName, 1, FolderPos)
If tmpDateTimeStamp <> "" Then
tmpFilename = Mid(OriginalFileName, FolderPos + 1, (Pos1 - 1) -
FolderPos)
Else
tmpFilename = Mid(OriginalFileName, FolderPos + 1, (ExtPos - 1) -
FolderPos)
End If
tmpExt = Mid(OriginalFileName, ExtPos + 1, LenFileName)
End Sub

Don't worry about the sub "FindDateTimeStamp", it's not implemented in this
case.

 

[Julian Milano]

Just a note to add. I was using XL2002 SP2 and this code worked fine. Since
upgrading to SP3, it will not work as designed.

 

[Jim Rech]

VB defaults to passing arguments "by reference". Since you did not
designate passing "by value" that's what you're getting. With "by
reference" when you change the passed argument in a sub it also change the
original.

So when you pass in vDataFileName, as the first argument to Sub
DisectFileName it becomes OriginalFileName and subsequent changes to
OriginalFileName change vDataFileName.

The problem is that Sub DisectFileName also passes vDataFileName as its
third argument where it becomes tmpFilename.

So OriginalFileName and tmpFilename point to the same source string, and
changes to one change the other.

--
Jim Rech
Excel MVP

| Just a note to add. I was using XL2002 SP2 and this code worked fine.
Since
| upgrading to SP3, it will not work as designed.
|
| --
|
| Julian Milano
|
|
| | > I have the following code:
| >
| > 1 If tmpDateTimeStamp <> "" Then
| > 2 tmpFilename = Mid(OriginalFileName, FolderPos + 1, (Pos1 - 1) -
| > FolderPos)
| > 3 Else
| > 4 tmpFilename = Mid(OriginalFileName, FolderPos + 1, (ExtPos -
1) -
| > FolderPos)
| > 5 End If
| > 6 tmpExt = Mid(OriginalFileName, ExtPos + 1, LenFileName)
| >
| > After applying Office SP3, I got the following results:
| >
| > Before execution of line #4, variable values were:
| > ==> tmpFilename = "H:\Documents\MS
Office\dj_asr_vendor_sales_4685652.dat"
| > ==> OriginalFileName = "H:\Documents\MS
| > Office\dj_asr_vendor_sales_4685652.dat"
| >
| > After execution of line #4, variable values were:
| > ==> tmpFilename = "dj_asr_vendor_sales_4685652.dat"
| > ==> OriginalFileName = "dj_asr_vendor_sales_4685652.dat"
| >
| > Why???
| >
| > Furthermore, if I type the following in the immediate window,
| >
| > tmpFilename="XXX"
| >
| > and the var "OriginalFileName" changed to "XXX" too, immediately.
| >
|
.............................................................................
| > .............................................................
| > Here's the calling code (The first var is a populated var, the others
are
| > populated by the sub):
| >
| > Call DisectFileName(vDataFileName, vDataFolder, vDataFileName,
| vDataDTStamp,
| > vDataExt)
| >
| > And the sub:
| >
| > Sub DisectFileName(OriginalFileName As String, tmpFolder As String, _
| > tmpFilename As String, tmpDateTimeStamp As String, tmpExt As String)
| > Dim FolderDelimiter As String, ExtDelimiter As String, StampDelimiter As
| > String
| > Dim LenFileName As Integer, i As Integer
| > Dim ExtPos As Integer, FolderPos As Integer, Pos1 As Integer, Pos2 As
| > Integer
| >
| > FolderDelimiter = "\"
| > ExtDelimiter = "."
| > StampDelimiter = "_"
| > Pos1 = 0
| > Pos2 = 0
| > LenFileName = Len(OriginalFileName)
| > For i = LenFileName To 1 Step -1
| > If ExtPos = 0 Then If Mid(OriginalFileName, i, 1) = ExtDelimiter
| > Then ExtPos = i
| > If FolderPos = 0 Then If Mid(OriginalFileName, i, 1) =
| > FolderDelimiter Then FolderPos = i
| > If Pos1 = 0 Then If Pos2 <> 0 And Mid(OriginalFileName, i, 1) =
| > StampDelimiter Then _
| > Pos1 = i
| > If Pos2 = 0 Then If Mid(OriginalFileName, i, 1) = StampDelimiter
| > Then Pos2 = i
| > If ExtPos <> 0 And FolderPos <> 0 Then Exit For
| > Next i
| > tmpDateTimeStamp = FindDateTimeStamp(OriginalFileName, Pos1, Pos2,
| > ExtPos)
| > tmpFolder = Mid(OriginalFileName, 1, FolderPos)
| > If tmpDateTimeStamp <> "" Then
| > tmpFilename = Mid(OriginalFileName, FolderPos + 1, (Pos1 - 1) -
| > FolderPos)
| > Else
| > tmpFilename = Mid(OriginalFileName, FolderPos + 1, (ExtPos -
1) -
| > FolderPos)
| > End If
| > tmpExt = Mid(OriginalFileName, ExtPos + 1, LenFileName)
| > End Sub
| >
| > Don't worry about the sub "FindDateTimeStamp", it's not implemented in
| this
| > case.
| > --
| >
| > Julian Milano
| >
| >
| >
|
|

 

[Julian Milano]

Thanks Jim,

I'm surprised. The code was working fine before this. And I have had similar
problems where in one XL version a code module works fine, then in another,
it fails, with an error. I can't explain it- but I must put up with it.

I'll change my code to ByVal now....