Sorry I didn't know about Code Tags. I'll include them in the re-post.
This post is about a function ReplaceString. The function ReplaceString is one of many routines in an application I am building to analyze and document Access VBA code in whatever Access application I choose. I have been building Access applications since the early 1990s, and there is occasionally old VBA code that I would like to revise. So, this application is for the purpose of more easily understanding that old code.
The bolded line below is a SQL example that came from VBA in an old Access application I developed that is to be analyzed:
SQL = "SELECT * FROM tblImport WHERE (([ID] = " & ImportID & ") AND ([FileName] LIKE " & Chr$(34) & "*.enc" & Chr$(34) & "))"
There are three separate calls to ReplaceString to fix this SQL string so that it can be stored in a table in a format that is easy to understand. They are:
(1) SQL = ReplaceString(" & ",SQL, "") which should replace the concatenating " & " between sub-strings of SQL with an empty string,
(2) SQL = ReplaceString(Chr$(34), SQL, "") which should remove the quotes surrounding sub-strings of SQL, and
(3) SQL = ReplaceString("Chr(34)", SQL, "'") which should replace the actual occurrences of "Chr$(34) - a quote mark - with single quotes
The SQL string the function should return is this:
SQL = SELECT * FROM tblImport WHERE (([ID] = ImportID ) AND ([FileName] LIKE '*.enc')) where ID and FileName are variables whose values are ImportID and '*.enc' respectively.
Here is the code for ReplaceString. Please note well the order of the arguments to the function. They are (1) the substring to be searched for, (2) the complete string to be searched, and (3) the replacement string:
Code:
Function ReplaceString(FindStr As String, Str As String, ReplaceWith As String) As StringDim s As String, i As Integer, Lf As Integer
Rem REPLACE EVERY INSTANCE OF FindStr OCCURRING IN Str WITH ReplaceWith
Lf = Len(FindStr) ' length of the string to find
s = Str
Do
i = InStr(1, s, FindStr, vbTextCompare) ' where does FindStr occur in Str?
If i = 0 Then Exit Do
s = Mid$(s, 1, i - 1) & ReplaceWith & Mid$(s, i + Lf) ' Str after replacement
Loop ' loop until all replacements have been made.
ReplaceString = s ' return value
End Function
That's it. There are no empty strings passed to the function. The input string is complex simply because I build the SQL in the app being analyzed and documented within the VBA code for specific purposes. For example, the SQL given above asks for all records from the table tblImport, whose "ID" have a given value and whose Filename column ends with the extension ".enc". No big mystery, but it needed to be built within a routine that returned a group of files all having the same ImportID. But that app is another story. The question I have is about the app I am currently building that will analyze and document VBA code in any app I need to have it done.
And, by "failed", it returns a result, just not the one I intended. I believe the issue is quotes within a string. VBA doesn't like unbalanced quotes and balances them if I remove one. As I stated earlier, I recall facing this problem years ago.