2

I'm trying to test if a cell in a closed external workbook is N/A. In the code below, cell "G5" in the referenced workbook is definitely N/A, but when referencing it using the IsNA function below it returns "Good to go!" when the intention is for it to return "Hay!" in the message box.

Sub TestTest()

    'Declaring variables [BD]
    Dim sFilePath As String
    Dim sFileName As String
    Dim sSourceSheet As String
    Dim sSourceCell As String

    sFileName = "0306-0312 Margin Master.xlsx"
    sFilePath = "\\store\GroupDrives\Pricing\_Deli_\Deli Fresh Shift\Margin Master\"
    sSourceSheet = "Bakery"
    sSourceCell = "G5"

    If Application.WorksheetFunction.IsNA("'" & sFilePath & "[" & sFileName & "]" & sSourceSheet & "'!" & _
    Range("A1").Range(sSourceCell).Address(, , xlR1C1)) Then
        MsgBox "Hay!"
    Else
        MsgBox "Good to go!"
    End If

End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
  • Why use `R1C1` style if the `sSourceCell` is `"G5"`? Try just using `....Address`, and change the `sSourceCell` to `$G$5`. Does it still not work correctly? – BruceWayne May 23 '17 at 20:07
  • Hi BruceWayne, thanks for the comment. I did exactly as you described and it's still returning "Good to Go!" –  May 23 '17 at 20:10
  • (Are you on PC or Mac? Is the FilePath correctly given?) – BruceWayne May 23 '17 at 20:11
  • Hi Bruce. PC - I was reading data from this closed file earlier with the same path, so the file path is correct. –  May 23 '17 at 20:12
  • Oh! You have `...Range("A1").Range("G5").Address...`, try just `...Range(sSourceCell).Address` without the first `Range("A1")` – BruceWayne May 23 '17 at 20:14
  • Do you think it has anything to do with the Range("A1") portion of the statement? I'm still a bit unsure what that refers to and the tutorial did not describe it's function. –  May 23 '17 at 20:14
  • Nope, still not working without the "A1" :( –  May 23 '17 at 20:15
  • #N/A is the "actual value" in the cell and is not being produced by a code error, perhaps this is the cause? –  May 23 '17 at 20:16
  • No, I tried it with an #N/A produced by a formula error, still not it. –  May 23 '17 at 20:18
  • `IsNA` only returns `True` when the cell contains an `#N/A` error value. If the cell contains the *text* `"#N/A"`, you're shooting yourself in the foot. Also `Range("A1")` is (implicitly) referring to the active sheet, which may or may not be what you intend. – Mathieu Guindon May 23 '17 at 20:18
  • Hi Matt, I just tried the same code on a cell returning the actual error and it still is not working. –  May 23 '17 at 20:18
  • 3
    Still, `"#N/A"` as text value is a VERY BAD IDEA. – Mathieu Guindon May 23 '17 at 20:19
  • Agreed, I will error correct for that after finding a solution here. I'm not the one creating the workbook itself. –  May 23 '17 at 20:21
  • So, have you tried qualifying that `Range("A1")` call with an unambiguous `Worksheet` object? – Mathieu Guindon May 23 '17 at 20:23
  • Do you *have* to keep the workbook closed? – BruceWayne May 23 '17 at 20:24
  • Hi Matt, I thought the file reference was as unambiguous as I needed to be. This same reference worked earlier to pull data from the file without declaring an object. But it was being used with ExecuteExel4Macro, and I'm unsure if that would create the object you're speaking of. –  May 23 '17 at 20:25
  • Hi Bruce, with the number of workbooks that may need to be opened it's preferable to keep them closed. –  May 23 '17 at 20:25
  • 1
    [Unqualified Range and Cells cause all kinds of errors all the time](https://stackoverflow.com/q/17733541/1188513). And no, "Range" used all by itself isn't unambiguous, especially in code that opens and closes workbooks and `.Select`s and `.Activate`s stuff. Always qualify `Range` calls with an explicit `Worksheet` object. – Mathieu Guindon May 23 '17 at 20:27
  • Thanks for the link Matt, that was helpful. Do you happen to know in a situation like this if using ActiveSheet is appropriate. Does excel interpret the closed workbook as active if the reference to it is there, or do I need to look elsewhere to create an appropriate object to qualify the range? –  May 23 '17 at 20:30
  • A closed workbook cannot be active. And `ActiveSheet.Range` is almost just as bad as just `Range`; it will fail for the exact same reason, only more explicitly says "the active sheet isn't the sheet you think it is"... – Mathieu Guindon May 23 '17 at 20:37
  • Yeah, I tried it and it bombed. So I would need to somehow create an object from a closed workbook? –  May 23 '17 at 20:38
  • You can't do that without opening it; it's the `Workbooks.Open` method that gives you a `Workbook` object. Seems you just need to change how you're building your address string. – Mathieu Guindon May 23 '17 at 20:40
  • The address string is just an address string - it doesn't really matter which worksheet it is created from - "G5" is "G5" no matter which worksheet it is. – YowE3K May 23 '17 at 20:42
  • You may be right Matt, I may be forced to reconsider the approach if this isn't possible - I REALLY didn't want to open the workbooks. –  May 23 '17 at 20:48

2 Answers2

3

Try using ExecuteExcel4Macro:

Sub TestTest()

    'Declaring variables [BD]
    Dim sFilePath As String
    Dim sFileName As String
    Dim sSourceSheet As String
    Dim sSourceCell As String
    dim externalValue As Variant

    sFileName = "0306-0312 Margin Master.xlsx"
    sFilePath = "\\store\GroupDrives\Pricing\_Deli_\Deli Fresh Shift\Margin Master\"
    sSourceSheet = "Bakery"
    sSourceCell = "G5"

    externalValue = ExecuteExcel4Macro("'" & sFilePath & "[" & sFileName & "]" & sSourceSheet & "'!" & _
        Range("A1").Range(sSourceCell).Address(, , xlR1C1)) 
    If Application.IsNa(externalValue) Then
        MsgBox "Hay!"
    ElseIf IsError(externalValue) Then
        MsgBox "May not work"
    Else
        MsgBox "Good to go! (value is '" & externalValue & "')"
    End If

End Sub

Note: Range("A1").Range(sSourceCell).Address(, , xlR1C1) can probably be abbreviated to Range(sSourceCell).Address(, , xlR1C1) if you are just using cell references such as "G5" as the values of sSourceCell.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
  • Wtf - Good to go, Yow! Nice, it works. I'm going to evaluate it a bit. A function I was working with earlier used ExecuteExcel4Macro but I was unfamiliar with it for the most part, but wow, you nailed it. Thank you! –  May 23 '17 at 21:00
  • ...What's `ExecuteExcel4Macro`? Is that built in? I'll have to research that. ...and anyways, pfft, that's too easy. You need to complicate it with some `Evaluates`.... ;) – BruceWayne May 23 '17 at 21:02
  • I'm still unclear about the purpose of `Range("A1").Range("G5")` (why can't that just be `Range("G5")`), and empty optional arguments call for being skipped and *named arguments* being used. Upvoted anyway. – Mathieu Guindon May 23 '17 at 21:06
  • @Mat'sMug I **suspect** that the `Range("A1").Range("G5")` isn't necessary - it certainly shouldn't be in such a simple scenario - but **maybe** there is a situation with a named range where it might be necessary, e.g. requiring `Range("A1").Range("myRangeName")` - and it looks like the code has been originally sourced from something that was trying to cover all eventualities. – YowE3K May 23 '17 at 21:11
  • I'm also curious about that as well, Matt. –  May 23 '17 at 21:13
  • 2
    @Mat'sMug - If `myRangeName` is set to be `Sheet1!A4`, then `Range("A1").Range("myRangeName").Address` gives `$A$4`, while `ActiveCell.Range("myRangeName").Address` (with cell C4 selected) gives `$C$7`. But `Range("myRangeName).Address` gives `$A$4`, so that still doesn't explain why it is needed. (And by "needed", I don't mean needed in the current situation [it isn't needed in this question] - just needed in any esoteric situation that I am yet to think of.) – YowE3K May 23 '17 at 21:19
2

An alternative may be to use Evaluate() with a "regular formula", i.e.

Sub TestTest()

'Declaring variables [BD]
Dim sFilePath As String, sFileName As String, sSourceSheet As String, sSourceCell As String

sFileName = "0306-0312 Margin Master.xlsx"
sFilePath = "\\store\GroupDrives\Pricing\_Deli_\Deli Fresh Shift\Margin Master\"
sSourceSheet = "Bakery"
sSourceCell = "R5C7"

If Application.WorksheetFunction.IsError(Evaluate("=('" & sFilePath & "[" & sFileName & "]" & sSourceSheet & "'!" & sSourceCell & ")")) Then
    MsgBox "Hay!"
Else
    MsgBox "Good to go!"
End If

End Sub

It should work for you if the cell is truly an #N/A error. If it's just a string that's #N/A, you can just tweak that If statement to check evaluate the cell value.

Note: The Cell Reference needs to be R1C1 style, I believe.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • Wait a minute.. Is `Application.WorksheetFunction.IsError` the same as `IsError`? – Mathieu Guindon May 23 '17 at 20:39
  • Thanks for the answer, Bruce. Did you test this? It is returning "Hay!" on cells that do not have N/A for me over here. –  May 23 '17 at 20:41
  • @Mat'sMug - I don't know, do you mean `IsNA()`? I was just trying to keep the code as similar to OP as possible. I found a quick way to do this with `IsNA()`, but it required opening/closing the workbook. – BruceWayne May 23 '17 at 20:41
  • @user4333011 - Yes, I tested. In cell `G5` of the workbook, I put `=#N/A` which worked. Note that it's not the string `="#N/A"`. – BruceWayne May 23 '17 at 20:42
  • I mean does it work when the cell has an actual value in it? It's returning "Hay!" on cells that have actual numbers and data in them. –  May 23 '17 at 20:43
  • No, I meant `VBA.Information.IsError`, from the standard library. – Mathieu Guindon May 23 '17 at 20:43
  • @user - See the edit, I updated the `If` line to `If Application.WorksheetFunction.IsError(Evaluate("=('" & sFilePath & "[" & sFileName & "]" & sSourceSheet & "'!" & sSourceCell & ")")) Then` – BruceWayne May 23 '17 at 20:49
  • Hi Bruce and Matt, see Yow's answer for a solution that works. Thank you both for your time and effort figuring this out, I appreciate it. Is there any way to give people points on slashdot for helping in comments even if they didn't provide an answer? –  May 23 '17 at 21:01
  • @user4333011 - On SO, if you want to note someone helped, you can just click the up arrow that's to the left of a post. It means "This answer is useful", and then (as you found) clicking the actual check mark marks it as the answer. – BruceWayne May 23 '17 at 21:04
  • 1
    @user4333011 did you not take the [tour]? ;-) – Mathieu Guindon May 23 '17 at 21:04
  • Hmm, took the tour again and it seems there isn't a way to up someone's pointage with helpful comments. I don't know if it helps you out at all, Matt, but if you post an approximate answer with one of your comments I'll upvote it. –  May 23 '17 at 21:10
  • 1
    You just got your 365th star on your GitHub project, Matt - thanks again! –  May 23 '17 at 21:16