Different behaviour on operating systems

[tiredofit]

It is well known that when writing VBA to use on a Mac, things to avoid include ActiveX objects, Dictionaries and APIs.

Today I came across a problem and thankfully I was told where the problem was.

The following code returns different results depending whether it's run on Windows or Mac.

Feel free to test.

Option Explicit

    Dim Counter As Integer
    
Public Sub Start()

    Sheet1.Cells(1, 1).Resize(Counter, 1).Value = Sec()
    
End Sub

Public Function Sec() As Variant
    
    Counter = 3
    
    Dim NewArray() As Variant
    
    ReDim NewArray(1 To Counter, 1 To 1) As Variant
    
    Dim i As Integer
    
    For i = 1 To Counter
    
        NewArray(i, 1) = i
        
    Next i
    
    Sec = NewArray()

End Function

On Windows, it returns the values 1, 2 and 3.

On a Mac, it'll return blank.

 

[Kyle123]

It doesn't return blank on my mac (11.4 Big Sur, Excel 16.54). It returns 1,2,3 as it does on Windows. Have you got a specific environment where this is failing?

As an aside, the code is a bit confusing though and I'd be tempted to refactor ?‍

 

[tiredofit]

It doesn't return blank on my mac (11.4 Big Sur, Excel 16.54). It returns 1,2,3 as it does on Windows. Have you got a specific environment where this is failing?

The code is somewhat confusing through and probably should be refactored ?‍

The "real" code is much longer. I picked out the bit that was relevant and posted it here.

Will check again and update in due course.

Thanks for testing though.

 

[tiredofit]

It is well known that when writing VBA to use on a Mac, things to avoid include ActiveX objects, Dictionaries and APIs.

Today I came across a problem and thankfully I was told where the problem was.

The following code returns different results depending whether it's run on Windows or Mac.

Feel free to test.

Option Explicit

    Dim Counter As Integer
   
Public Sub Start()

    Sheet1.Cells(1, 1).Resize(Counter, 1).Value = Sec()
   
End Sub

Public Function Sec() As Variant
   
    Counter = 3
   
    Dim NewArray() As Variant
   
    ReDim NewArray(1 To Counter, 1 To 1) As Variant
   
    Dim i As Integer
   
    For i = 1 To Counter
   
        NewArray(i, 1) = i
       
    Next i
   
    Sec = NewArray()

End Function

On Windows, it returns the values 1, 2 and 3.

On a Mac, it'll return blank.

Actually, second thoughts, I think it crashed on the mac, so I'm told.

 

[Kyle123]

I think the problem probably lies elsewhere, that said, I think the code would be better written without the module level variable for both mac and Windows, Sec should probably accept it as an argument and then Start defines it.

 

[tiredofit]

I think the problem probably lies elsewhere, that said, I think the code would be better written without the module level variable for both mac and Windows, Sec should probably accept it as an argument and then Start defines it.

Thanks for your suggestion, I'll bear that in mind going forwards.