Menu
Right now I'm confused. I was going to create an answer for this question.
By running some tests I noticed that
ActiveSheet.Cells.Replace Chr(1), "", xlPart, , True, , False, False completely clears out the sheet. It does make sense for Chr(42) which is * but why Chr(1)?
Does anyone know the reason? Is it some kind of second/hidden placeholder?
(Couldn't find something, but used probably just the wrong search terms)
473
Step 1: First, mention the Range of cells we are replacing. In this example, Range is from A1 to B15, so the code will be Range (“A1: B15”). Step 2: Now put a dot to see the IntelliSense list. Step 3: Select the Replace method from the IntelliSense list.
The VBA REPLACE function is listed under the text category of VBA functions. When you use it in a VBA code, it replaces a substring from string with a new sub-string. In simple words, you can use REPLACE to replace a part of text with another text and it returns that new text in the result.
The reason can be inferred from the difference between
NUL : No character. It is used for filling in time or filling space on the surface (such as surface of platter) of storage device where there are no data. We’ll use this character when we’ll be doing programming for data wipers (destructive and non-destructive both) to wipeout the unallocated space so that deleted data may not be recovered by any one or by any program.
SOH : This character is used to indicate the start of heading, which may contain address or routing information.
STX : This character is used to indicate the start of text and in this way this is also used to indicate the end of the heading.
Source of Definition
Various comments have already pointed out that these characters (first 32) are called the control characters because these characters perform various printer and display control operations rather than displaying symbols.
In Hadoop, SOH is often used as field separator especially while reading CSV files. Also, upon opening the Excel file (without unzipping as plain xlsx i.e. as binary) in Notepad++ and printing the control characters, one will see a lot of SOH characters. Due to the security features, one won't be able to see the actual data.
chr(0) = SOH is a pointer to the Memory address of all the cells containing TEXT
and
chr(1) = NUL is a pointer to the Memory address of all the cells containing NULL or BLANK CELLS
Let's look at the above statements in purview to the different commands shown in the image below. The TOP-LEFT is the original data range and example shows that how replacement works for SOH, NUL and ASTERISK (*) using the Range.Replace function.
So, why Chr(0) & Chr(1) are able to replace the values in the Excel Cell if they are merely address pointers and how chr(1) is acting differently than chr(42)?
This happens because of the structure of an Excel Worksheet. When you open a brand new Excel Workbook, the Excel Cells are not marked. It's completely visually and in hard and soft memory of the system.
However, As soon a one enters something in Cell B2 then all the BLANK CELLS squared upto B2 will be assigned a pointer with Chr(0) and Cell B2 will be assigned a pointer with Chr(1).
Now, if one deletes the content of cell B2 then along with other cells B2 will be assigned a NUL or Chr(0) pointer to it.
This is also the reason that why merely clearing the contents using Cells.ClearContents won't clear the cells in the memory and Excel workbook bloats when a far distant cell was used but not deleted using Cells.Clear or Ctrl+Delete.
Now, while it is clear that why Excel replaces the contents or clears the worksheet with the given command. It's also, important to understand the difference between asterisk (*) and SOH [or Chr(0)].
Asterisk (*) replaces the contents of cells with minimum length 0.
While Chr(0) identifies the cells with SOH i.e. the cells containing value and then the replace the value at the destination of pointer.
The difference is not in result but how the cells to be replaced are find or identified.
I created a small macro to validate the hypotheses in which I am recording the time it takes to replace half a million cells (A1 to CZ5000) using Chr(1) = SOH and Chr(42) = *
It turns out that using the pointer to address of cells with TEXT i.e. SOH takes less time than ASTERISK (*).
Sub Replace_Timer() Sheet3.Activate Dim startTime, endTime Dim i As Integer Sheet3.Range("A1:CZ5000").Value = "A" For i = 1 To 25 startTime = Timer Sheet3.Cells.Replace Chr(1), "X", xlPart, xlRows, False endTime = Timer Sheet4.Cells(i + 1, 2).Value = endTime - startTime 'Execution time in miliseconds startTime = Timer Sheet3.Cells.Replace Chr(42), "Z", xlPart, xlRows, False endTime = Timer Sheet4.Cells(i + 1, 3).Value = endTime - startTime 'Execution time in miliseconds Next Sheet4.Activate End Sub If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
