Category: vba Common

vbCommon – Chomp & Nibble

' -- When using a loop to build a string  - you often want to remove leading (nibble) or trailing (chomp) vbCrLf  - (but what about trim()  ?!)

' ────────────────────
Function chomp(somestring)
if len(somestring) >= 1 then 
  while  (asc(right(somestring,1)) <= 13 ) 
'this will eat vbTab chr(9) !!!  
  somestring = left(somestring, len(somestring)-1)
 wend 
end if 
chomp = somestring ' or trim(somestring) 'best used earlier
End Function

' ────────────────────
Function nibble(somestring) 
if len(somestring) >= 1 then 
 while  (asc(left(somestring,1)) <= 13 )  
  somestring = right(somestring, len(somestring)-1) 
 wend
end if
  nibble = somestring ' or trim(somestring)

End Function
' ────────────────────

 

vbCommon – illegal filename chars

' -- remove illegal filename characters - note VBS' dim is different than VBA's

' ─────────────
Function stripBadFileChars(somestring)
dim badchar, xyz 'As String in vba - not vbs

badchar = ":;\|/*?" & chr(34) & chr(26) & vbCrLf & vbTab

for xyz = 1 to len(badchar) 
   somestring = StripCharsFrom(somestring, mid(badchar,xyz,1), "_")
   somestring = replace(somestring, "__", "_")
next ' xyz
   somestring = replace(somestring, "__", "_")

   stripBadFileChars = somestring

End Function ' stripBadFilChars

' ─────────────