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

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

vbaCommon – GetEnv (Environment)

 

' ───────

Private Function getENV(strReturn As String)
  'Example ChDir getENV("userprofile") & "\Desktop\"
Dim EnvString, Indx, Msg, PathLen    ' Declare variables.
For Indx = 1 To Len(Environ(Indx)) + 2
    EnvString = Environ(Indx)  ' Get environment
    If UCase(Left(EnvString, Len(strReturn))) = UCase(strReturn) Then
      getENV = Mid(EnvString, Len(strReturn) + 2)
    End If
 Next Indx 'Loop Until EnvString = ""

End Function
' ───────

vbCommon – stripIllegalChars

Private Function stripIllegalChars(strTest As String)
Dim strTemp, testThis As String
Dim kount As Integer

strTemp = ""
strTest = Trim(strTest)
For kount = 1 To Len(strTest)
testThis = Mid(strTest, kount, 1)
If InStr(".|\/?*:<>' ", testThis) > 0 Or Asc(testThis) < 32 Or Asc(testThis) = 34 Or Asc(testThis) > 129 Then
 'strTemp = strTemp & "_"
 Else: strTemp = strTemp & Mid(strTest, kount, 1)
End If
Next kount
stripIllegalChars = IIf(Len(strTemp) < 1, "Missing_Subject" & Int(Rnd(1) * 1000), strTemp)
End Function
' ───────