Results 1 to 3 of 3

Insert of a function in an Cell with VBA

Threaded View

  1. #1
    Registered User
    Join Date
    05-19-2021
    Location
    Switzerland
    MS-Off Ver
    2016/365
    Posts
    2

    Insert of a function in an Cell with VBA

    Hi all

    I have a bit of Code to prepare and Format a worksheet. Allmost all is working.
    I need to be sure that it is working with Swiss and English language of Excel. The problem is, that the Excel-Functions are different in the languages.
    In my code I want to insert for exaple =ROUND(RC[-1]*(RC[1]/100),2) into my sheet. in German the Function is called RUNDEN.
    So I thought to check the language and then create the function

    In sert to Excel Sheet like this:
    .FormulaR1C1 = "=" & fctReturnFunction(Round) & "(RC[-1]*(RC[1]/100),2)"
    The Code for the VBE-Function:
    Function fctReturnFunction(bytFunction As enuFunctions)
        Dim intLanguage As Integer
        Dim strCode As String
    
        intLanguage = Application.LanguageSettings.LanguageID(msoLanguageIDUI)
        Select Case intLanguage
            Case 1031   'Deutsch
                Select Case bytFunction
                    Case Round
                        strCode = "RUNDEN"
                End Select
             Case 1033
                Se lect Case bytFunction 
                     Case Round
                        strCode = "ROUND"
                End Select 
        End Select
    fctReturnFunction = strCode 
    End Function
    This works fine and it creates the correct Excel-Function. It is working with the English version.

    BUT not with the German Version. It gives the correct Excel-Function
    =RUNDEN(100/H8*F8;2)
    But it shows (at)NAME?. When I go to the Cell with the Excel-Code, Press F2 to edit and Click Enter without changing anything the Function is working normaly and the (at)NAME? is gone. This means the Excel-Function is correct. So I need to enter each Cell with a function, Press Edit and Enter... I do not know how this can happen, especially when the English version is working as it should.

    The calculation is automatic and recalculation does not help

    Do you have any idea?

    Thank you
    Thomas
    Last edited by thomaswolf; 05-19-2021 at 12:21 PM. Reason: Always Error with SQL Injection

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Function to read cell color and insert Letter?
    By AmazingTrans in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2015, 12:39 AM
  2. Insert cell value in Array function VBA
    By barqujo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-20-2013, 05:40 PM
  3. Insert left most cell in function code
    By sachinahj in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2012, 08:36 PM
  4. How do you insert a custom function into an excel cell using vba?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2011, 10:55 AM
  5. how to insert IF function into cell?
    By Shockaido in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-10-2010, 11:12 AM
  6. Replies: 2
    Last Post: 06-09-2006, 03:18 AM
  7. [SOLVED] pethow do i insert a Yes/No function into a cell in Excel?
    By petethomson in forum Excel General
    Replies: 1
    Last Post: 09-14-2005, 12:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1