# Excel 2011 Buggs



## mikerickson (Nov 6, 2010)

Well, I found one.

Format(-12,"0.00") returns vbNullString rather than "-12.00"

Using Application.Text is a work-around fix.

Edit: Huh? It just fixed itself.


----------



## Norie (Nov 6, 2010)

Do you really mean Excel 2011?


----------



## DonkeyOte (Nov 6, 2010)

2011 is the latest Mac release.


----------



## Norie (Nov 6, 2010)

I was just wondering, sometimes it can be a little confusing with all the versions kicking about.

I think I almost downloaded a trial of Excel 11.

Wasn't until I saw the name of the file that I twigged it was meant for the Mac.


----------



## mikerickson (Nov 6, 2010)

Its looking better than 2004. The VBA has been upgraded to v.6 (Join, Split, Replace). 
It looks like the online Help system is still being worked on. Excel Help is OK, but VBA help is non-existant.

No ActiveX controls for the sheet.
But Userforms seem stabler than (my copy of) Excel 2004.

Oddly enough Userforms.Add("Userform1") fails, so I'm guessing that there is no creation of userforms at run time.


----------



## pgc01 (Nov 9, 2010)

Hi Mike

This means that you can finally return typed arrays from functions in the mac, right?

I never liked to use variants if I can use typed variables. I had, however, problems with mac users in the past and so I was usually careful not to post functions that return typed arrays to be compatible with the mac, but I must say that it bothers me.


----------



## mikerickson (Nov 9, 2010)

I haven't dug into passing typed arrays rather than variants. It on my list.
What would be the syntax for a routine like this. 
If I try delcaring the function as an array, I get illegal ReDim errors or an error on the reverseArray = Result line.


```
Sub test()
    Dim testArray() As Long
    ReDim testArray(1 To 3)
    testArray(1) = 11
    testArray(2) = 12
    testArray(3) = 13
    
    MsgBox Join(reverseArray(testArray))
    
    'this gives a type mismatch error:array or user-defined type expected 
    'MsgBox Join(reverseArray(Array(21, 22, 23, 24, 25)))
End Sub

Function reverseArray(aArray() As Long) As Variant
    Dim Low As Long, High As Long, i As Long
    Dim Result As Variant
    Low = LBound(aArray): High = UBound(aArray)
    ReDim Result(Low To High)
    
    For i = Low To High
        Result(i) = aArray(High - i + Low)
    Next i
    reverseArray = Result
End Function
```
I guess I'm not sharing your dislike of variant arrays.


----------



## shg (Nov 9, 2010)

You can't coerce a variant to a numeric array as the second invocation attempts.

A variant parameter is a 'universal recipient' of arguments, which is very convenient for procedures like sorting.


----------



## repairman615 (Nov 9, 2010)

On topic and off the subject, vise versa...Does Mac support Active X?  I read somewhere breifly that it does not, although I am not sure if the 2011 does, or for arguments sake, Excel for Mac ever did or did not.

Please enlighten me as I have zero experience with Mac.


----------



## mikerickson (Nov 9, 2010)

ActiveX controls that can be put on a worksheet are not supported by Excel 2011, nor have they ever been supported for Mac.
However


----------



## pgc01 (Nov 11, 2010)

Mike

Sorry that I did not answer before but I could not come to the board yesterday.

Using your example this is how I usually like to do it:


```
Sub test()
    Dim testArray() As String
 
    ReDim testArray(1 To 3)
    testArray(1) = "a"
    testArray(2) = "b"
    testArray(3) = "c"
 
    MsgBox Join(reverseArray(testArray))
End Sub
 
Function reverseArray(aArray() As String) As String()
    Dim Low As Long, High As Long, i As Long
    Dim Result() As String
 
    Low = LBound(aArray): High = UBound(aArray)
    ReDim Result(Low To High)
 
    For i = Low To High
        Result(i) = aArray(High - i + Low)
    Next i
    reverseArray = Result
End Function
```
 
Although this has worked in Windows since excel 2000, it would not work in the Mac, because it still had vba5.

I'm curious to know if this finally works in excel 2011. If you find the time to test it please post the result.

Remark: I changed the type in the arrays to String to use the Join(), that expects an array of substrings, but the function could return any typed array.



mikerickson said:


> I guess I'm not sharing your dislike of variant arrays.


 
Well, I recognise that I'm a fan(atic) of strong typing. 

I know vba needs variants sometimes but, being given the choice, I prefer to be specific about the type.


----------



## mikerickson (Nov 11, 2010)

pgc01 said:


> ...I'm curious to know if this finally works in excel 2011. If you find the time to test it please post the result.
> ...


Yes, the code you posted works in Excel 2011.
It also works if 
	
	
	
	
	
	



```
Redim reverseArray(1 to 4)
```
 is added at the end.

This is good, in some situations, I prefer using String arrays so a ReDim will fill with a null stiring null rather than a 0 null.


----------



## repairman615 (Nov 11, 2010)

mikerickson, 
Thanks for confirming that, and for your other helpful posts.


----------



## pgc01 (Nov 12, 2010)

mikerickson said:


> Yes, the code you posted works in Excel 2011.
> 
> ... I prefer using String arrays so a ReDim will fill with a null stiring null rather than a 0 null.


 
Great!

Good point. That's another advantaged of returning a typed array, the elements of the array are initialised with the corresponding initial value for the type.


----------

