6

I'm designing a set of related excel file which are related between them. The objective is that the macros which refere to each other document, can work in any given computer/path. For this reason I have used a set of relative path which lets the macros work well.

I have used the follwoing functions:

=+CELDA("nombrearchivo";$A$1)

"nombredearchivo" means "filename" in english.

The problem here is that this function only works when the computer is setup in Spanish, but when the files are transferred to a English set up computer, it translates de function CELDA to CELL, but not the "nombrearchivo".

To solve it I have thought about trying to show in cell the language in which Excel is setup and then write an if function with three main languages which would display the nombrearchivo, filename or the same in any other language.

Is it possible to show in a cell the language in which excel is setup??

The objective is that the macros ccan work at any given computer and path.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Alex O
  • 63
  • 1
  • 4

5 Answers5

5

This will return you a lang code go here to understand what LCID is..

And Here to get the list of all LCID code signification

for exemple : 1036 = French - Standard 

dim lang_code as long
lang_code = Application.LanguageSettings.LanguageID(msoLanguageIDUI)

Application.LanguageSettings Doc Here

TourEiffel
  • 4,034
  • 2
  • 16
  • 45
3

It is a petty that you cannot request the language of the user. It is not only needed in date-format functions, many times we need to know the languange in indirect-functions as well. All indirect parts of formulas (which are used between quotes) is fixed text, while formulas are automatically 'translated' to the users language. For parts in a formula which is between quotes (like a format of a date), excel has no solution. So we cannot request the language, however, from Excel 2016 and above, there is a possibility to get a formula in text. The English name of that function is FORMULATEXT (please find the translated function name of your own language). This means that we can use this function to create our own way to detect a language. If you wnat to create an international sheet, it is a lot of work, but of you only need to support 2 or 3 languages, it can be used.

Place in Cell A1 the formula: =IF(A2>20,A2,A2) This formula doesn't do anything.

Place in Cell A2 the formula: =MID(LEFT(FORMULATEXT(A1),FIND("(",FORMULATEXT(V2))-1),2,20)

Now we know the language. If cell A2="IF", the language is English, if it is "WENN" it is German, if it is "COMME" it is French and if it is "ALS" it is Dutch, etc.

Be aware that if you have users with older versions of excel, this function isn't available and you cannot detect the language (at least not in this way). I added an ISERROR to this. In case of an error (older versions), I only support Dutch (in my case :) )

Nightwatch
  • 31
  • 2
2

This answer is totally extracted from this source:

https://www.mrexcel.com/forum/excel-questions/617870-language-code-french-spanish-spanish.html

So all credits go to original author Leith Ross:

' Written: March 01, 2012
' Author:  Leith Ross
' Summary: Converts a Language Code Identifier (LCID) into the language name.

Private Declare Function GetLocaleInfoA _
    Lib "kernel32.dll" _
        (ByVal Locale As Long, _
         ByVal LCType As Long, _
         ByVal lpLCData As String, _
         ByVal cch As Long) _
    As Long

Function GetLanguageName(ByVal LCID As Long) As String

    Const LOCALE_SENGLANGUAGE As Long = &H1001
    Const LOCALE_SENGCOUNTRY As Long = &H1002

    Dim Buffer As String
    Dim BuffSize As Long
    Dim RetVal As Long

        BuffSize = GetLocaleInfoA(LCID, LOCALE_SENGLANGUAGE, 0, 0)
        Buffer = String(BuffSize, Chr(0))

        RetVal = GetLocaleInfoA(LCID, LOCALE_SENGLANGUAGE, Buffer, BuffSize)

        If RetVal > 0 Then GetLanguageName = Left(Buffer, BuffSize - 1)

End Function

To test it, just type in a cell =GetLanguageName(1034) (decimal value) but it works also with Hex value, like =GetLanguageName("&H40A")

I got this in my Excel:

enter image description here

And to get the decimal number you need to type, you can use something like this:

Function GetLanguage() As String
GetLanguage = GetLanguageName(Application.LanguageSettings.LanguageID(msoLanguageIDUI))
End Function

So typing in a cell GetLanguage() will return the user's language.

enter image description here

Hope you can adapt this to your needs.

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • Thank very much for all your answers. So should i copy all the pieces of code in a new module? I get an error with this first part of the code Private Declare Function GetLocaleInfoA _ Lib "kernel32.dll" _ (ByVal Locale As Long, _ ByVal LCType As Long, _ ByVal lpLCData As String, _ ByVal cch As Long) _ As Long – Alex O Jul 25 '19 at 10:29
  • Yes, all code into same module should work. IF you are getting trouble with the `Private Declare Function GetLocaleInfoA _ Lib "kernel32.dll" _` could be because you are on 64 bit version, so then you should use `Private Declare PtrSafe Function GetLocaleInfoA _ Lib "kernel32.dll" _`. Check **[how to make VBA code compatible for office 2010 - 64 bit version and older office versions](https://stackoverflow.com/questions/4251111/how-to-make-vba-code-compatible-for-office-2010-64-bit-version-and-older-offic)** – Foxfire And Burns And Burns Jul 25 '19 at 10:38
  • Yes, exactly that is what was hapening, I got an error regarding a 64 bit version, will try now what you said. Thank you so much! – Alex O Jul 25 '19 at 10:45
  • Glad to help @AlexO . If this answer worked for you, please, mark it as correct so it can be closed **[What does it mean when an answer is "accepted"?](https://stackoverflow.com/help/accepted-answer)** – Foxfire And Burns And Burns Jul 25 '19 at 11:34
1

Maybe this could work:

  • enter a formula which includes the text part in double quotes;
  • use IF to check whether the result is the expected one; if it is, then the language is A, ELSE the language is not A.

Example: =IF(ISNUMBER(VALUE(TEXT("01/01/1900";"YYYY")));"Local language is English";"Local language is NOT English")

xLoris
  • 11
  • 1
  • what a straightforward solution! for the OP it's `=IFERROR(CELL("nombrearchivo";$A$1);CELL("filename";$A$1))` – robotik Jan 20 '22 at 10:06
0

I believe you are asking for X but your actual problem is Y. See What is the X-Y-Problem. Therfore I write an answer for Y and not for what you have actually asked for:

To get the path of the current workbook in VBA use the Workbook.Path property

ThisWorkbook.Path

Use this in your macros to build your relative path.

For example if ThisWorkbook.Path is something like C:\My\Path\To\Workbook then you can use it like 

ThisWorkbook.Path & "\Subfolder"

to access the subfolder of the path where the current workbook was saved
C:\My\Path\To\Workbook\Subfolder.

Paths in formulas should adjust automatically if you move all the files into anothor folder.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73