VBA in MS Office 2016

[jvbeaupre]

Is anyone besides me looking at how VBA works in the 2016 pre-release versions on MS Office Excell?
I'd be interested in comparing notes.
For instance, the file location syntax is different on the 2016 Excell for Mac VBA...which of course causes problems on most of my macros that open and create files.
Are there any other big VBA issues with the new Office betas?

[Aflatoon]

The file location will always be different on a Mac since it uses a different OS - that's not really anything to do with VBA (that's what Application.Pathseparator is for).

[jvbeaupre]

The file location will always be different on a Mac since it uses a different OS - that's not really anything to do with VBA (that's what Application.Pathseparator is for).

The paths referred to are the results of the VBA directory function, DIR, and are expected to show the correct Mac path syntax
These paths to files have a different syntax from the Mac 2011 VBA and look for files in a weird place.
New path & syntax- /Users/jim/Library/Containers/com.microsoft.Excel/Data (attempt to open a file from a macro-causes a bomb because the file isn't there. What is the "container-thing?)
Correct path & syntax- Moe:Macros:!Fastsig 8x:New file.xlsx
Is this a result of having to modify addressing to take cloud access into account or is this an artifact of the beta status?

[Aflatoon]

You haven't indicated how you are using the Dir code. Are you specifying a directory?

[jvbeaupre]

I'm using curdir, which returns the current directory in debug.prints.
But what is strange is that the Windows form of an address is returned on a Mac! ("/" vs ":")

[Aflatoon]

That could well be a bug. It's pretty obvious that the 2016 beta is a long way from ready where VBA is concerned - especially the VBIDE (if you can call it that, at present).

[jvbeaupre]

Here's what I've found out about VBA in Excel 2016:
1. Application.GetSaveAsFilename is hosed, but Application.GetOpenFilename is OK.
These both define long file names (Path to folder & path separator & short name)
2. The current issue I'm puzzling about is file windows are open when I expect them to be closed. That really messes up the execution of Activesheet statements.
3. There is a workaround for Application.GetSaveAsFilename, for Mac Excel 2016 as follows:
sel = Application.PathSeparator
MainPath = MacScript("choose folder as string")
If Val(Application.Version) >= 15 Then
MainPath = Replace(MainPath, ":", "/")
nplen = InStr(MainPath, sel)
MainPath = Mid(MainPath, nplen, Len(MainPath))
End If
nplen = Len(MainPath)
If Mid(MainPath, nplen, nplen) <> sel Then MainPath = MainPath & sel
sName = InputBox(prompt:="New File Name?", Default:="New_file.xlsx")
New_File_Name$ = MainPath & sName
4. There seem to be two different path separators used in the beta Excel:
---The Mac OS separator, ":", which is used in CurDir returns
---The Excel 2016 beta separator, "/" , which is used for paths and filenames by most of the other VBA file ops.

[jvbeaupre]

More VBA Discoveries:
1. Random errors (difficult to reproduce consistently) seem to be associated with the function val. (Why is val uncapitalized in the 2016 excel vba?). About 30% of the time the 1st statement with "val" causes errors in the following subroutine.

Sub Get_2Nums(prmpt, ttle, a, b)
x = InputBox(prompt:=prmpt, Title:=ttle, Default:="0.0, 0.0")
k = InStr(x, ",")
If k = 0 Then
a = 0
b = 0
Else
a = val(Left(x, k - 1))
b = val(Mid(x, k + 1, Len(x)))
End If
End Sub

2. These errors don't seem to be present in modules of small size.

[Aflatoon]

Wouldn't it make more sense to provide this feedback to someone who can actually do something with it?

[snb]

Why do you use that instead of (e.g.):

Sub Get_2Nums(prmpt, ttle, a, b)
    sn = Split(InputBox(prmpt, ttle, "0.0, 0.0"), ",")
    If UBound(sn) > -1 Then
        a = Val(sn(0))
        b = Val(sn(1))
    End If
End Sub