Allow (Excel) language dependent XML files

[Mo-Gul]

It would be nice if there would be a possibility to provide IntelliSense texts that correspond to the current Excel language, e.g. English or German.

[konne]

ExcelDNA already supports multi language resx files, so for me the best way would be to reference in the Attributes to an resxkey

[govert]

For an Excel-DNA add-in, using the Registration extension library should already allow this to be built easily as a transformation of the attributes during the registration.
For a VBA add-in it might be possible by rewriting the xml file at runtime to contain the right descriptions for the active language, and then calling the hidden IntelliSense 'refresh' macro.

@Mo-Gul I think you have a VBA add-in - is that right? Would re-writing the intellisense xml file at runtime be a way to make this work for you?

[Mo-Gul]

@govert, you are absolutely right. I have a VBA-AddIn. Unfortunately I don't have a clue about Excel-DNA, C#, VB.NET, or F# programming. So as long as the your suggested solution works with only knowing VBA stuff and some instructions on how to make stuff work, I'll live with any solution you provide.

And because I am unable to help directly in programming a solution the only thing I can offer is testing and prove-reading manuals.

[Mo-Gul]

For a pure Excel solution, here a suggestion:

When I import the XML file

XML file

<IntelliSense xmlns="http://schemas.excel-dna.net/intellisense/1.0">
	<!-- modPolynomReg -->
	<FunctionInfo>
		<Function 
			Name="Polynom"
			Description="Calculates polynomial expression f(x) = a0 + a1*x + a2*x^2 + ... + an*x^n"
		>
			<Argument 
				Name="Coefficients" 
				Description="Coefficients (a0, a1, a2, ...)" 
			/>
			<Argument 
				Name="x" 
				Description="Independent variable (x)" 
			/>
			<Argument 
				Name="[IgnoreNA]" 
				Description="(Optional) TRUE = interpret #NA's as 0's" 
			/>
		</Function>
		
		<Function 
			Name="PolynomReg"
			Description="Calculates polynomial coefficients (a0,...,an)"
		>
			<Argument 
				Name="x" 
				Description="Array of 'x' values" 
			/>
			<Argument 
				Name="y" 
				Description="Array of 'y' values" 
			/>
			<Argument 
				Name="PolynomialDegree" 
				Description="Polynomial degree" 
			/>
			<Argument 
				Name="[VerticalOutput]" 
				Description="(Optional) TRUE = return coefficients vertically" 
			/>
			<Argument 
				Name="[IgnoreNAs]" 
				Description="(Optional) TRUE = ignore #NA entries" 
			/>
		</Function>
		
		<Function 
			Name="PolynomRegRel"
			Description="Calculates polynomial coefficients (a0,...,an)"
		>
			<Argument 
				Name="x" 
				Description="Array of 'x' values" 
			/>
			<Argument 
				Name="y" 
				Description="Array of 'y' values" 
			/>
			<Argument 
				Name="PolynomialDegree" 
				Description="Polynomial degree" 
			/>
			<Argument 
				Name="[VerticalOutput]" 
				Description="(Optional) TRUE = return coefficients vertically" 
			/>
			<Argument 
				Name="[IgnoreNAs]" 
				Description="(Optional) TRUE = ignore #NA entries" 
			/>
		</Function>
	</FunctionInfo>
</IntelliSense>

I get

Slightly modifying it

• add another first line for each function and add the function name to it
• copy Description to Description2 and leave Name2 field empty (for the first line)
• delete Description column
• rename columns
  • Name2 --> Argument
  • Description2 --> Description
  • href --> Length
• add LEN function calls to Length column and check length of (new) Description column (that allows to check if the description in longer than 255 characters)

results almost in the following screenshot.

Assuming that only the argument description will be language dependent it would be easy to append the Description by _<msoLanguageIDUI> (Application.LanguageSettings.LanguageID(msoLanguageIDUI), e.g. for German it will be 1031. Additional languages could then easily be added after the Length column.

This new layout would have several advantages, if the Intellisense-AddIn could be modified to read it:

• easier to read then the current layout, also when there are multiple lines in the Description column
• it would be easy to print, also it would be easy to hide unwanted columns/languages, and
• most important: it could easily be used to fill the function wizard with the same information applying a sub on the Workbook_Open event to read the data from that table and fill the arguments of Application.MacroOptions.

[AL7Seven]

Hi everybody
I know it is late but may few of you still have the same problem so …
I am trying the way to change the XML file
I have one XML file per language. I copy the new language XML file to the default XML name then reload its content: ThisWorkbook.CustomXMLParts.Add strFileContent.
I did not resolved it but I wrote a little Sub to submit. May it will help me, and all of you, to solve the problem of refreshing the server.
Here is the VBA code:

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

Rep = Application.Run(functionName, "REFRESH") return True but nothing happened.
I let you think about because I do not understand why nothing change.
Worst!!
Even when I quit EXCEL and then run it, it doesn't change.
Thanks for all

[Mo-Gul]

For the reference also here, which was mentioned in #142 (comment): Have a look at https://github.com/AL7Seven/ExcelDna.IntelliSense-and-XML-multi-languages

[AL7Seven]

I just opened "Discussion" in m'y repo so let’s go ...
Let me know about questions, bugs and etc.
ToDo? Why not? 😁