VBA API Changes for Excel 64 bit?

[TJMurphy]

Hi all,
Our company has decided to roll out 64bit Excel on us with minimal advance notice (yay us!).

So, I've got a brand new test PC and installed Office 64bit on it. I also installed the TM1 Perspectives 64 bit addin. So far so good, I can connect to the server, run reports and so on. Testing is ongoing but I've just spent a day on something and not gotten anywhere so hoping someone else can point me in the right direction.

We have some Excel models that use the TM1 API in VBA to fire off some TI processes. I can't make these run now under 64bit. I've put "PtrSafe" in all the declarations and they compile fine. It still runs in the 32bit version after this.

When I step through the code it's working fine until it calls TM1SystemServerHandle (hUser, sServer). On this line, Excel just crashes, every time.

There *are* values for hUser and sServer. I believe this means the API is connected / working because API calls were used earlier to get a user handle.

Anyone else come across this? Any suggestions?

[gtonkin]

Not at my PC right now but seem to remember having to not only add the PtrSafes but needed to change the ‘As Long’ to ‘As LongPtr’

HTH

[jrizk]

If you download TM1Tools which was modified for 64bit you should be able to follow the changes required for your models.

https://www.tm1forum.com/viewtopic.php?f=3&t=11593

You have to add PtrSafe/LongPtr to the API declarations as well as LongPtr to the functions and dims that reference the declarations . Even though you are getting values for hUser and sServer they could be error types - which might be because hUser is not being obtained correctly in 64bit and this could be because of the SSL certificates (refer to this post https://www.tm1forum.com/viewtopic.php?f=3&t=14473).

As an example a function to obtain the server handle and runs on 32bit and 64bit would be:

#If Win64 And VBA7 Then
Public Function serverHandle(ByVal s As String) As LongPtr
#Else
Public Function serverHandle(ByVal s As String) As Long
#End If

#If Win64 And VBA7 Then
Dim hPool As LongPtr
Dim hServerName As LongPtr
#Else
Dim hPool As Long
Dim hServerName As Long
#End If

Dim sServerName As String * 75

'Note assumes hUser has been obtained correctly for 32/64bit

If hUser = 0 Then Exit Function

hPool = TM1ValPoolCreate(hUser)

serverHandle = TM1SystemServerHandle(hUser, s)

'= This section is just a check the server handle is valid
hServerName = TM1ObjectPropertyGet(hPool, serverHandle, TM1ObjectName())

If TM1ValType(hUser, hServerName) = TM1ValTypeString() Then
TM1ValStringGet_VB hUser, hServerName, sServerName, 75
Debug.Print sServerName
End If
' =

TM1ValPoolDestroy (hPool)

End Function

Sub getServerHandle()
Call serverHandle("servername")
End Sub

[TJMurphy]

Awesome, thank you. I can't easily get onto this forum from work so get back here less often than I like.

I'm having some other wierd behaviour too - edit a Declare line, even to just add a space and I'm getting Out of Memory. I'm off to try and run a repair on Excel as well as follow through on the updated add-in.

Updated : Not going to pretend I fully understand what I've done but having commented out all declarations and then copying the ones I needed back in from the TM1 Tools APIs codes and then debugging my code I've got it functioning. The TM1 Tools API code is far clearer than the old IBM one I was using so thanks very much for that.