FormulaR1C1 returning nothing when using a variable

[Zem32619]

Hi All.

I have a code that works perfectly fine for users with the US excel and windows setting. I'm stuck why FormulaR1C1 doesn't work as it should when ran in a German setting.
Here is the line of code that works for US setting.

Range("L2").FormulaR1C1 = "=(1-(RC[14])- " & ACoS & " ))*RC[-1]"

But it returns nothing, as in empty when ran in a German setting. I'm thinking if it is the variable thing "ACoS".

Would really appreciate any help. Thank you so much in advance.

 

[StephenCrump]

... and commented out On Error Resume Next.
What I got now is an error "Run-time error '1004': Application-defined or object-defined error", and the highlighted line is below:

There's a lesson here. There is a place to use On Error Resume Next, but not as a blanket provision to avoid errors displaying. Who knows what your code is (or isn't) doing if you suppress error messages.

You should NEVER, NEVER, NEVER used reserved words (the name of existing functions, properties, methods, etc) as names of your variables, procedures, and user defined functions.

Thanks @Joe4 and @Michael M. Sound advice, but I don't think it's the problem here as ACos isn't a VBA function, and I think the OP ruled this out in Post #10.

@Zem32619, what happens if you enter, say, 3000 in the InputBox, so that ACoS is an integer 30?

I suspect that when you enter 30, as you've done previously, the problem is the difference between the English 0.3 and the German 0,3. Try to put the wrong one in a formula and it will throw a Run-time error 1004.

 

[Michael M]

@StephenCrump
I fully agree with you on the Acos....but in this instance it IS being used within a formula, which I believe, will still cause issues....Why take the risk ? As Joe and I have mentioned, eliminate ALL possibilities for errors !!

 

[Joe4]

@StephenCrump
I fully agree with you on the Acos....but in this instance it IS being used within a formula, which I believe, will still cause issues....Why take the risk ? As Joe and I have mentioned, eliminate ALL possibilities for errors !!

And it is a good practice to get into too!

The point about error handling is good too. I never liked "blanket" error handling.
If there is a possibility of a specific error you want to ignore, better to add error handling specific to that particular error code, and return all others.
And in debugging, temporarily comment out all error handling, if necessary, so you can see all errors.

 

[Zem32619]

Thank you all for your insights. Well noted. @Joe4 & @Michael M already changes the variable name to other names as suggested.
@StephenCrump I still got a Run-time error 1004, same line of error. The variable is storing the right value in it but the formula is not calculating. You're right it is the difference of English 0.3 & German 0,3. When I put 0.3 in the formula instead of the variable, it calculates in L2. I'm in stuck how to deal with different separators as it seems it is the problem.

 

[Michael M]

Put ACoS into a cell and use the REPLACE function to change the value
Change this

ACoS = (Application.InputBox(Prompt:="Enter Target ACoS.",  Type:=1) / 100)
 If ACoS = False Then
 Exit Sub
 End If

To this

 Cells(1, 1) = Replace((Application.InputBox(Prompt:="Enter Target ACoS.", Type:=2)), ",", ".") / 100
If ACoS = False Then Exit Sub

 

[StephenCrump]

I'm in stuck how to deal with different separators as it seems it is the problem.

Did you try this:

@Zem32619, what happens if you enter, say, 3000 in the InputBox, so that ACoS is an integer 30?

If the code works when we eliminate the decimal point, this will provide more conclusive evidence that the separator is the problem.

If it is a separator issue, it's not immediately obvious why, given that your code sets:

.DecimalSeparator = "."
.ThousandsSeparator = ","
.UseSystemSeparators = False

and that VBA treats the number as 0.3 and not 0,3. You may need to play around with this, as it's going to be difficult to replicate on our machines/settings. A quick patch may be in order, e.g. as in Michael M's post above.

Or perhaps: Range("L2").FormulaR1C1 = "=(1-(RC[14])-" & Replace(ACoS, ",", ".") & ")*RC[-1]"
(or is it the other way around?)

 

[StephenCrump]

I fully agree with you on the Acos....but in this instance it IS being used within a formula, which I believe, will still cause issues....Why take the risk ? As Joe and I have mentioned, eliminate ALL possibilities for errors !!

Thanks @Michael M, I totally agree, and adopt a conservative approach to variable names myself to minimise the scope for error.

But in this particular case, my comments about AcoS were based on:
- The fact that the Excel formula isn't using the ACOS function. Rather, the code is passing to Excel the value (0.3) of the ACoS variable.
- The OP having already changed the variable name ACoS, without success.

 

[Alex Blakenburg]

@Zem32619, do you by any chance have your Use system separators turned off ?
File > More > Options > Advance.
My testing indicates your original Type1 setting will use the regional setting when assigning to the variable, but when the formula gets entered if the below is not turned on it will validate the formula using the Excel setting on entry and error out.

That is the only way I have been able to make if fail.

Note: You indicated you are entering 30%, the Type2 code method that has / 100 at the end will error out if you enter a %

 

[StephenCrump]

That is the only way I have been able to make if fail.

Thanks Alex

If I change my system number settings to German format (including decimal separator ",") , I can replicate and fix the problem like this:

Sub Test()
    
    Dim d As Double
    d = 30 / 100

    Application.DecimalSeparator = "."  'My system is changed to ","
    Application.UseSystemSeparators = False
    Range("A1").Value = d   'displays as 0.3
    MsgBox Range("A1").Value 'System setting unchanged: 0,3
    MsgBox Range("A1").Text 'Excel display only: 0.3
       
    'This fails
'    Range("L2").FormulaR1C1 = "=(1-(RC[14])-" & d & ")*RC[-1]"
    
    'This works
    Range("L2").FormulaR1C1 = "=(1-(RC[14])-" & Replace(d, ",", ".") & ")*RC[-1]"
        
End Sub

which makes me think that when @Zem32619 said the message box was returning 0.3, he was actually "translating" from 0,3 for our benefit?

I had a play with a couple of other variations, e.g. reverting the system settings to my normal "." decimal separator, and having Excel display using "|". For coding purposes, the behaviour was quite idiosyncratic.

 

[Zem32619]

@Zem32619, do you by any chance have your Use system separators turned off ?
File > More > Options > Advance.
My testing indicates your original Type1 setting will use the regional setting when assigning to the variable, but when the formula gets entered if the below is not turned on it will validate the formula using the Excel setting on entry and error out.

That is the only way I have been able to make if fail.

Note: You indicated you are entering 30%, the Type2 code method that has / 100 at the end will error out if you enter a %

View attachment 57531

Hi Alex,
Yes, the Use system Separator is turned on.
Thank you for your reply.