VBA split() help

[dealer of aces]

I'm working on a program to automate some functionality of a research lab.  One of the problems is that saving the file they want to use the sample ID as part of the name.  Unfortunately I've run into some issues where " and / are used in the sample ID which as you know cannot be saved in Windows.

 

The reason I'm changing variables from sampleID to correctedID is that I want to keep corrected id and my sample ID separate.  I'm doing this because this program also automatically writes a several page report where the sample ID is called multiple times and needs the full sample ID.

 

The bit of my code that I've inserted is this:

 Dim idArray As Variant
    Dim y As Integer

    correctedID = ""

    If InStr(sampleID, """") Then
        idArray = Split(sampleID, """")
        y = UBound(idArray)

        For x = 0 To y
            correctedID = correctedID & idArray(x)
        Next x
    End If
    If InStr(sampleID, "/") Then
        idArray = Split(sampleID, "/")
        y = UBound(idArray)

        For x = 0 To y
            correctedID = correctedID & idArray(x)
        Next x

    Else
    End If


    If correctedID = "" Then
        correctedID = sampleID
    End If

 

Until I added this block of code there were no crashes whatsoever.  With this block of code about 10% of the time excel will hard freeze with no error thrown and require me to kill it using command prompt.  Thoughts?  On the rare event that the code crashes BUT doesn't hard freeze excel it highlights a line near the bottom when I am saving the excel file.  That line is:

 

ThisWorkbook.SaveAs "T:\TCNA Laboratory US\PTCA\PTCA Data\20" & labArray(2) & " Automated\" & labArray(1) & labArray(2) & "\PTCA" & labArray(1) & _
        labArray(2) & " C373-5 " & correctedID & ".xlsm"

 

This is where I am calling the correctedID.  I ran debugging statements to keep an eye on what value is stored in correctedID but it seems correct.

 

If more information is required please let me know and I will provide it asap.

 

Thanks!

 

 

[mariushm]

I'm wondering why you don't use the Replace function : https://www.techonthenet.com/excel/formulas/replace_vba.php

 

As for your code, I see a small problem....

 

You're checking for presence of double quotes and if you find some, you put the changed text in the correctedID variable.

But then, you go and check for "/"  and you're checking in the old sampleID variable and if you find some... you put in correctedID the changed text but you're using the original sampleID as source.

Basically, if the text has both double quotes AND /  , your code lets double quotes go through because the part where you check for / ignores whatever was before in correctedID. 

 

At the very least I would change the code to something like this:

 

  Dim idArray As Variant
  Dim x as Integer
  Dim y As Integer
  
  Dim correctedID as String
  dim tempString as String
  
  tempString = ""
  
  If InStr(sampleID, """") Then
        idArray = Split(sampleID, """")
        y = UBound(idArray)
        For x = 0 To y
            tempString = tempString & idArray(x)
        Next x
  End If
  ' if we made replacements, then tempString contains corrected text SO FAR
  ' if no changes made, then just copy the original text because we use this 
  ' variable from this point, instead of sampleID
  if tempString = "" then tempString = sampleID

  correctedID = ""

  If InStr(tempString, "/") Then
        idArray = Split(tempString, "/")
        y = UBound(idArray)
        For x = 0 To y
            correctedID = correctedID & idArray(x)
        Next x

  End If

  If correctedID = "" Then
        correctedID = testString
  End If 

 

 

And a small tip : Windows doesn't mind if you use  /  in file paths, like   c:/files/my file.txt  , which is great if you're coding in some languages where some combinations of characters get changed when you use \ ... for example "T:\TCNA ..."  could be interpreted by some languages as  "T: [TAB KEY] CNA ..."  .... i don't think it's the case here but it's worth keeping in mind... you can probably easily say "t:/tcna ... " and should work just fine.

 

[dealer of aces]

9 minutes ago, mariushm said:

I'm wondering why you don't use the Replace function : https://www.techonthenet.com/excel/formulas/replace_vba.php

 

As for your code, I see a small problem....

 

You're checking for presence of double quotes and if you find some, you put the changed text in the correctedID variable.

But then, you go and check for "/"  and you're checking in the old sampleID variable and if you find some... you put in correctedID the changed text but you're using the original sampleID as source.

Basically, if the text has both double quotes AND /  , your code lets double quotes go through because the part where you check for / ignores whatever was before in correctedID. 

 

At the very least I would change the code to something like this:

 

  Dim idArray As Variant
  Dim x as Integer
  Dim y As Integer
  
  Dim correctedID as String
  dim tempString as String
  
  tempString = ""
  
  If InStr(sampleID, """") Then
        idArray = Split(sampleID, """")
        y = UBound(idArray)
        For x = 0 To y
            tempString = tempString & idArray(x)
        Next x
  End If
  ' if we made replacements, then tempString contains corrected text SO FAR
  ' if no changes made, then just copy the original text because we use this 
  ' variable from this point, instead of sampleID
  if tempString = "" then tempString = sampleID

  correctedID = ""

  If InStr(tempString, "/") Then
        idArray = Split(tempString, "/")
        y = UBound(idArray)
        For x = 0 To y
            correctedID = correctedID & idArray(x)
        Next x

  End If

  If correctedID = "" Then
        correctedID = testString
  End If 

 

 

And a small tip : Windows doesn't mind if you use  /  in file paths, like   c:/files/my file.txt  , which is great if you're coding in some languages where some combinations of characters get changed when you use \ ... for example "T:\TCNA ..."  could be interpreted by some languages as  "T: [TAB KEY] CNA ..."  .... i don't think it's the case here but it's worth keeping in mind... you can probably easily say "t:/tcna ... " and should work just fine.

 

That's a great catch!  I didn't think to change the sampleID on the second check for symbols.    Also, I completely forgot about the replace function.  I will change my additions to the replace and report back after testing.  Thanks a ton!

 

As for the saving, anytime I have say "c373-5 samples 3" hexagonal.docx" (the technicians put the sampleID into excel and I'm pulling it directly from that cell) the " will throw an error which is why the block of code was included in the first place.

 

[dealer of aces]

4 hours ago, mariushm said:

I'm wondering why you don't use the Replace function : https://www.techonthenet.com/excel/formulas/replace_vba.php

 

As for your code, I see a small problem....

 

You're checking for presence of double quotes and if you find some, you put the changed text in the correctedID variable.

But then, you go and check for "/"  and you're checking in the old sampleID variable and if you find some... you put in correctedID the changed text but you're using the original sampleID as source.

Basically, if the text has both double quotes AND /  , your code lets double quotes go through because the part where you check for / ignores whatever was before in correctedID. 

 

At the very least I would change the code to something like this:

 

  Dim idArray As Variant
  Dim x as Integer
  Dim y As Integer
  
  Dim correctedID as String
  dim tempString as String
  
  tempString = ""
  
  If InStr(sampleID, """") Then
        idArray = Split(sampleID, """")
        y = UBound(idArray)
        For x = 0 To y
            tempString = tempString & idArray(x)
        Next x
  End If
  ' if we made replacements, then tempString contains corrected text SO FAR
  ' if no changes made, then just copy the original text because we use this 
  ' variable from this point, instead of sampleID
  if tempString = "" then tempString = sampleID

  correctedID = ""

  If InStr(tempString, "/") Then
        idArray = Split(tempString, "/")
        y = UBound(idArray)
        For x = 0 To y
            correctedID = correctedID & idArray(x)
        Next x

  End If

  If correctedID = "" Then
        correctedID = testString
  End If 

 

 

And a small tip : Windows doesn't mind if you use  /  in file paths, like   c:/files/my file.txt  , which is great if you're coding in some languages where some combinations of characters get changed when you use \ ... for example "T:\TCNA ..."  could be interpreted by some languages as  "T: [TAB KEY] CNA ..."  .... i don't think it's the case here but it's worth keeping in mind... you can probably easily say "t:/tcna ... " and should work just fine.

 

Follow up question for you.  I replaced my block with the replace function and that worked wonders.  I have another block that someone recommended that I use an on error resume next for.  It seems this was the culprit of the hard freezes.  Essentially I want to see if a directory exists and if it doesn't I want to create it and if it does leave it alone.  That code is as follows:

    On Error Resume Next
    If (Dir("T:\TCNA Laboratory US\Testing Data\20" & labArray(2) & " Data Automated\" & labArray(1) & labArray(2) & "\") = "") = True Then
        MkDir "T:\TCNA Laboratory US\Testing Data\20" & labArray(2) & " Data Automated\" & labArray(1) & labArray(2)
    Else
        
    End If
    On Error GoTo 0

I'm aware that else isn't required in vba and that I could have used

 

If 

'some code

End if

 

but its a habbit from another language.  What in that bit would cause hard freezes?  Could it be the else?  It seems to not crash on that but crashing at the very end (shortly after this) on the exit sub.

 

Thanks for your time.

[mariushm]

2 minutes ago, dealer of aces said:

Follow up question for you.  I replaced my block with the replace function and that worked wonders.  I have another block that someone recommended that I use an on error resume next for.  It seems this was the culprit of the hard freezes.  Essentially I want to see if a directory exists and if it doesn't I want to create it and if it does leave it alone.  That code is as follows:

    On Error Resume Next
    If (Dir("T:\TCNA Laboratory US\Testing Data\20" & labArray(2) & " Data Automated\" & labArray(1) & labArray(2) & "\") = "") = True Then
        MkDir "T:\TCNA Laboratory US\Testing Data\20" & labArray(2) & " Data Automated\" & labArray(1) & labArray(2)
    Else
        
    End If
    On Error GoTo 0

I'm aware that else isn't required in vba and that I could have used

 

If 

'some code

End if

 

but its a habbit from another language.  What in that bit would cause hard freezes?  Could it be the else?  It seems to not crash on that but crashing at the very end (shortly after this) on the exit sub.

 

Thanks for your time.

 

I would rewrite the code like this:

 

On Error Resume Next
dim desiredFolder as string

' make sure you have space characters in all the right places

desiredFolder = "T:\TCNA Laboratory US\Testing Data\20" & labArray(2) & " Data Automated\" & labArray(1) & labArray(2)

' Dir enumerates files and folders in a folder. the vbDirectory constant restricts enumeration to only folders. 
' If the folder doesn't exist, dir can't return any folders or files, so the returned data is an empty string
' so just test if the returned value has a length of 0 characters

If Len(Dir(desiredFolder, vbDirectory)) = 0 Then
   MkDir desiredFolder
End If

// see : https://www.techonthenet.com/excel/formulas/dir.php

 

and don't be afraid of defining variables and using them to simplify things, it reduces the risk of errors popping up, like maybe changing the path in the IF but forgetting to also update the path in the mkdir command..

 

Honestly not sure about the freezes, as I don't code much VBA ... i have coded a lot of VB6 applications, that's why it's easy for me to suggest corrections here, since it's a VB based language....

 

[dealer of aces]

1 hour ago, mariushm said:

 

I would rewrite the code like this:

 

On Error Resume Next
dim desiredFolder as string

' make sure you have space characters in all the right places

desiredFolder = "T:\TCNA Laboratory US\Testing Data\20" & labArray(2) & " Data Automated\" & labArray(1) & labArray(2)

' Dir enumerates files and folders in a folder. the vbDirectory constant restricts enumeration to only folders. 
' If the folder doesn't exist, dir can't return any folders or files, so the returned data is an empty string
' so just test if the returned value has a length of 0 characters

If Len(Dir(desiredFolder, vbDirectory)) = 0 Then
   MkDir desiredFolder
End If

// see : https://www.techonthenet.com/excel/formulas/dir.php

 

and don't be afraid of defining variables and using them to simplify things, it reduces the risk of errors popping up, like maybe changing the path in the IF but forgetting to also update the path in the mkdir command..

 

Honestly not sure about the freezes, as I don't code much VBA ... i have coded a lot of VB6 applications, that's why it's easy for me to suggest corrections here, since it's a VB based language....

 

That worked great.  I don't see any errors arising now.  There must have been some odd syntax issue that would throw an unseen error from time to time.  Thanks for the advice!