Detect hidden characters in VBA

[YasserKhalil]

No not really. May be 160 is an example

[LisaGreen]

May I suggest the you write something to display ALL/EVERYTHING in the immediate window.

THen you perhaps have a clearer, more exact idea of which characters, in whatever language, are printable/presentable on screen.

You need information Yasser.
I'd hazard a guess that the majority of users here do not use whatever language you are presenting.
I'm not certain it's actually Arabic. :-)

But! You need to know, in that language!!! What character values are invisible/don't show up.
If you need code to do that we can go further with that.

You keep saying "invisible characters". But you need a list!!!

Lisa

[YasserKhalil]

I thought there is a UDF for example that can list the hidden characters in general regardless the language and in this UDF we can add more codes for those characters...
Thanks anyway for help

[LisaGreen]

Hello Yasser,

To my knowledge there is no way of "detecting hidden characters" You need to know which characters are "hidden".

This applies for things like carraige returns and all sorts of stuff.

And then we get into, what is a hidden character anyway!

A reasonable introduction is to look at a immediate window list of characters from zero to whatever. Print a number, the character, and the code.

Lisa

[LisaGreen]

Does this help??

But you still have to know what you don't want!!!!!

Lisa

Code: Select all

Function fncStripChrsEx( _
            spStringToStrip As String, _
            spChrsToStrp As String, _
            spChrPairs As String _
            ) _
            As String
' Strip a list of given chrs from a string.
' Skip anything in the ChrPair sets.
'
' Made for multilinguality... ex Arabic.
' Assumes each chr is just that. a single chr
'  that can be picked up with the MID$ function.
'  This means that different forms of the same chr
'   need to be listed... ex Aa.
'
' Format of spChrPairs...
'  "Chr1Chr2,Chr3Chr4,....,ChrnChrm"
'

Dim ilN As Integer
Dim ilM As Integer
Dim slNewString As String
Dim slChr As String
Dim slChrToStrip As String
Dim ilLen As Integer
Dim slChrsToStrp() As String
Dim lnglCurrentChrToCheck As Long
Dim slChrPairs() As String
Dim lnglO As Long
Dim lnglUBChrsToStrip As Long
Dim lnglStartPairChr As Long
Dim slEndChrPair As String
Dim lnglPairsLen As Long

' Do a crude check of the spChrPairs.
lnglPairsLen = Len(spChrPairs)
If lnglPairsLen > 0 Then
  If lnglPairsLen Mod 2 <> 0 Then
    fncStripChrsEx = ""
    Exit Function
  End If
End If

' End chr position.
ilLen = Len(spStringToStrip)

' Split the chrs to strip ONCE so we aren't
'  doing mid$ all the time.
slChrsToStrp = Split(spChrsToStrp)
lnglUBChrsToStrip = UBound(slChrsToStrp, 1)

' Step through the string.
slNewString = ""
lnglCurrentChrToCheck = 0
Do
  lnglCurrentChrToCheck = lnglCurrentChrToCheck + 1
  If lnglCurrentChrToCheck > ilLen Then
    Exit Do
  End If
  
  ' Get Current chr from the string to strip.
  slChrToCheck = Mid$(spStringToStrip, lnglCurrentChrToCheck, 1)
  
  ' Check against chrs to strip.
  lnglCurrentChrToStrip = -1
  Do
  
    lnglCurrentChrToStrip = lnglCurrentChrToStrip + 1
    If lnglCurrentChrToStrip > lnglUBChrsToStrip Then
      Exit Do
    End If
  
    slCurrentChrToStrip = Mid$(spChrPairs, lnglCurrentChrToStrip, 1)
    
    ' Is this a start chr for a chr pair to exclude?
    lnglStartPairChr = InStr(spChrPairs, slCurrentChrToStrip)
    If lnglStartPairChr > 0 Then
      
      ' Set the end chr.
      slEndPairChr = Mid$(spChrPairs, lnglStartPairChr + 1, 1)
      
      ' Increment chr counter until we get to the end chr for this pair.
      ' NO ERROR checking here. The assumption is you've set everything up
      '  correctly.
      Do
                
        lnglCurrentChrToCheck = lnglCurrentChrToCheck + 1
        slChrToCheck = Mid$(spStringToStrip, lnglCurrentChrToCheck, 1)
        
        If slChrToCheck = slEndChrPair Then
          
          ' Set up the next chr.
          lnglCurrentChrToCheck = lnglCurrentChrToCheck + 1
          slChrToCheck = Mid$(spStringToStrip, lnglCurrentChrToCheck, 1)
          
          ' And get out.
          Exit Do
                          
        End If
      
      Loop
    
    End If
  
    If slChrToCheck = slCurrentChrToStrip Then
      
      'Skip.
    
    Else
    
      ' Add Chr.
      slNewString = slNewString & slChrToCheck
    
    End If
  
    lnglCurrentChrToCheck = lnglCurrentChrToCheck + 1

  Loop
  
Loop Until ilN >= ilLen

fncStripChrsEx = Trim$(slNewString)
' ***********************************************************************
End Function

[YasserKhalil]

Thank you very much. How to use the UDF? Can you give example? I got the first and the third parameter but what about the second one?

[LisaGreen]

Hello Yasser,

You have tto supply the characters to strip.
You can do this by stringing them together as char$(#) for the parameter.

For example, if you want to strip chr(254) use that as a parameter. "Split" with no delimiters will split on space, so as set up, the chrs should be separated by a space.
If you wanted to remove say chr(10) as well then the parameter becomes
chr$(254) & " " & chr$(10)

HTH
Lisa

[LisaGreen]

Yasser,

Which font are you using please?

Lisa

[LisaGreen]

Just some procedures I dug out from old code.

It may be interesting for you Yasser.

Which Font are you using please??????

Regards
Lisa

[YasserKhalil]

What is the relation between the issue and the font ?? Generally the font used in A1 is "Arial" font
Can you post a sample file with how to use the UDF that you provided as I feel lost completely?

[LisaGreen]

I was expecting a font that was in the language you're using.

If we know the font we can take a look at it and see which chrs are being used. That would give us a list of chrs that can be displayed and all the others would be your "invisible" characters. Though never having written in a different language I could be wrong. This is on the excel worksheet you are typing into.

I'll get a worksheet together for you to show the use.

Lisa

[Doc.AElstein]

Hi Yasser.
Here is an idea to get us further here in this Thread. It is just an idea. That’s all
There are 2 routines below, and also the routines are in the uploaded File.
Do not do this quickly.
Do not rush.

You can try this:
_1)
Run code Sub YassersChrs() Just once

_2)
Run code Sub ArabicStuff() many times, using different text in cell A2 of worksheet “Sheet1”.
Run Sub ArabicStuff().JPG https://imgur.com/NYRlA4a" onclick="window.open(this.href);return false;

Run Sub ArabicStuff().JPG

Do this sometimes when you have spare time in the next few days.
Take a few days to do this. Do not rush
Put any Arabic text in cell A2 in worksheet “Sheet1”
Run code Sub ArabicStuff()
Delete text in cell A2 in worksheet “Sheet1”
Put some more Arabic text or English text, or any text in cell A2 in worksheet “Sheet1”.
Run code Sub ArabicStuff()
Delete text in cell A2 in worksheet “Sheet1”
Put some more Arabic text or English text, or any text in cell A2 in worksheet “Sheet1”.
Run code Sub ArabicStuff()
Delete text in cell A2 in worksheet “Sheet1”
Put some more Arabic text or English text, or any text in cell A2 in worksheet “Sheet1”.
Run code Sub ArabicStuff()
Delete text……..etc..
Etc….. etc……

Use any text or numbers. Try to use all text or numbers that you might use in your Arabic Excel

In a few days, you can give us this file back. We may then have a better understanding. We may then have new ideas on what to do. Give us the file back, but not quickly. Give us this file back in a few days after you have run Sub ArabicStuff() many times: Run Sub ArabicStuff() each time with different text in cell A2 in worksheet “Sheet1”.

_._________________________________________________

What is the point of this:
I do not know yet, what this will tell us….. We may be able to see all allowed characters and also we may be able to determine which are your “Hidden” characters……
We may learn together from this…………….. ………………………….. قد نتعلم من هذا. سيسعد الله

do you understand me now?.............................................................................. هل تفهمني الآن؟
this will help a lot ……………………………………………………………………. وهذا سوف يساعد كثيرا
For better understanding. Between us for better understanding between us…………….. :-
…………………………………………………… :-) …… من أجل فهم أفضل. لفهم أفضل بيننا. سيسعد الله……:-)

Alan

File: “ArabicChrs.xlsm” : https://app.box.com/s/qu359r6uc51h43er29zj3f5v14xxjo7a" onclick="window.open(this.href);return false;

Code: Select all

Option Explicit
Sub YassersChrs() ' To determine what Yassers Arabic Excel does with Chr( ) function ' https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/chr-function
Dim WsTb1 As Worksheet: Set WsTb1 = Worksheets("AlansTests")
 WsTb1.Range("C2:C2589").ClearContents
Dim cnt As Long
    For cnt = 1 To 2588
     On Error Resume Next
     Let WsTb1.Range("C" & cnt + 1 & "").Value = Chr(cnt)
        If Err.Number <> 0 Then Let WsTb1.Range("C" & cnt + 1 & "").Value = "Herrad"
     On Error GoTo 0
    Next cnt
Dim WsTb2 As Worksheet: Set WsTb2 = Worksheets("YassersTests")
 WsTb2.Range("C2:C2589").ClearContents
'Dim cnt As Long
    For cnt = 1 To 2588
     On Error Resume Next
     Let WsTb2.Range("C" & cnt + 1 & "").Value = Chr(cnt)
        If Err.Number <> 0 Then Let WsTb2.Range("C" & cnt + 1 & "").Value = "Herrad"
     On Error GoTo 0
    Next cnt
End Sub
Sub ArabicStuff() ' To determine what Yassers Arabic Excel does with Asc( ) function ' https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/asc-function
Dim WsTb2 As Worksheet: Set WsTb2 = Worksheets("YassersTests")
Dim Lenf As Long ' The total length of all characters in cell A2
 Let Lenf = Len(Worksheets("Sheet1").Range("A2").Value)
Dim cnt As Long
    For cnt = 1 To Lenf ' Go through all characters, visible and not visible
'    Dim Mid_Cnt_1 As String ' A variable for each character
'     Let Mid_Cnt_1 = Mid(Worksheets("Sheet1").Range("A2").Value, cnt, 1)
' Get the ascii code number for the character value  '  https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/asc-function
    Dim AssSki As Long: Let AssSki = Asc(Mid(Worksheets("Sheet1").Range("A2").Value, cnt, 1)) '

' In each row we color the cell interior color as yellow to indicate we added there, so we need to get a number for the next unused cell in this row
    Dim NxtWite As Long: Let NxtWite = 7 ' I ( Alan ) have already used up to and including column 6
        Do While WsTb2.Cells.Item(AssSki + 1, NxtWite).Interior.Color = vbYellow
         Let NxtWite = NxtWite + 1 ' we keep increasing while we still have yellow cells
        Loop

'The cell is colored yellow to indicate we were there, and an attempt is made to paste out the value of the character: It may or may not be seeeable
     Let WsTb2.Cells.Item(AssSki + 1, NxtWite).Interior.Color = vbYellow
     Let WsTb2.Cells.Item(AssSki + 1, NxtWite).Value = Mid(Worksheets("Sheet1").Range("A2").Value, cnt, 1)
    Next cnt
End Sub