Showing Formula with values in other language

[SamT]

Ramecid,

Welcome to VBA Express, I hope we can fulfill all your requests.

Please note that all post times are shown in your local time zone, so I can only guess that you mean Mike's post #8. See top right of each post for Post #.

I haven't analyzed the code, but I think you want to use the code from Post # 7.

If that doesn't work, let us know what error you get and what line it is on.

You can find the exact line the error occurs on by placing the cursor in sub "Workbook_SheetCalculate" and pressing F8 until you get the Error.

[mikerickson]

If nessesary, I'll do my best to try to re-remember what I was doing.

[Glissege]

I tried to copy the two files from post #8 but i get an error message.

In post #5 it said:

"Then, when put in a cell, =SUBVALS(A1) will return the formula in A1 with cell references replaced by values.
(note: SUBVALS will error if called from a VB routine)"

What did i do wrong? I'm using Excel 2010 and i really wish i could add this in my Excel files.

[SamT]

f that doesn't work, let us know what error you get and what line it is on.

You can find the exact line the error occurs on by placing the cursor in sub "Workbook_SheetCalculate" and pressing F8 until you get the Error.

[Glissege]

f that doesn't work, let us know what error you get and what line it is on.

You can find the exact line the error occurs on by placing the cursor in sub "Workbook_SheetCalculate" and pressing F8 until you get the Error.

I added the first tekst in a module, the second tekst i added in the workbook.
Cell A1 = B1 + C1
With B1 = 2 & C1 = 3

What do i need to write in the excel file?
I tried =SUBVALS(A1) and i get a "#Value" error

[mikerickson]

To use this as a worksheet formula, one needs to include the code from both post 4 and 5.

Look at Sheet1! column E in the attached.

[Glissege]

Is it a problem if my Excel is in Dutch? I use windows 7; Excel in dutch. When i open the file i see the values. When i sav eit on my desktop i get an error =Naam# (=Name#)

[mikerickson]

I'd need to check through the code. The only thing that I can think of is that the UDF parses formulas. What is your divider?
Do you use =MAX(3 , 4) or =MAX(3 ; 4) ?

[Glissege]

I'd need to check through the code. The only thing that I can think of is that the UDF parses formulas. What is your divider?
Do you use =MAX(3 , 4) or =MAX(3 ; 4) ?

We use = MAX(3;4)

[snb]

@Glissege

Plaats je vraag in Helpmij.nl

[mikerickson]

We use = MAX(3;4)

I'll need to look through the code later, when I wrote it, I assumed American conventions.

[Glissege]

I'll need to look through the code later, when I wrote it, I assumed American conventions.

If i set my language and settings to US it will work. Unfortuanly the people i sent this document too only have the dutch excel.

I have a second question, when i input some numbers and press enter, the selection goes straight to the subval. What is the reason for this and can we change it?

[mikerickson]

If i set my language and settings to US it will work. Unfortuanly the people i sent this document too only have the dutch excel.

I have a second question, when i input some numbers and press enter, the selection goes straight to the subval. What is the reason for this and can we change it?

As written, the code uses .NavigateArrows and ActiveCell to determine the precedents of a cell. Since then, I've learned that the ActiveCell is not needed, so that could be re-written and (hopefuly) the second issue will be addressed.

[mikerickson]

The core of the UDF is the function ValuesInsteadOfPrecenents, which, to my testing, works fine. However, it uses methods that are not available to UDFs that are called by a worksheet formula. i.e. running this code will get the expected result

Sub test()
    MsgBox ValuesInsteadOfPrecedents(Range("C1"))
End Sub

But putting =ValuesInsteadOfPrecedents(C1) in a cell will get #VALUE.

SUBVALS was a workaround for this that involves the Calculate event and Validation.

Since it was first written, Validation has changed. It used to be that one could access the .InputMessage and .ErrorMessage of any cell's .Validation object, even if there was no validation set. That is no longer the case.

[Glissege]

Currently i use the english language and then change the english words to the dutch words. Do you have an idea how i can change it so it works on toher langagues too in europe?

Thanks for your time and effort!

[GoChucky]

Sorry for bumping an old topic like this.
I'm having trouble using this.
I got the same problem as Glissege. I tried using it on an English version of Excel and it works fine, but I need it on multiple dutch excel versions..

I looked into the code a bit and I found that deleting the following code and executing the whole code using F8 works, but once I change any cell those functions will become blank again.

If Me.Cells.SpecialCells(xlCellTypeAllValidation) Is Nothing Then Exit Sub

Does someone have any idea?
I would really appreciate any help!

[SAM2]

Hi could somebody help on this. I have followed the procedure mentioned under #7 but it has not worked. I wonder whether somebody could look at and comment on the attached file. Thanks