asking explanation about vba code

[somnath6309]

Dear Sir/Madam,

Following is the vba code that extracts sheet_names and makes the list in a separate sheet. I am pursuing vba course and know about "with_end_with" & For_next Loop Techniques. But I have not understood the Red underline portions that requires a little bit help of you to be understood. CStr is a vba function but what is its use? Please give me some explanation about the Red Lines as follows:

Set NewSheet = Sheets.Add(Type:=xlWorksheet)

For i = 1 To Sheets.Count

With NewSheet.Cells(i, 1)
.NumberFormat = "@"
.Value = CStr(Sheets(i).Name)
End With

Next i

Regards,
Somnath

 

[Brij Arora]

CStr(Sheets(i).Name)

Cstr is used for converting number to string . here in this case number is converted to string to use as sheet name.

Regards
Brij A

 

[anupamtiwari05]

Hi Somnath,

Set NewSheet = Sheets.Add(Type:=xlWorksheet)

In the above line, NewSheet is an object type variable in which new sheet object is being stored using Sheets.Add as Sheets.Add insert the new sheet in the workbook.

Whenever we declare any variable as an object type variable, we use Set to store the object in that variable.

Hope this will help you to understand the statement.

Thanks & Regards,
Anupam

 

[NARAYANK991]

Hi Somnath ,

To add to what has already been posted by Arora and Anupam , I think usage of the Cstr may not be warranted.

Let us go over the code , line by line ; to do that , I'm copying below the code with the line numbers assigned :

1. Set NewSheet = Sheets.Add(Type:=xlWorksheet)

2. For i = 1 To Sheets.Count

3. With NewSheet.Cells(i, 1)
4. .NumberFormat = "@"
5. .Value = CStr(Sheets(i).Name)
6. End With

7. Next i

Line 1 has already been explained by Anupam ; lines 2 through 7 form a loop , whereby the cells in the newly added sheet are populated with the names of the sheets in the workbook , including the name of the newly added sheet.

Line 4 is formatting the cell for Text.

Line 5 , with or without the CStr function , will put the sheet name into the cell.

We know that even within a worksheet , if we format a cell for text , then anything that we enter in that cell , even a number , is entered as text ; so , if the the cell format is being put to Text , by line 4 , then the usage of the CStr function in line 5 is redundant.

Secondly , if we do not use line 4 , and we do not convert the cell format to Text , then even if we use the CStr function , in line 5 , the entered data will not be Text.

Lastly , all this is material only if the sheet names are numbers ; if the sheet names are text strings such as Sheet1 , Sheet2 or any other customized text string , then neither line 4 nor the CStr function in line 5 are of any use. Except ....

The exception is if your sheet name contains special characters ! You can have a sheet named (2) , but if you try to enter (2) in a worksheet cell , Excel converts it to -2 , unless you have formatted the cell as Text.

I think to summarise , line 4 is very important , but the usage of CStr is not so important.

Narayan