0

Is anyone aware of any differences in versions of .NET/VBA/Excel etc that would cause a reference to Range.Cells to compile for most computers but not a client's new Mac ("with the error unknown data member")?

The code is:

Dim validationArr As Range

Set validationArr = Worksheets("sheet1").Range("B3:T3") 'Grab row dedicated to validation

For i = 1 To validationArr.Cells.Count

It thinks validationArr.Cells is unknown.

I'm just confused as to why it would compile and work on most computers including the client's old Mac but not on their new Mac.

I intend to replace it with validationArr.Rows(1).Cells.Count and see if it works but have yet to confirm.

TylerH
  • 20,799
  • 66
  • 75
  • 101
HelloWorld
  • 418
  • 3
  • 15
  • Does `Dim validationArr As Excel.Range` fix it? – GSerg Sep 13 '19 at 16:23
  • 1
    Is this code running in Excel? – Tim Williams Sep 13 '19 at 16:28
  • @TimWilliams yep runs in Excel when a button is clicked – HelloWorld Sep 13 '19 at 16:35
  • @GSerg I can't verify any solutions until Monday as the affected computer belongs to someone else. Thanks for the suggestions though, I'll give it a try on Monday – HelloWorld Sep 13 '19 at 16:35
  • FWIW .NET has no bearing whatsoever on this, and qualifying `Range` with the `Excel` type library, while a very much warmly recommended approach, wouldn't have any effect if your code is hosted in Excel. – Mathieu Guindon Sep 13 '19 at 16:38
  • @MathieuGuindon thanks for the input, I suppose in this case only differences in versions of Excel would cause the discrepancy described then? I will find out what version of Excel the person is using and see if it explains it. – HelloWorld Sep 13 '19 at 16:44
  • Another thing - `Range.Cells` (without any arguments) is completely redundant, it just yields a reference to itself, so `For i = 1 To validationArr.Count` should be exactly equivalent. – Mathieu Guindon Sep 13 '19 at 16:48
  • @MathieuGuindon Unfortunately this needs fixing on a mac but I hear what you're saying and can try validationArr.Count as well :) I took the naming from an online example but will change it. – HelloWorld Sep 13 '19 at 16:50
  • 1
    @MathieuGuindon It is redundant in this case, but it's probably worth noting that [not in general](https://stackoverflow.com/a/51097214/11683) though. – GSerg Sep 13 '19 at 16:51
  • I initially thought there was a reference to the Word library that was higher in the list than the Excel library, but apparently Excel won't let that happen. Another possibility is that there is a custom class named Range but then it wouldn't work on all computers, unless that customer added that class specifically into their copy of the file. On Windows I would put the cursor inside the word `Range` and press Shift+F2, don't know if that would work on a Mac. – GSerg Sep 13 '19 at 16:54
  • @GSerg Exactly. My problem with Shift+F2 is that sometimes (depending on context - works in this case though) it just pops an irritating message saying "unrecognized identifier", whereas RD's context status bar tells you what you've selected and where it came from, regardless of whether the VBE can navigate to its Object Browser entry (assuming early-bound code of course). At this point I'm just as stumped as the OP though (also it's currently unclear whether the OP is working on Windows or a Mac). – Mathieu Guindon Sep 13 '19 at 16:59

1 Answers1

0

Okay so for some reason the replacing it with validationRng.Count worked (I renamed the variable).

I faced similar unexplained compilation errors later on and exporting the module, deleting the module, and re-importing it fixed all compilation problems.

Bizarre.

HelloWorld
  • 418
  • 3
  • 15