excel VBA formula throws #name error

2
votes

My sub createTrip() creates a new worksheet and a table within it.

It works basically fine, but I use this one function for all the data range cells. The function calculates distances between coordinates in the top rows and the left rows. The function itself has no mistakes:

=ARCCOS(SIN(E$3*PI()/180)*SIN($C5*PI()/180)+COS(E$3*PI()/180)*COS($C5*PI()/180)*COS(($D5*PI()/180)-(E$4*PI()/180)))*6367.4445

(I also tried to add the Worksheets name to it like =ARCCOS(SIN(Processing!E$3*PI()/1... - without any success.)

The problem is, when my sub is done, there will be a #NAME-error in all the data range cells. The error will be removed by clicking into the cell, clicking into the formula-bar and clicking back onto the worksheet - without changing anything. I tried to add some recalculation lines to my sub, but that does not help:

ws.EnableCalculation = False
ws.EnableCalculation = True

What else can I do?

The formula is applied to the cells with VBA like this:

Dim Pro As Worksheet
Set Pro = ThisWorkbook.Sheets("Processing")

   // start for loop (ii)

        // if maximum ii is reached:

        With Pro
            Set distanceRange = .Range(.Cells(5, 5), .Cells(ii + 6, ii + 6))
        End With

        distanceRange.Select
        distanceRange.Formula = "=ARCCOS(SIN(E$3*PI()/180)*SIN($C5*PI()/180)+COS(E$3*PI()/180)*COS($C5*PI()/180)*COS(($D5*PI()/180)-(E$4*PI()/180)))*6367.4445"

Thanks for helping here!

2
vbaexcel
Which version you are using? I am using 2013 and the function ARCCOS is not available. #NAME? errors only occurs when you input a function which is either misspelled of not available with the version you are using. – Subodh Tiwari sktneer
well, thanks. it's supposed to be a... "dot". like 1.5 (one and a half) - it only works with a dot. thanks for checking, but i already sorted that out before. the #Name error remains. – maxischl
Should ARCCOS be ACOS? – user4039065
I am using Excel 2016 - no idea about the VBA version. I just tried ACOS instead of ARCCOS, but it does not work, still. – maxischl
I'm on xl2016 as well and there is no ARCCOS function. Just ACOS and ACOSH. – user4039065

2 Answers

1
votes

Use ACOS not ARCCOS

=ACOS(SIN(E$3*PI()/180)*SIN($C5*PI()/180)+COS(E$3*PI()/180)*COS($C5*PI()/180)*COS(($D5*PI()/180)-(E$4*PI()/180)))*6367.4445

0
votes

The problem here is working with the exact same formula of Excel if Excel language is set to a different language from English.

In VBA we must write the formulas in English. (Spanish example):

Application.Range("'Hoja1'!T3").Formula = "=MODA(O8:O" & row_set & ")" Throw #Name error

Changing from "MODA" function in Spanish to "MODE" function in English

Application.Range("'Hoja1'!T3").Formula = "=MODE(O8:O" & row_set & ")" Work as expected

Try this tool for function translate