API tools faq
paste
Advertisement
AlanElston

WotchaGotInJaStingyMatey

AlanElston
Feb 28th, 2019
227
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VisualBasic 12.58 KB | None | 0 0
raw download clone embed print report
  1. Sub WtchaGot(ByVal strIn As String) ' http://www.excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string?p=10943#post10943
  2. Rem 1  ' Output "sheet hardcopies"
  3. '1a) Worksheets     'Make a Temporary Sheet, if not already there, in Current Active Workbook, for a simple list of all characters
  4.    If Not Evaluate("=ISREF(" & "'" & "WotchaGotInString" & "'!Z78)") Then '   ( the '  are not important here, but iin general allow for a space in the worksheet name like  "Wotcha Got In String"
  5.    Dim Wb As Workbook '                                   ' ' Dim:  ' Preparing a "Pointer" to an Initial "Blue Print" in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Objec of this type ) . This also us to get easily at the Methods and Properties throught the applying of a period ( .Dot) ( intellisense )                     '
  6.     Set Wb = ActiveWorkbook '  '                            Set now (to Active Workbook - one being "looked at"), so that we carefull allways referrence this so as not to go astray through Excel Guessing inplicitly not the one we want...         Set: Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191                                '
  7.     Wb.Worksheets.Add After:=Wb.Worksheets.Item(Worksheets.Count) 'A sheeet is added and will be Active
  8.    Dim ws As Worksheet '
  9.     Set ws = ActiveSheet 'Rather than rely on always going to the active sheet, we referr to it Explicitly so that we carefull allways referrence this so as not to go astray through Excel Guessing implicitly not the one we want...    Set: Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191            ' Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191
  10.     ws.Activate: ws.Cells(1, 1).Activate ' ws.Activate and activating a cell sometimes seemed to overcome a strange error
  11.     Let ws.Name = "WotchaGotInString"
  12.     Else ' The worksheet is already there , so I just need to set my variable to point to it
  13.     Set ws = ThisWorkbook.Worksheets("WotchaGotInString")
  14.     End If
  15. '1b) Array
  16. Dim myLenf As Long: Let myLenf = Len(strIn)  '            ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in.  '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )       https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
  17. Dim arrWotchaGot() As String: ReDim arrWotchaGot(1 To myLenf + 1, 1 To 2) ' +1 for header  Array for the output 2 column list.  The type is known and the size,  but I must use this ReDim  method simply because the dim statement  Dim( , )  is complie time thing and will only take actual numbers
  18. Let arrWotchaGot(1, 1) = Format(Now, "DD MMM YYYY") & vbLf & "Lenf is   " & myLenf: Let arrWotchaGot(1, 2) = Left(strIn, 20)
  19. Rem 2  String anylaysis
  20. 'Dim myLenf As Long: Let myLenf = Len(strIn)
  21. Dim cnt As Long
  22.     For cnt = 1 To myLenf ' ===Main Loop========================================================================
  23.    ' Character analysis: Get at each character
  24.    Dim Caracter As Variant ' String is probably OK.
  25.    Let Caracter = Mid(strIn, cnt, 1) ' '    the character in strIn at position from the left of length 1
  26.    '2a) The character added to a single  WotchaGot  long character string to look at and possibly use in coding
  27.    Dim WotchaGot As String ' This will be used to make a string that I can easilly see and also is in a form that I can copy and paste in a code line  required to build the full string of the complete character string
  28.        '2a)(i) Most common characters and numbers to be displayed as "seen normally" ' -------2a)(i)--
  29.        If Caracter Like "[A-Z]" Or Caracter Like "[0-9]" Or Caracter Like "[a-z]" Then ' Check for normal characters
  30.            'SirNirios
  31.            If Not cnt = 1 Then ' I am only intersted in next line comparing the character before, and if i did not do this the next line would error if first character was a  "normal"  character
  32.                If Not cnt = myLenf And (Mid(strIn, cnt - 1, 1) Like "[A-Z]" Or Mid(strIn, cnt - 1, 1) Like "[0-9]" Or Mid(strIn, cnt - 1, 1) Like "[a-z]") Then  ' And (Mid(strIn, Cnt + 1, 1) Like "[A-Z]" Or Mid(strIn, Cnt + 1, 1) Like "[0-9]" Or Mid(strIn, Cnt + 1, 1) Like "[a-z]") Then
  33.                 Let WotchaGot = WotchaGot & "|LinkTwoNormals|"
  34.                 Else
  35.                 End If
  36.             Else
  37.             End If
  38.         Let WotchaGot = WotchaGot & """" & Caracter & """" & " & " ' This will give the sort of output that I need to write in a code line, so for example if I have a123 , this code line will be used 4 times and give like a final string for me to copy of   "a" & "1" & "2" & "3" &      I would phsically need to write in code  like  strVar = "a" & "1" & "2" & "3"   -  i could of course also write  = "a123"   but the point of this routine is to help me pick out each individual element
  39.        Else ' Some other things that I would like to "see" normally - not "normal simple character" - or by a VBA constant, like vbCr vbLf  vbTab
  40.         Select Case Caracter ' 2a)(ii)_1
  41.          Case " "
  42.            Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  43.           Case "!"
  44.            Let WotchaGot = WotchaGot & """" & "!" & """" & " & "
  45.           Case "$"
  46.            Let WotchaGot = WotchaGot & """" & "$" & """" & " & "
  47.           Case "%"
  48.            Let WotchaGot = WotchaGot & """" & "%" & """" & " & "
  49.           Case "~"
  50.            Let WotchaGot = WotchaGot & """" & "~" & """" & " & "
  51.           Case "&"
  52.            Let WotchaGot = WotchaGot & """" & "&" & """" & " & "
  53.           Case "("
  54.            Let WotchaGot = WotchaGot & """" & "(" & """" & " & "
  55.           Case ")"
  56.            Let WotchaGot = WotchaGot & """" & ")" & """" & " & "
  57.           Case "/"
  58.            Let WotchaGot = WotchaGot & """" & "/" & """" & " & "
  59.           Case "\"
  60.            Let WotchaGot = WotchaGot & """" & "\" & """" & " & "
  61.           Case "="
  62.            Let WotchaGot = WotchaGot & """" & "=" & """" & " & "
  63.           Case "?"
  64.            Let WotchaGot = WotchaGot & """" & "?" & """" & " & "
  65.           Case "'"
  66.            Let WotchaGot = WotchaGot & """" & "'" & """" & " & "
  67.           Case "+"
  68.            Let WotchaGot = WotchaGot & """" & "+" & """" & " & "
  69.           Case "-"
  70.            Let WotchaGot = WotchaGot & """" & "-" & """" & " & "
  71.           Case "_"
  72.            Let WotchaGot = WotchaGot & """" & "_" & """" & " & "
  73.           Case "."
  74.            Let WotchaGot = WotchaGot & """" & "." & """" & " & "
  75.           Case ","
  76.            Let WotchaGot = WotchaGot & """" & "," & """" & " & "
  77.           Case ":"
  78.            Let WotchaGot = WotchaGot & """" & ":" & """" & " & "
  79. '          Case " "
  80. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  81. '          Case " "
  82. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  83. '          Case " "
  84. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  85. '          Case " "
  86. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  87. '          Case " "
  88. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  89. '          Case " "
  90. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  91. '          Case " "
  92. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  93. '          Case " "
  94. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  95. '          Case " "
  96. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  97. '          Case " "
  98. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  99. '          Case " "
  100. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  101. '          Case " "
  102. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  103. '          Case " "
  104. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  105. '                   ' 2a)(ii)_2
  106. '          Case " "
  107. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  108. '          Case " "
  109. '           Let WotchaGot = WotchaGot & """" & " " & """" & " & "
  110.          Case vbCr
  111.            Let WotchaGot = WotchaGot & "vbCr & "  ' I actuall would write manually in this case like     vbCr &
  112.          Case vbLf
  113.            Let WotchaGot = WotchaGot & "vbLf & "
  114.           Case vbCrLf
  115.            Let WotchaGot = WotchaGot & "vbCrLf & "
  116.           Case """"   ' This is how to get a single   "    No one is quite sure how this works.  My theory that,  is as good as any other,  is that  syntaxly   """"    or  "  """  or    """    "   are accepted.   But  in that the  """  bit is somewhat strange for VBA.   It seems to match  the first and Third " together as a  valid pair   but  the other  " in the middle of the  3 "s is also syntax OK, and does not error as    """     would  because  of the final 4th " which it syntaxly sees as a valid pair matched simultaneously as it does some similar check on the  first  and Third    as a concluding  string pair.  All is well except that  the second  "  is captured   within a   accepted  enclosing pair made up of the first and third  "   At the same time the 4th  "  is accepted as a final concluding   "   paired with the   second which it is  using but at the same time now isolated from.
  117.           Let WotchaGot = WotchaGot & """" & """" & """" & """" & " & "                                ' The reason why  ""  ""   would not work is that    at the end of the  "" the next empty  character signalises the end of a  string pair, and only if  it saw a " would it keep checking the syntax rules which  then lead in the previous case to  the situation described above.
  118.          Case vbTab
  119.            Let WotchaGot = WotchaGot & "vbTab & "
  120.           ' 2a)(iii)
  121.            Case Else
  122.              WotchaGot = WotchaGot & "Chr(" & Asc(Caracter) & ")" & " & "
  123.             'Let CaseElse = Caracter
  124.        End Select
  125.         End If ' End of the "normal simple character" or not ' -------2a)------Ended-----------
  126.    '2b)  A 2 column Array for convenience of a list
  127.     Let arrWotchaGot(cnt + 1, 1) = cnt & "           " & Caracter: Let arrWotchaGot(cnt + 1, 2) = Asc(Caracter) ' +1 for header
  128.    Next cnt ' ========Main Loop=================================================================================
  129.    If WotchaGot <> "" Then
  130.      Let WotchaGot = Left(WotchaGot, Len(WotchaGot) - 3) ' take off last " & "    ( 2 spaces one either side of a  & )
  131.                                                                                                                     '   If Not Mid(WotchaGot, Len(WotchaGot)) = """" Then Let WotchaGot = WotchaGot & """" ' Last "
  132.     Let WotchaGot = Replace(WotchaGot, """ & |LinkTwoNormals|""", "", 1, -1, vbBinaryCompare)
  133.      Dim Gelined As String: Let Gelined = Replace(WotchaGot, " & vbCr & vbLf", vbCr & vbLf & "& vbCr & vbLf", 1, -1, vbBinaryCompare)   ' Put a new line in display where a new line is
  134.    Else
  135.     End If
  136. Rem 3 Output
  137. '3a) String
  138. MsgBox Prompt:=WotchaGot: Debug.Print WotchaGot: Debug.Print: Debug.Print Gelined ' Hit Ctrl+g from the VB Editor to get a copyable version of the entire string
  139. '3b) List
  140. Dim NxtClm As Long: Let NxtClm = 1 ' In conjunction with next  If  this prevents the first column beine taken as 0 for an empty worksheet
  141. If Not ws.Range("A1").Value = "" Then Let NxtClm = ws.Cells.Item(1, Columns.Count).End(xlToLeft).Column + 1
  142.  Let ws.Cells.Item(1, NxtClm).Resize(UBound(arrWotchaGot(), 1), UBound(arrWotchaGot(), 2)).Value = arrWotchaGot()
  143.  ws.Cells.Columns.AutoFit
  144. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!