VBA UDF for Excel: IntelliSense and Multi countries XML files

[AL7Seven]

Hi,
First of all, sorry if you feel that my English is … "poor". It is the trough.
I write VBA-UDF for Excel and I am trying to set up IntelliSense with XLM files.
So far I am using ExcelDna.IntelliSense64.xll and "IntelliSense" WorkSheet to implement IntelliSense
The goal is to have multiple language XLM fileS per UDF.
I think this implies 2 points:

1. To have one XLM per language
2. To be able to unload the already loaded XML file then load the new necessary language XML file.

Is it possible?
I tested to do this but it does not work. May be I did wrong.
Any solution?
Thank you very much for your help.

[govert]

I don't have a good answer for localisation of the IntelliSense at the moment.
But it is a valid request and worth thinking about as an extension.

What you can do is to rename or copy the xml files as needed.
It would be possible to refresh the server without reloading the workbook, though this is easier from C# than VBA.
From VBA one would have to get the active IntelliSense server Id from the environment variable EXCELDNA_INTELLISENSE_ACTIVE_SERVER, then call a magic hidden function called IntelliSenseServerControl_{activeServerId} with the argument "REFRESH".

[Mo-Gul]

maybe related: issue #82 ...

[AL7Seven]

Thanks a lot Govert.
OK, I'm a dummy. I confess. 🥇
But any of you can give me a link or a guide to know, in VBA using ExcelDna.IntelliSense64.xll in Excel (early binding), how …

1. "to get the active IntelliSense server Id from the environment variable EXCELDNA_INTELLISENSE_ACTIVE_SERVER, then call a magic hidden function called IntelliSenseServerControl{activeServerId} with the argument "REFRESH"_"
   Please !!! Tell me how to do/access that refresh. Any link to get VBA sample code?
   It will fix a lot of things

2. I'm testing a way that's let me think it is more flexible and an easier way:

   • I made several xml file. One per language. Ex: Myfunction_EN.IntelliSense.xml, Myfunction_FR.IntelliSense.xml, Myfunction_GER.IntelliSense.xml etc.
   • In my ThisWorkBook of UDF's MyFunction, in the actual CustomXMLParts, I delete the actual loaded XML.
   • I replace the actual Myfunction.IntelliSense.xml with the new renamed XML file.
     I doesn't work. If it was French and want to swap to English one it is still in french sometimes it works sometimes it doesn't when I unload/reload ExcelDna.IntelliSense64.xll in Excel Add-ins.
     Don't understand why it's just what I notice.
     Any solution, explanation.
     In very simple XML file, I also notice that adding a "Country=xxxx" doesn't 'disturb' Excel DNA.
     May it's also a way to explor.
     Between and ?
     Exp:

<IntelliSense xmlns="http://schemas.excel-dna.net/intellisense/1.0">
  <FunctionInfo>
   <Function Name="AddTwo" Description="Ajoute + 2 au nombre"
              HelpTopic="http://www.google.com" Country="1036" >
      <Argument Name="Number" Description="Le nombre à ajouter" />
    </Function>
  </FunctionInfo>
</IntelliSense>

I hope it will also give you few ideas.
Anyway, thank you very much for responding.

[AL7Seven]

So far I tried to execute the refresh function. It doesn't work.
Just to show you the way I did it:

Declare PtrSafe Function GetEnvironmentVariable Lib "kernel32" Alias "GetEnvironmentVariableA" (ByVal lpName As String, ByVal lpBuffer As String, ByVal nSize As Long) As Long

Sub VariableEnvironnement()
  Dim buffer As String * 255
  Dim thesize As Long
  Dim Rep
  Dim idParts
  Dim functionName
  Dim serverId

  thesize = GetEnvironmentVariable("EXCELDNA_INTELLISENSE_ACTIVE_SERVER", buffer, Len(buffer))
  If thesize > 0 Then
    serverId = Left(buffer, thesize)
    
    ' Extract ID
    idParts = Split(serverId, ",")(1)
    
    If Len(idParts) >= 1 Then
      ' Use unique ID to build the name
      functionName = "IntelliSenseServerControl_" & idParts
  
      ' Appeler la fonction cachée pour rafraîchir IntelliSense avec l'argument "REFRESH"
      On Error Resume Next
      Rep = Application.Run(functionName, "REFRESH")
      On Error GoTo 0
      If Rep Then
          MsgBox "IntelliSense refresh done."
      Else
          MsgBox "IntelliSense refresh not done."
      End If
    End If
  End If
End Sub

I surly did something wrong but I don't find what. Any help?

[govert]

@AL7Seven Your code for calling the refresh looks right - I'll try to test and confirm myself.
You might use the Environ function instead of the Windows API call, but that should not make a difference.

It should be possible to create a file called ExcelDna.IntelliSense64.xll.config next to the .xll file with settings that let you trace the IntelliSense library. Example config files for logging are described here: https://excel-dna.net/docs/archive/wiki/Diagnostic-Logging This should allow you to check that the Refresh call is working.

Reloading the IntelliSense add-in should definitely refresh everything, like restarting Excel.

If you are trying to change the Workbook.CustomParts, maybe that is not working as expected. In the IntelliSense we look for the first CustomPart that has the namespace "http://schemas.excel-dna.net/intellisense/1.0". Adding extra parts, even with this namespace, won't make a difference. The relevant code is here:

IntelliSense/Source/ExcelDna.IntelliSense/Providers/WorkbookIntelliSenseProvider.cs

Line 349 in 65f82cf

void RegisterWithXmlProvider(Workbook wb)

I see no reason why changing the file and then calling refresh should not work.

[AL7Seven]

Hello,
I'm back ;) . Thank you for answering me.
About: "... Environ function instead of the Windows API call …"
I did try with Environ function but it did work/find DNA results. So I use the API with no problem.
Anyway it doesn't change anything.

About the log file, I will test what you wrote me.

1. Refresh hidden function:
   I have a "strange" feeling about your Refresh hidden function.
   It feels like you have a buffer (or in the registry but did not find any) OR it doesn't do the entire "job" meaning it does not delete the old one. It seams that _xmlProvider doesn't force to reload the data with a new instance of CustomXMLParts.
   Something like that. (Sorry I'm not at all fluent in C#. I just can guess what it written and can do)

2. XML files:
   If I first load the French XML file and then want the English one it is always in French.
   BUT … Since this morning I tried something.
   So far ExcelDna host was in a separate Directory. I moved it to the same my UDF's Directory.
   Refresh function still doesn't have any effect.
   EXCEPT THAT NOW when unload ExcelDna from the Add-ins menu and then reload it …
   IT WORKS!
   Also my first conclusion (may be wrong) is that Refresh does not do the job correctly.
   My second conclusion is that, may be, ExcelDna AND the UDF AND the XML file "feel" :)) better when they are all together.

It would be better if XLL code (Refresh) is corrected but I am not able to do.
It's not a good way but …
I'm going on about that and will test if using VBA to unload then reload it will work.
I let you now.
Cheers

[AL7Seven]

Hi,
Sorry I didn't have time to try the ".config".
It's not perfect but for now it works.
SO ... here down the program. I hope it will help you and others.

P.S.: Of course we could concatenate Sub or Function to make the code shorter but I did (quick) that way to make it more clear and (try to be) more educationa.

' 1) First sub to Call
Sub EmbedIntelliSense()
    Dim Language As Integer
    Dim strFilePath As String
    Dim strFilename As String
    Dim strFileContent As String
    Dim LangCode As String
    Dim iFile As Integer
    Dim Rep
    Dim namespaceURI
    Dim functionName
    
    If Application.RegisterXLL("ExcelDna.IntelliSense64.xll") Then
      
      Const constDefaultFile = "C:\Users\Papa\AppData\Roaming\Microsoft\AddIns\AddTwo.IntelliSense.xml"
      
      strFilePath = "C:\Users\Papa\AppData\Roaming\Microsoft\AddIns\"
      
      ' Look for the XML elements with this <Function> name
      functionName = "AddTwo"
      ' Save the NameSpace
      namespaceURI = "http://schemas.excel-dna.net/intellisense/1.0"
			
      ' Delete ALL this function's XML that could be in ThisWorkbook.CustomXMLParts
			' If True => At least 1 has been deleted. Not really usefull
      Rep = DeleteXMLPartByFunctionName(functionName)
      
      ' Detect the Excel language
      Language = Application.LanguageSettings.LanguageID(msoLanguageIDUI)
      
      ' Get the active Excel language (fr ou en)
      Select Case Language
        Case Is = 1036
          LangCode = "FR"
				Case Is = 1031
					LangCode = "EN"
        Case Else
          LangCode = "EN"
      End Select
      
			' Build the file name to get
      strFilename = strFilePath & functionName & "_"
      strFilename = strFilename & LangCode & ".IntelliSense.xml"
      
			' Test the length to know if exists
      If Len(Trim(Dir(strFilename))) > 0 Then
				' Replace the old file
        FileCopy strFilename, constDefaultFile
        iFile = FreeFile
        Open constDefaultFile For Input As #iFile
        ' Load the new XLL content
				strFileContent = Input(LOF(iFile), iFile)
				Close #iFile
        
        ' Add the new XML file in CustomXMLParts
        ThisWorkbook.CustomXMLParts.Add strFileContent
        
				' Useless for now: Call the Refresh ExcelDna function
        Rep = RefreshDNA
        if Rep Then
					' Reload the
					Rep = ReLoadDNA
					if Rep = False Then
						MsgBox "Excel DNA is not loaded" 
					end if
				End if
      End If
    Else
      MsgBox "Excel DNA is not loaded"
    End If
End Sub

' 2) Delete all the function name. Here "AddTwo"
Function DeleteXMLPartByFunctionName(ByVal functionName As String) as boolean
    Dim xmlPart As CustomXMLPart
    Dim xmlDoc As Object
    Dim funcNode As Object
    Dim xmlContent As String
    
    ' Vérifier tous les CustomXMLParts dans le classeur
    For Each xmlPart In ThisWorkbook.CustomXMLParts
        ' Récupérer le contenu XML sous forme de chaîne
        xmlContent = xmlPart.XML
        
        ' Charger le contenu XML dans un objet DOM (MSXML)
        Set xmlDoc = CreateObject("MSXML2.DOMDocument")
        xmlDoc.LoadXML xmlContent
        
        ' Rechercher les éléments <Function> dans le XML avec un nom spécifique
        Set funcNode = xmlDoc.SelectSingleNode("//Function[@Name='" & functionName & "']")
        
        ' Si la fonction avec ce nom est trouvée, on peut la supprimer
        If Not funcNode Is Nothing Then
            ' Si la fonction correspond, effacer le XML
            xmlPart.Delete
'            MsgBox "XML Part with function '" & functionName & "' deleted!"
'            Exit Sub
        End If
    Next xmlPart
    
'    MsgBox "XML Part with function '" & functionName & "' not found!"
End Function

' Load the new XML file
Function RefreshDNA() as boolean
  Dim buffer As String * 255
  Dim thesize As Long
  Dim Rep
  Dim idParts
  Dim functionName
  Dim serverId

	RefreshDNA = False

  thesize = GetEnvironmentVariable("EXCELDNA_INTELLISENSE_ACTIVE_SERVER", buffer, Len(buffer))
  If thesize > 0 Then
    serverId = Left(buffer, thesize)
    
    ' Extract ID
    idParts = Split(serverId, ",")(1)
    
    If Len(idParts) >= 1 Then
      ' Use unique ID to build the name
      functionName = "IntelliSenseServerControl_" & idParts
  
      ' Appeler la fonction cachée pour rafraîchir IntelliSense avec l'argument "REFRESH"
      On Error Resume Next
      Rep = Application.Run(functionName, "REFRESH")
      On Error GoTo 0
      If Rep  Then
				RefreshDNA = True
			Else
         MsgBox "IntelliSense refresh not done."
      End If
    End If
  End If
End Function

Function ReLoadDNA() as boolean
'    Dim xllPath As String
    Dim addIn As addIn
    Dim wasLoaded As Boolean
    
    Const xllPath = "C:\Users\Papa\AppData\Roaming\Microsoft\AddIns\ExcelDna.IntelliSense64.xll" ' Chemin du XLL
		
		ReLoadXLL = False
		
    ' ?? Rechercher le complément dans la liste des compléments Excel
    If Application.AddIns2("Excel-Dna IntelliSense Host").Installed = True Then
      Application.AddIns2("Excel-Dna IntelliSense Host").Installed = False
'      just 1 sec of breathing
      Application.Wait Now + TimeValue("00:00:01")
      Application.AddIns2("Excel-Dna IntelliSense Host").Installed = True
			ReLoadXLL = True
    End If

    ' ?? Reload the XLL if necessary
    'If wasLoaded Then
    '    Application.RegisterXLL xllPath
    'End If
End Function

I let few pieces of code in comments that I think they could be helpful for others.
Let me know if any thing is wrong there … or just comments.
If this is good enough feel free to share.
Al7

[govert]

@Sergey-Vlasov Could you please investigate whether the Refresh for the WorkbookIntelliSenseProvider works correctly when an embedded CustomPart is changed? The idea is that refresh should re-read everything, similar to reloading the add-in.

[govert]

@Sergey-Vlasov Could we support loading of IntelliSense information at runtime by adding a new call to the IntelliSenseServerControl function? I think that would be one way to address the original concern of this issue more directly.

[Sergey-Vlasov]

Refresh for the WorkbookIntelliSenseProvider doesn't reload CustomXMLParts.

Calling "DEACTIVATE" and then "ACTIVATE", instead of "REFRESH", can be used as a workaround.

[AL7Seven]

hello
I just tried with this VBA code:

Function RefreshDNA() As Boolean
  Dim buffer As String * 255
  Dim thesize As Long
  Dim Rep
  Dim idParts
  Dim FunctionName
  Dim serverId

  RefreshDNA = False

  thesize = GetEnvironmentVariable("EXCELDNA_INTELLISENSE_ACTIVE_SERVER", buffer, Len(buffer))
  If thesize > 0 Then
    serverId = Left(buffer, thesize)
    
    ' Extract ID
    idParts = Split(serverId, ",")(1)
    
    If Len(idParts) >= 1 Then
      ' Use unique ID to build the name
      FunctionName = "IntelliSenseServerControl_" & idParts
  
      ' Appeler la fonction cachée pour rafraîchir IntelliSense avec l'argument "REFRESH"
      On Error Resume Next
'      Rep = Application.Run(FunctionName, "REFRESH")
      **Rep = Application.Run(FunctionName, "DEACTIVATE")**
      **Rep = Application.Run(FunctionName, "ACTIVATE")**
      On Error GoTo 0
      If Rep Then
        RefreshDNA = True
      Else
         MsgBox "IntelliSense refresh not done."
      End If
    End If
  End If
End Function

I just can tell that it doesn't work but the functions, Deactivate and Activate, return TRUE.
At the beginning I switch it OFF. After the code it is still OFF.
See the image after the code.

[Sergey-Vlasov]

After the code it is still OFF.

"DEACTIVATE" and then "ACTIVATE" update all IntelliSense info. It doesn't reload the add-in.

[Sergey-Vlasov]

Could we support loading of IntelliSense information at runtime by adding a new call to the IntelliSenseServerControl function?

We have 4 methods of providing IntelliSense information. I think adding another one will make it even more complicated.