List cells containing references to files, i.e. containing formulas with broken links

[snb]

I have to amend my last suggestion: a oneliner suffices:

Sub M_snb()
   if not evaluate("isref(" & shtName &"!A1)") then thisworkbook.sheets.add.name=shtname
End Sub

[vanhunk]

Why do you consider a worksheet to be an element of Excel instead of a workbook ? For Each anyWS In Application.Worksheets should be For Each anyWS In thisworkbook.Worksheets or For Each anyWS In activeworkbook.Worksheets or For Each anyWS In workbooks(2).Worksheets or For Each anyWS In workbooks("workbookName.xlsx").Worksheets

Thanks snb, I haven't really thought about it. What is the benefit? Regards, vanhunk

[vanhunk]

I have to amend my last suggestion: a oneliner suffices:

Sub M_snb()    if not evaluate("isref(" & shtName &"!A1)") then thisworkbook.sheets.add.name=shtname End Sub

Impressive, Thanks snb I will certainly give it a go. Best Regards, vanhunk

[vanhunk]

Hi snb, Because I run the macro from the Personal Workbook I have adapted your proposed code to the following: [vba]If Not Evaluate("isref(" & shtName & "!A1)") Then BeginBook.Sheets.Add.Name = shtName BeginBook.shtName.Move After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count) 'Moves it to the last "tab" position.[/vba] I ran the code before the change and it added another sheet to the Personal Workbook, called "Sheet4(BrokenLinksList)" in the VBA Project window. How do I get rid of this sheet? Regards, Henk

[snb]

Check the difference between 'ThisWorkbook' and 'Activeworkbook' in the VBEditor's helpfiles.
Use:

Sub M_snb() 
    If Not evaluate("isref(" & shtName &"!A1)") Then ActiveWorkbook.sheets.add.name=shtname
 End Sub

[vanhunk]

Check the difference between 'ThisWorkbook' and 'Activeworkbook' in the VBEditor's helpfiles.
Use:

Sub M_snb() 
    If Not evaluate("isref(" & shtName &"!A1)") Then ActiveWorkbook.sheets.add.name=shtname
 End Sub

Thanks,
ThisWorkBook object refers to the workbook that the code is contained in. ActiveWorkBook object refers to the workbook that is currently active.

What I would like to know is whether there is a performance difference between referring to it as an excel element instead of a workbook element.

vanhunk

[vanhunk]

I have to amend my last suggestion: a oneliner suffices:

Sub M_snb()    if not evaluate("isref(" & shtName &"!A1)") then thisworkbook.sheets.add.name=shtname End Sub

Hi snb, Could you please explain the use of & and the placement of the quotation marks? Regards, vanhunk

[snb]

Have a look:

Sub M_snb() 
    msgbox "isref(" & shtName &"!A1)"
    If Not evaluate("isref(" & shtName &"!A1)") Then ActiveWorkbook.sheets.add.name=shtname 
End Sub

[vanhunk]

Have a look:

Sub M_snb()      msgbox "isref(" & shtName &"!A1)"     If Not evaluate("isref(" & shtName &"!A1)") Then ActiveWorkbook.sheets.add.name=shtname  End Sub

I understood what it does, and what the result would look like. What I want to know or understand is how would I have known where to put the &s and "s and why there? Regards

[snb]

If you need to concatenate a string & a non-string (number, variable, date, Range value, object property) you need to use the concatenation operator &

msgbox "abc " & 5
msgbox "abc" & variables
msgbox "abc" & range("A5").value
msgbox "abc " & Date
msgbox "abc " & Now
msgbox "abc " & activeworkbook.name