How necessary is this code?

[jguscs]

I'm looking to find out how necessary the function is that replaces a given character in a string with another character (ReplaceG).
I'm interested in compacting this code as much as possible without losing its functionality.
As you can tell, this is part of the code for importing a spreadsheet into Access by browsing.
If you want to see the rest of the code, let me know.

Public Function ReplaceG(sString As String, sFind As String, sReplace As String) As String
On Error GoTo Proc_Err
Dim lPtr As Long
Dim sTmp, sRetVal As String
sTmp = sString
If sFind <> sReplace Then
lPtr = InStr(1, sTmp, sFind)
Do Until lPtr = 0
sRetVal = sRetVal & Left(sTmp, lPtr - 1) & sReplace
sTmp = Right(sTmp, Len(sTmp) - (lPtr + Len(sFind) - 1))
lPtr = InStr(1, sTmp, sFind)
Loop
End If
ReplaceG = sRetVal & sTmp
Proc_Exit:
Exit Function
Proc_Err:
MsgBox Err.Description
Resume Proc_Exit
End Function

Public Function GetOpenFileNameGs(objForm As Form) As String
Dim sInitialDir, sFileFilter As String
Dim lReturn, lFlag As Long
Dim OpenFile As OPENFILENAME
sInitialDir = "C:\"
sFileFilter = "Excel Files (*.XLS)|*.XLS|Comma Delimited Files (*.CSV)|*.CSV|" & _
"DEL File (*.DEL)|*.DEL|All Files|*.*||"
lFlag = 0
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hWndOwner = objForm.Hwnd
OpenFile.hInstance = Application.hWndAccessApp
OpenFile.lpstrFilter = ReplaceG(sFileFilter, "|", vbNullChar)
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = sInitialDir
OpenFile.lpstrTitle = "Open File"
OpenFile.flags = lFlag
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
GetOpenFileNameGs = ""
Else
GetOpenFileNameGs = ReplaceG(OpenFile.lpstrFile & "", vbNullChar, "")
End If
End Function

 

[dcx693]

There is a Replace function in Access (I have XP running right now) that has the functionality of that ReplaceG function. It's unlikely that a built-in function would be slower than one you write yourself.

 

[SforSoftware]

Hi,

The internal Replace-funtion is supported since Acc2k, so in Acc97 you've to use a procedure like this.
Some remarks at the code:
1. lPtr As Long -> I'd think an Integer would do as well (up to more than 32000 charachters in sString)

2. sTmp is not declared as a String, but as a Variant. Use:
Dim sTmp As String, sRetVal As String

I think this procedure will be some quicker:

Public Function ReplaceG(ByVal sString As String, sFind As String, sReplace As String) As String
  On Error GoTo ReplaceG_Err

  Dim i As Integer
  
  If sFind = sReplace Then GoTo ReplaceG_Exit

  For i = 1 To Len(sString)
    If Mid(sString, i, 1) = sFind Then Mid(sString, i, 1) = sReplace
  Next i

  ReplaceG = sString

ReplaceG_Exit:
  Exit Function

ReplaceG_Err:
  MsgBox Err.Number & ": " & Err.Description, , "An Error Occured!"
  Resume ReplaceG_Exit
End Function

Excuse me, I didn't test this code.

Greetz
Bert

 

[jguscs]

Thanks for bringing the Replace function to my attention.
Indeed, it is available in 2k (which I am using and am not interested in being backwards-compatible to 97... but thanks for the tweaked procedure, SforSoftware).

I also need to point out that this code is not my own, it is... someone's... that I found posted somewhere on this board.

One question that comes to mind is...
Is it even necessary to replace the "|" characters with the vbNull character?
OpenFile.lpstrFilter = ReplaceG(sFileFilter, "|", vbNullChar)

How about if I just use:
sFileFilter = "Excel Files (*.XLS)" & Chr(0) & "*.XLS" & Chr(0) & "Comma Delimited Files (*.CSV)" & Chr(0) & "*.CSV"& Chr(0) & "DEL File (*.DEL)" & Chr(0) & "*.DEL" & Chr(0) & "All Files" & Chr(0) & "*.*" & Chr(0) & "" & Chr(0) & ""

Instead of:
sFileFilter = "Excel Files (*.XLS)|*.XLS|Comma Delimited Files (*.CSV)|*.CSV|DEL File (*.DEL)|*.DEL|All Files|*.*||"