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

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

Code:

---

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

---

Quote:

---

Originally Posted by snb

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

Quote:

---

Originally Posted by snb

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

Code:

---

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

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

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

Code:

---

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

---

Quote:

---

Originally Posted by snb

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

Code:

---

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

Quote:

---

Originally Posted by snb

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

Code:

---

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

Have a look:

Code:

---

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

---

Quote:

---

Originally Posted by snb

Have a look:

Code:

---

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

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

Code:

---

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

---