Excel-DNA
VBA UDF for Excel: IntelliSense and Multi countries XML files
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:
- To have one XLM per language
- 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.
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".
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 …
-
"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
-
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 <FunctionInfo> and <Function Name= …> ? 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.
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?
@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: https://github.com/Excel-DNA/IntelliSense/blob/65f82cf70cc8e490503f87c739f7a2dd5c00343c/Source/ExcelDna.IntelliSense/Providers/WorkbookIntelliSenseProvider.cs#L349
I see no reason why changing the file and then calling refresh should not work.
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.
-
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
_xmlProviderdoesn't force to reload the data with a new instance ofCustomXMLParts. Something like that. (Sorry I'm not at all fluent in C#. I just can guess what it written and can do) -
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.
Refreshfunction 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
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
@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.
@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.
Refresh for the WorkbookIntelliSenseProvider doesn't reload CustomXMLParts.
Calling "DEACTIVATE" and then "ACTIVATE", instead of "REFRESH", can be used as a workaround.
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.
After the code it is still OFF.
"DEACTIVATE" and then "ACTIVATE" update all IntelliSense info. It doesn't reload the add-in.
Could we support loading of IntelliSense information at runtime by adding a new call to the
IntelliSenseServerControlfunction?
We have 4 methods of providing IntelliSense information. I think adding another one will make it even more complicated.
Hi @Sergey-Vlasov , about your answer I could agree … from your point of view. The gaol is: Does the XLL DNA.IntelliSense want/should be able to be multi languages? So far, I would say it is not build to be. Not big dill but I / We would find a way to be able to do that for peoples who need it. So far I know, having 4 or more methods. I do not know all of them so may be I missed some interesting ones. From my very very small point of view I think it is a need. So … actually I'm trying to code a way to do it (if possible) without you need to change your own code. Not so easy but I'm still trying. Please, just be patient and, in the next few days, I will submit (in my GitHub) a possible solution I will announce here if I achieve , at least, a correct way. Anyway thank you to taking care of that problem.
PS; You wrote: "REFRESH" "DEACTIVATE" and then "ACTIVATE" update all IntelliSense info. It doesn't reload the add-in". Just a way about these functions that, if I understand, none of them do load/reload. May be one of them (Refresh?) should do it? Isn't?