Category: vba Common

vB Regex Error Detection

This is a sample of "stub" code using REGEX (regular expressions) to find predefined Error words expecially when Internet Explorer unexpectedly asks for a Certificate.

    1. detect and skip  Windows Certificate Errors or similar Error page that IE may surprise the user with
    2. Fill in a Login Form
  • To WORK: it needs you to identify the (login or other) form elements
    1. identify a unique STRING in the unexpected  Error (Certificate) page
'________________________
Function checkForError(strErr,strTag)' returns 1 of 3 strings FULL LARGE INNERTEXT STRING IF ERR FOUND, ELSE "PASS"
Dim names,ele,innerText,outerText,temp,bFlag' used as string to debug
temp=""
bFlag=False' flag True if ERR FOUND, false if ERR NOT FOUND
Set names=objIE.Document.getElementsByTagName(strTag)

' sample: needs IE page object to be defined and navigated to as current page, having login form elements

'√' CHECKING for and bypassing MICROSOFT CERTIFICATE WARNING

isUnsafe=findClickOkIfUnsafe("not recommended",testLinks)
'bad string is first param signalling certificate error

If isUnsafe>=0then testLinks(isUnsafe-1).Click
' (1) works if HARD CODED FROM OBSERVATION - code returns
' (2) ??
' testLinks(isUnsafe).Click ' using index as boolean????  

call waitForReady(objIE)    ' do this after each click on menu
end If   
   ' self documenting comments by doing "find" of H4 tag or any desired flag/tag

' LEAVING MICROSOFT CERTIFICATE WARNING INTO LOG IN PAGE
'_________ functions / subroutines _________

Function findClickOkIfUnsafe(strInnerText,someArray) ' returns index if any member of someArray has "bad string"
Dim k, foundAt, strtmp

foundAt=-1
strtmp=""
'msgbox "array length: " & someArray.length , , "from Function findClickOkIfUnsafe"          ' for debugging, tells you where you are
for k = 0 to someArray.length-1    'LBound(someArray) to UBound(someArray)
If (boolRegExMatch ( someArray( k ).innerText, trim(strInnerText)) > 0)then
        'booltemp = True '( True OR booltemp)
foundAt= k
exit for
end if
next 'k

findClickOkIfUnsafe=foundAt
 ' if for/next never entered then returns -1 from second line

End Function

'________________________
Function boolRegExMatch(regx,strng) ' returns COUNT no it's NOT boolean!!!!
Dim regEx, Match,Matches, n, tmp ' Create variable.
Set regEx=NewRegExp ' Create a regular expression.
regEx.Pattern=regx ' Set pattern.
regEx.IgnoreCase=True ' Set case insensitivity.
regEx.Global=True ' Set global applicability.
Set Matches=regEx.Execute(strng) ' Execute search.
boolRegExMatch=Matches.count
Set Matches=Nothing
Set RegEx=Nothing
End Function

'________________________

Function checkForError(strErr,strTag)
' returns 1 of 3 strings full INNERTEXT STRING IF error found, ELSE "PASS"
Dim names,ele,innerText,outerText,temp,bFlag' used as string to debug
temp=""

bFlag=False ' flag True if ERR FOUND, false if ERR NOT FOUND
Set names=objIE.Document.getElementsByTagName(strTag)

For each ele in names
innerText=ele.innerText
If (boolRegExMatch(strErr,innerText)>0)Then
 
temp = "Fail: "&innerText' innerText

exit For   ' only reports first error found!?
Else
temp="PASS"
End If
Next
checkForError=temp
End Function
'________________________
Function fnLogin(uid,pwd)
Dim inputs,strPageStatus
Set inputs=objIE.Document.getElementsByTagName("INPUT")

inputs(2).Value=uid
inputs(3).Value=pwd
inputs(4).Click

' on Error GoTo 0
' If Err.Number then strPageStatus = "FAILERROR: CLOSE DUPLICATE BROWSER SESSION" & vbCrlf & Err.Description else
waitForReady(objIE)
strPageStatus=checkForError(".*password.*incorrect.*|Prohibite.*","DIV")'prefixes "Failed:" to InnerText
' End If
fnLogin=strPageStatus
End Function

'________________________

Function checkForError(strErr,strTag)' returns 1 of 3 strings full INNERTEXT STRING IF ERR FOUND, ELSE "PASS"
Dim names, ele, innerText, outerText, temp, bFlag  ' used as string to debug
temp=""

bFlag=False ' flag True if ERR FOUND, false if ERR NOT FOUND
Set names=objIE.Document.getElementsByTagName(strTag)

For each ele innames
innerText=ele.innerText
If (boolRegExMatch(strErr,innerText)>0)Then
'If Instr(1, innerText, strErr) Then temp="Fail: "&innerText' innerText

exit For  ' only reports first error found!?
Else
temp="PASS"
End If
Next
checkForError=temp
End Function
'________________________

VBS – time stamp: options demo

VBS will run in Windows as a stand alone script just like JS (JavaScript) and BATCH files.

'save as ANSI TXT file with extenstion ".vbs" 
'saving as UTF and Unicode will show "Error in Line 1"
nowis = Now
msgbox minuteStamp, vbOKOnly,"MinuteStamp"
msgbox timeStamp, vbOKOnly, "TimeStamp"
msgbox timestamphhmmss(nowis), vbOKOnly, "Time Stamp hh mm ss"

' ==== 'right' forces two digit values ===
Function minuteStamp() 
 minuteStamp = Year(Now) & "_" & _
 Right("0" & Month(Now),2) & "_" & _
 Right("0" & Day(Now),2) & "_" & _ 
 Right("0" & Hour(Now),2) & _
 Right("0" & Minute(Now),2) 
 '& _ Right("0" & Second(Now),2) 
End Function

' ============
Function timeStamp()
 timeStamp = Year(Now) & "_" & _
 Right("0" & Month(Now),2) & "_" & _
 Right("0" & Day(Now),2) & "_" & _ 
 Right("0" & Hour(Now),2) & _
 Right("0" & Minute(Now),2) & _
 Right("0" & Second(Now),2) 
End Function
' ======


Function timestamphhmmss(someTime)
 timestamphhmmss = FormatDateTime(someTime,vbshorttime) & ":" & right("0" & Second(someTime), 2)
End Function
 
'Constant Value Description from Microsoft® HELP
'vbGeneralDate 0 Display a date and/or time. If there is a date part, display it as a short date. If there is a time part, display it as a long time. If present, both parts are displayed.
' vbLongDate 1 Display a date using the long date format specified in your computer's regional settings.
' vbShortDate 2 Display a date using the short date format specified in your computer's regional settings.
' vbLongTime 3 Display a time using the time format specified in your computer's regional settings.
' vbShortTime 4 Display a time using the 24-hour format (hh:mm).
'_______________________________

 

Excel Macro Autonumber NonBlanks

Sub NumbernonBlankTestLines()
' user must manually highlight range in Column A
Dim msgs, qy, rep
Dim testcount, r, c As Integer,
Dim startHere As Range, rngSelected As Range
Dim markblank, enforcesinglerowblank As Boolean
enforcesinglerowblank = False

' MsgBox "last cell is " & findlastusedcell()
' use if we add feature to insert column A and auto-select range

testcount = 0

Set startHere = ActiveCell
Set rngSelected = Selection

If TypeName(Selection) = "Range" Then
If Selection.Areas.Count = 1 Then
' MsgBox "selected rows is " & rngSelected.Rows.Count & vbCrLf & "columnn is " & ActiveCell.Column

If rngSelected.Rows.Count < 2 Or ActiveCell.Column <> 1 Then GoTo oops

' magic starts here
'Selection.Name = "eraseme"
markblank = False
'MsgBox rngSelected.Rows.Count

If WorksheetFunction.CountA(rngSelected) > 1 Then
qy = MsgBox("OVERWRITE existing DATA?", vbOKCancel, "WARNING!")
If qy = vbCancel Then
Exit Sub
Else
rngSelected.ClearContents
End If
'WorksheetFunction.CountA(rngSelected)
End If
For r = 1 To rngSelected.Rows.Count

rngSelected(r).Activate

If r = 1 Then
If rngSelected(r).Offset(0, 1) = "" Then
MsgBox rngSelected(r).Offset(0, 1).Value
GoTo oops
End If
End If

'case not blank
If rngSelected(r).Offset(0, 1) > "" Then
If enforcesinglerowblank And markblank Then
GoTo doublelineerror
ElseIf Not markblank Or Not enforcesinglerowblank Then
markblank = Not markblank
rngSelected(r).Value = testcount + 1
' markblank = False Else markblank = True
testcount = testcount + 1
End If
'case blank
ElseIf rngSelected(r).Offset(0, 1) = "" Then
If enforcesinglerowblank And Not markblank Then
GoTo doublelineerror
ElseIf markblank Then
markblank = Not markblank
End If
Else: MsgBox "Unknown", vbCritical, "Error!"

End If

Next r
' GoTo A1
MsgBox "Numbered " & testcount & " rows"
'measure column B begin and end
'make temporary range from A2: to match B begin to B end

'tbd select column A
'if selection < 2 rows warn and exit
'for each row in A, test adjacent cell B has value - number A, set flag notblank
'if flag notblank then next row should be blank, else warn andexit else skip to next
'if flag notblank, then next row should NOT be blank, else warn and exit, else number A
Else
MsgBox "Please select only one area.", vbInformation
End If
End If
Exit Sub

doublelineerror:
MsgBox "Only one row per test, MUST be separated by exactly one blank row: see Test# " & testcount, vbCritical, "Halted / Config Error"

Exit Sub

oops:
MsgBox "HALTED: must select multiple continuous rows within column A, alongside alternating data in column B" & vbCrLf _
& "and first selected row is NOT blank", vbCritical, "SELECTION ERROR"

End Sub

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 ' dim is different in VBA than in VBS

' ─────────────
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

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