la methode "formulalocal" de l'objet "range" a échoué

[insosama]

Bonjour tout le monde !

encore une fois, c'est un code de m**** qui me pose probleme, une fonction seule, simple, maitrisé en théorie, et avec pourtant rien de bien exceptionnel.

If Left(temp, 1) <> "=" Then temp = "=" & temp
Cells(1, cl).FormulaLocal = temp

erreur d'execution -2147417848(80010108) - la methode "formulalocal" de l'objet "range" a échoué.

donc, temp est un string
cl est un long
cl est égal ici dans mon fichier a 6 (mais il pourrait avoir une autre valeur, évidement)
temp contient une formule, celle ci pointant sur une fonction perso.
elle est parfaitement rédigé, aux normes francaise.

temp = "=Percage(6.31;2)" (c'est ma fonction)

alors la, c'est a devenir chevre, se frapper la tete contre le sol, je sais plus quoi inventer.
j'ai tenté par dépit "6,31" au lieu du ".", cette valeur etant le résultat d'un calcul, donc a priori "directement aux bonnes normes" (-c'est du moins ce que je pensais-)
comme c'est le résultat d'une fonction, ce n'est pas la fonction que j'ai testé, mais seulement la formule telle quel dans une cellule vide.
sans surprise

=percage(6.31;2) => fonctionne
=percage(6,31;2) => erreure.

je n'ai pas cherché, donc, a modifier ma fonction source du "6.31"

j'ai tenté par dépis un "formula", avec des "," au lieux de ";" (chose simple a changer) => meme sanction
j'ai essayé avec un objet "range" au lieux de cells.
encore plus fort, dans ce cas, j'obtiens :

range(Cells(1, cl)).FormulaLocal = temp

nouvelle erreure (faut varrier les plaisir) :
erreur d'exection 1004 : la methode 'range' de l'objet '_global' a échoué

au moins le numéro d'erreur est plus commun que "-2147417848(80010108)"
et peut etre que le soucis est bien le "range". mais en quoi il y a une gaffe dans cette facon de rédiger ?

je comprend plus rien, je craque.

a ce stade du code, il n'y a eue aucun select, et donc l'onglet courant est l'onglet appelant.
la fonction de calcul automatique est coupé le temps que la macro s'execute pour favoriser la rapidité d'execution du code,
la phrase qui plante est donc suivit normalement immédiatement d'un :

Cells(1, cl).Calculate
resultat = cells(1,cl).value
cells(1,cl).clear

évidement, le code n'arrive jusque la.

toute aide sera .... plus que bienvenue !

cdlmt.

edit : ajout des balise "code". sorry pour ceux qui ont vu avant correction.

 

[insosama]

Re : la methode "formulalocal" de l'objet "range" a échoué

je viens d'avoir un éclair de géni.
ou pas.
disons une piste.

en fait, je viens de me souvenir que j'ai eue un cas un peu similaire.
https://www.excel-downloads.com/thr...-pas-sans-message-derreur-je-craque-_.220541/

en quoi cet ancien probleme est similaire ?

en fait, dans les deux cas, j'essayais, depuis une FONCTION, donc appellé par une FORMULE de modifier une autre cellule que celle ou se trouve la formule appellant la fonction.

ca me parrait élémentaire, possible
mais le fait est que j'ai eue beau m'acharner dans le premier cas, alors que la c'etait uniquement le VALUE meme pas un FORMULA que j'essayais de changer, rien a faire, je n'y parvenais pas.

apres deux jours passé sur le probleme, j'ai fini par laisser tomber, et j'ai contourné le soucis en mettant tout les résultat (qui auraient du etre réparti sur plusieurs cellule) dans la seule cellule ou se trouve la formule (et la, ca a fonctionné), et en créant des fonctions pour chacune des cellules de résultat pour "extraire" du paquet de donné celle qui les concernait.


deux questions

1) n'ai je pas l'ombre d'une bonne intuition, est n'est ce pas parce que le pointage de valeur de d'autre cellule au sein d'une fonction est impossible ?
1 bis : est il possible de contourner le soucis, et si oui, comment ?
2) a défaut, cette fois ci, pas de parade, je DOIS pouvoir executer la formule "temp". comment faire ? existe t'il une facon de forcer excel a interpreter "temp" pour me donner insitu le résultat, sans avoir a poindre sur une cellule ?
parce qu'au fond, pour info, le "cl", c'est juste pour trouver la premiere cellule vide dispo, l'utiliser a l'arrache pour faire le calcul, récuperer le résultat, et re vider la cellule.
ce qui signifie que si mon probleme vient bien du fait qu'on ne peux modifier une cellule cible, au fond, contrairement a la premiere fois, la, je m'en fou un peu de la cellule !
seul l'execution de la formule (et son résultat) m'importe.
il existe peut etre donc des "callbyname" ou autre pour pouvoir directement executer le code sans cellule ?

je peux aussi avoir tord.

le fait est que l'autre exemple etait encore plus "basique" (value) et qu'en deux jours, je n'ai (ni personne d'ailleurs) trouvé de réponse probante.

cordialement.

 

[pierrejean]

Re : la methode "formulalocal" de l'objet "range" a échoué

Bonjour insosama

teste avec:

temp = "=Percage(6.31,2)"

Virgule en lieu et place de point-virgule

 

[insosama]

Re : la methode "formulalocal" de l'objet "range" a échoué

marcherait dans le cas de "formula" et non "formulalocal".
j'ai testé de tout basculer en "formula" plutot que "formulalocal", pour verrifier <=> strictement le meme soucis.
je suis repassé en formulalocal, car il est plus que pertinant pour l'utilisateur final de pouvoir entrer les formules en francais plutot qu'en anglais !

j'ai creusé mon idée de mon coté, pour en avoir le coeur net.
j'ai isolé les deux cas completement.
celui de ce topic, avec le formulalocal
et celui du simple déport de "value" dans des cellules distante.

bingo. ca bug.

dans un fichier, j'ai déclaré en A1 : "4"
en A2 : "8"
en A3 : "somme(A1;A2)"
en A4 : =test(A1;A2;A3)

normalement, je devrais avoir en A4 : 4 - 8
en B4 : 4*8
et en C4 : 4 + 8 (resultat de la somme)

la fonction est : (hormis basique)

Public Function test(a As Double, b As Double, Optional formule As String)
    
    Dim temp As String

    test = a - b
    Cells(Application.Caller.Row, Application.Caller.Column + 1) = a * b
    temp = "=" & formule
    Cells(Application.Caller.Row, Application.Caller.Column + 2).FormulaLocal = temp

End Function

et bien => pas de message d'erreur.
meme cas que dans le topic dont j'ai passé le lien. le code cesse de s'executer. par contre, je n'ai tout de meme pas de message d'erreur (une autre curiosité)

j'en reviens donc a mon interrogation

est il possible de pointer une cellule "B" (pour y mettre une formule ou une valeur) quand on execute la fonction au sein de la cellule "A" ? d'après mes test (et mes expérience) contre toute attente cela ne semble carrément pas marcher.
si tel est le cas, éventuellement, comment executer une formule sans passer par un objet range ? un "formula" tout seul ?
je pensais a traduire peut etre tout en anglais, puis faire un callbyname (encore que je sais pas si callbyname existe sous VBA)

des idées, des pistes ?
ou je me fourvois et y'a un truc évidant que je vois pas ?

merci pour toute aide et suggestion

cordialement.

 

[pierrejean]

Re : la methode "formulalocal" de l'objet "range" a échoué

Re

Percage étant manifestement une fonction personnalisée formula s'impose .Comment Excel pourrait-il 'traduire' un mot inconnu in english ?

Peux-tu matérialiser un peu plus ton problème dans un fichier test S.T.P ?

 

[insosama]

Re : la methode "formulalocal" de l'objet "range" a échoué

si il cherche a traduire des fonctions personnalisé ... la logique d'excel me dépassera toujours

j'ai carractérisé le probleme, toutefois, sans fonction personnalisé, dans mon second message. une seule fonction dans un module, 3 valeur dans 3 cellule. chez moi, cela ne marche pas.
et cela "prouverais" qu'il n'est pas possible de pointer une cellule B depuis la cellule A en écriture via une fonction (formule) que ce soit pour du "value" ou du "formula"

si tu veux vraiment un fichier :



cordialement.

 

[eriiic]

Re : la methode "formulalocal" de l'objet "range" a échoué

Bonjour,

Si j'ai bien compris ta question, une fonction retourne une valeur, et ne peut en aucun cas modifier une autre cellule ni son environnement (format, encadrement, etc).
eric

PS: par contre tu peux faire une fonction personnalisée matricielle qui te remplirait plusieurs cellules (si sélectionnées à la validation).

 

[insosama]

Re : la methode "formulalocal" de l'objet "range" a échoué

ouais, voila. tu confirme ce que j'avais deviné.

alors cela change radicalement tout mon systeme. car je n'ai pas envie de faire du matriciel sur ce cas (et il faut l'eviter), et pourtant, j'ai une "formule" qui en encapsule une autre, dynamique, que je dois calculer séparément impérativement.
je ne vois VRAIMENT PAS comment faire autrement que sus expliqué.
il faut absoluement que j'execute une "formula" pour en trouver le resultat et le traiter. sans cela, toute ma fonction s'effondre, et il n'est en aucun cas possible d'executer la formule autrement qu'a l'interieur de cette fonction

pour détailler mon probleme et que vous comprenniez l'utilité de la chose :
la formule "intermédiaire" doit se charger de changer les formalisme utilisateur dans une plage d'entrée de donnée. (par exemple, exemple bidon, changer des MPH par des KMH avec une fonction =ConvertVitesse() ...) et récuperer le résultat "modifié" pour le traiter ensuite classiquement. le tout, sans modifier la cellule d'origine.

c'est un exemple BIDON mais qui illustre parfaitement l'utilité de la chose et le but poursuivit.
que l'utilisateur entre des choses qui lui sont familiere, qu'elles y restent, mais que quand le calcul se fait, des formules dynamique (car chaque couple ligne / colonne a SA formule de conversion, et tout ce passage, celui qui amene au final au contenu de temp, marche parfaitement) vienne modifier la valeur pour la mettre dans un formalisme qui convient mieux au PC (mais qui est illisible)

bref, comment contourner le probleme ?

comment parvenir malgrès tout a executer du texte représentant une formule ?

je suis en train de creuser du coté des callbyname, mais cette fonction me parrait difficile a mettre en oeuvre dans mon cas :
quel "object" conviendrait ? quel "type de methode" ? get ? vbmethode ?
a priori pour le moment, ca serait sans doute la meilleur facon de faire.

cela oblige l'utilisateur a renseigner la matrice de controle (celle ou sont donné les listes de formules pour chaque référence de cellule) à l'anglaise, ou moi, a faire une fonction de traduction (ideal) mais ca serait un moindre mal.

toute idée me sauverait la mise, au stade en j'en suis.
parce que vraiment, je peux pas contourner le probleme comme je l'avais fait la premiere fois. cette fois ci, pas le choix, je DOIS réussir d'une facon ou une autre a "executer" formula au sein de la fonction.


cordialement.

 

[eriiic]

Re : la methode "formulalocal" de l'objet "range" a échoué

il faut absoluement que j'execute une "formula" pour en trouver le resultat et le traiter

Avec résultat dans la feuille ?
Sinon tu peux faire un evaluate()
Et autre idée (à creuser), se servir de l'évènement Calculate si tu dois vraiment mettre à jour d'autres cellules à la fin.
eric

eric

eric

 

[Dranreb]

Re : la methode "formulalocal" de l'objet "range" a échoué

Bonsoir.

L'impossibilité absolue de modifier quoi que soit dans un classeur durant la phase de calcul (et par définition une Function invoquée dans une cellule s'exécute durant la phase de calcul) est très fortement incontournable. Il m'est même arrivé de tenter involontairement de la contourner par un vrai timer. Cette exécution là, Excel n'à pas pu l'empêcher. Ça s'est donc soldé par une sortie instantanée et silencieuse de l'application avec perte de toutes les données !

En somme Excel préfère mourir plutôt que d'accepter de modifier quoi que ce soit pendant qu'il fait ses calculs !

En revanche tant qu ça reste confiné dans VBA et qu'on ne dérange pas Excel durant ses calculs, tout reste possible. Donc si on a absolument besoin qu'une fonction appelée depuis une formule entraine des changements dans d'autre cellules, il est possible de ranger dans une collection globale des sortes de consignes d'exécution ultérieure, collection qui sera analysée et vidée par une Worksheet_Calulate. C'est l'idée d'eriiiic je pense.

Je ne sais pas à quoi servent a et b dans votre Function test, mais pour peu que vous mettiez du natif dans A3 soit "sum(A1,A2)"
votre formule en A4:

=test(A1;A2;A3)

trouve 12, si vous l'écrivez :

Function test(a As Double, b As Double, Optional formule As String)
test = Evaluate(formule)
End Function

 

[insosama]

Re : la methode "formulalocal" de l'objet "range" a échoué

oui, évaluate que eriiiic m'avait deja conseillé m'a effectivement sauvé la mise.
ca oblige de passer par l'anglais, donc ca rend d'autant plus necessaire mon vieux projet de fonction de traduction automatique, mais ca marche.

ca marche aussi avec des fonctions perso, en plus, j'ai testé. aucun soucis.

merci pour tout.

(et y'a pas idée ce bloquage à la ....)

merci a vous trois pour votre aide et vos renseignement. il serait bon que les tuto sur les fonctions et les cours mentionnent CLAIREMENT ce point de bloquage, car il est nule part explicité. la derniere fois (cf le liens vers mon ancien post) personne (ni moi) n'a pensé a ca.
j'ai contourné le soucis par dépit, et c'est que hiers avec ce nouveau probleme que j'ai eue l'intuition que j'avais affaire au meme soucis.

cordialement.