Scopri come assegnare una macro ad un pulsante sul foglio di lavoro per automatizzare i calcoli. Crea una macro in Excel e impara come assegnare una macro a un oggetto

Come assegnare una macro ad un oggetto grafico in ONLYOFFICE

Usi macro per automatizzare i tuoi calcoli di routine? In questo post scopriremo un esempio in più su utilizzo di macro che potrebbe servirti.
<img decoding="async" loading="lazy" class="alignnone size-full wp-image-24459" src="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-assign-macros-to-shapes.jpg" alt="Assigning macros to graphic objects in ONLYOFFICE spreadsheets" width="736" height="371" srcset="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-assign-macros-to-shapes.jpg 736w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-assign-macros-to-shapes-300x151.jpg 300w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-assign-macros-to-shapes-634x320.jpg 634w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-assign-macros-to-shapes-353x178.jpg 353w" sizes="(max-width: 736px) 100vw, 736px" /> 
<h3>A cosa serve assegnare macro alla grafica</h3>
Nella <a href="https://wpblog.onlyoffice.com//it/2021/08/onlyoffice-docs-v6-4-con-formattazione-condizionale/?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape" target="_blank" rel="noopener">versione 6.4</a> di ONLYOFFICE Docs abbiamo aggiunto una funzionalità in più negli editor di fogli di calcolo — possibilità di assegnare macro ed eseguirla quando fai un clic su un oggetto grafico. Questo ti permette usare quelli oggetti grafici, ad esempio forme già pronte, come pulsanti e svolgere macro quando pulsante viene schiacciato.
Così gli utenti possono fare un clic sul pulsante con una macro assegnata per eseguire qualche azione. E’ più comodo invece che aprire la pagina con macro ogni volta quando uno vuole eseguirla.
<h3>Esempi di macro: diagramma automatico per mostrare tassi di cambio delle valute</h3>
Per farti vedere come funziona, abbiamo creato 3 macro per coppie di valute diverse* (EUR-USD, USD-EUR e BTC-USD) che ottengono le informazioni di tassi di cambio per ultimi giorni e compilano una tabella con i valori ricevuti.
Queste macro sono assegnate a 3 oggetti. Quando fai un clic su ogni oggetto svolgi una macro e così la tabella viene compilata con i dati pertinenti e il grafico verrà ricostruito.
Coppia di value USD – EUR
<a href="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur.png"><img decoding="async" loading="lazy" class="alignnone wp-image-24460" src="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur.png" alt="Assigning macros to graphic objects in ONLYOFFICE spreadsheets" width="736" height="418" srcset="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur.png 1114w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur-300x170.png 300w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur-1024x581.png 1024w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur-768x436.png 768w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur-314x178.png 314w" sizes="(max-width: 736px) 100vw, 736px" /></a>
Codice macro:
<pre class="theme:github font:monospace lang:js decode:true">(function()
{
 var sCurPair = "USD_EUR";
 
 function formatDate(d) {
 var month = '' + (d.getMonth() + 1),
 day = '' + d.getDate(),
 year = d.getFullYear();
 
 if (month.length &lt; 2) 
 month = '0' + month;
 if (day.length &lt; 2) 
 day = '0' + day;
 
 return [year, month, day].join('-');
 }
 
 function previousWeek(){
 var today = new Date();
 var prevweek = new Date(today.getFullYear(), today.getMonth(), today.getDate()-7);
 return prevweek;
 }
 
 var sDate = formatDate(previousWeek());
 var sEndDate = formatDate(new Date());
 var apiKey = 'e5ed9f0b2b3aa6f4158f';
 var sUrl = 'https://free.currconv.com/api/v7/convert?q='
 + sCurPair + '&amp;amp;amp;amp;compact=ultra' + '&amp;amp;amp;amp;date=' + sDate + "&amp;amp;amp;amp;endDate=" + sEndDate + '&amp;amp;amp;amp;apiKey=e5ed9f0b2b3aa6f4158f';
 var xmlHttp = new XMLHttpRequest();
 xmlHttp.open("GET", sUrl, false);
 xmlHttp.send();
 if (xmlHttp.readyState == 4 &amp;amp;amp;amp;&amp;amp;amp;amp; xmlHttp.status == 200) {
 var oData = JSON.parse(xmlHttp.responseText);
 for(var key in oData) {
 var sheet = Api.GetSheet("Sheet1");
 var oRange = sheet.GetRangeByNumber(0, 1);
 oRange.SetValue(key);
 var oDates = oData[key];
 var nRow = 1;
 for(var date in oDates) {
 oRange = sheet.GetRangeByNumber(nRow, 0);
 oRange.SetValue(date);
 oRange = sheet.GetRangeByNumber(nRow, 1);
 oRange.SetValue(oDates[date]);
 nRow++;
 }
 }
 }
})();</pre>
Currency pair EUR – USD
<a href="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd.png"><img decoding="async" loading="lazy" class="alignnone wp-image-24464" src="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd.png" alt="Assigning macros to graphic objects in ONLYOFFICE spreadsheets" width="736" height="418" srcset="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd.png 1116w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd-300x170.png 300w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd-1024x582.png 1024w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd-768x436.png 768w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd-313x178.png 313w" sizes="(max-width: 736px) 100vw, 736px" /></a>
Codice macro:
<pre class="theme:github font:monospace lang:js decode:true">(function()
{
 var sCurPair = "EUR_USD";
 
 function formatDate(d) {
 var month = '' + (d.getMonth() + 1),
 day = '' + d.getDate(),
 year = d.getFullYear();
 
 if (month.length &lt; 2) 
 month = '0' + month;
 if (day.length &lt; 2) 
 day = '0' + day;
 
 return [year, month, day].join('-');
 }
 
 function previousWeek(){
 var today = new Date();
 var prevweek = new Date(today.getFullYear(), today.getMonth(), today.getDate()-7);
 return prevweek;
 }
 
 var sDate = formatDate(previousWeek());
 var sEndDate = formatDate(new Date());
 var apiKey = 'e5ed9f0b2b3aa6f4158f';
 var sUrl = 'https://free.currconv.com/api/v7/convert?q='
 + sCurPair + '&amp;amp;compact=ultra' + '&amp;amp;date=' + sDate + "&amp;amp;endDate=" + sEndDate + '&amp;amp;apiKey=e5ed9f0b2b3aa6f4158f';
 var xmlHttp = new XMLHttpRequest();
 xmlHttp.open("GET", sUrl, false);
 xmlHttp.send();
 if (xmlHttp.readyState == 4 &amp;amp;&amp;amp; xmlHttp.status == 200) {
 var oData = JSON.parse(xmlHttp.responseText);
 for(var key in oData) {
 var sheet = Api.GetSheet("Sheet1");
 var oRange = sheet.GetRangeByNumber(0, 1);
 oRange.SetValue(key);
 var oDates = oData[key];
 var nRow = 1;
 for(var date in oDates) {
 oRange = sheet.GetRangeByNumber(nRow, 0);
 oRange.SetValue(date);
 oRange = sheet.GetRangeByNumber(nRow, 1);
 oRange.SetValue(oDates[date]);
 nRow++;
 }
 }
 }
})();</pre>
Currency pair BTC – USD
<a href="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd.png"><img decoding="async" loading="lazy" class="alignnone wp-image-24466" src="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd.png" alt="Assigning macros to graphic objects in ONLYOFFICE spreadsheets" width="736" height="417" srcset="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd.png 1115w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd-300x170.png 300w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd-1024x580.png 1024w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd-768x435.png 768w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd-315x178.png 315w" sizes="(max-width: 736px) 100vw, 736px" /></a>
Codice macro:
<pre class="theme:github font:monospace lang:js decode:true">(function()
{
 var sCurPair = "BTC_USD";
 
 function formatDate(d) {
 var month = '' + (d.getMonth() + 1),
 day = '' + d.getDate(),
 year = d.getFullYear();
 
 if (month.length &lt; 2) 
 month = '0' + month;
 if (day.length &lt; 2) 
 day = '0' + day;
 
 return [year, month, day].join('-');
 }
 
 function previousWeek(){
 var today = new Date();
 var prevweek = new Date(today.getFullYear(), today.getMonth(), today.getDate()-7);
 return prevweek;
 }
 
 var sDate = formatDate(previousWeek());
 var sEndDate = formatDate(new Date());
 var apiKey = 'e5ed9f0b2b3aa6f4158f';
 var sUrl = 'https://free.currconv.com/api/v7/convert?q='
 + sCurPair + '&amp;amp;compact=ultra' + '&amp;amp;date=' + sDate + "&amp;amp;endDate=" + sEndDate + '&amp;amp;apiKey=e5ed9f0b2b3aa6f4158f';
 var xmlHttp = new XMLHttpRequest();
 xmlHttp.open("GET", sUrl, false);
 xmlHttp.send();
 if (xmlHttp.readyState == 4 &amp;amp;&amp;amp; xmlHttp.status == 200) {
 var oData = JSON.parse(xmlHttp.responseText);
 for(var key in oData) {
 var sheet = Api.GetSheet("Sheet1");
 var oRange = sheet.GetRangeByNumber(0, 1);
 oRange.SetValue(key);
 var oDates = oData[key];
 var nRow = 1;
 for(var date in oDates) {
 oRange = sheet.GetRangeByNumber(nRow, 0);
 oRange.SetValue(date);
 oRange = sheet.GetRangeByNumber(nRow, 1);
 oRange.SetValue(oDates[date]);
 nRow++;
 }
 }
 }
})();</pre>
*Ti preghiamo di notare: Per creare queste macro abbiamo usato i servizi di terze parti CurrencyConverterApi.com per ottenere le informazioni di tassi di cambio. Hanno un limite di quantità di richieste all’ora. Se questo limite viene superato la macro non funziona più. Se vuoi usare questa macro meglio registrarsi sul sito del servizio e usare la chiave propria nel codice macro.
<div class="useful-links">
<h3>Useful links</h3>
<a href="https://wpblog.onlyoffice.com//2020/06/two-onlyoffice-macros-highlight-duplicates-and-go-to-the-next-empty-row/?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape" target="_blank" rel="noopener">Maggiori informazioni su macro in ONLYOFFICE</a>
Ottieni ultima versione degli editor: <a href="https://www.onlyoffice.com/it/download-docs.aspx?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape#docs-community" target="_blank" rel="noopener">community</a> / <a href="https://www.onlyoffice.com/it/download-docs.aspx?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape#docs-enterprise" target="_blank" rel="noopener">enterprise</a> / <a href="https://www.onlyoffice.com/it/download-docs.aspx?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape#docs-developer" target="_blank" rel="noopener">developer</a>
<a href="https://wpblog.onlyoffice.com//it/2021/08/onlyoffice-docs-v6-4-con-formattazione-condizionale/?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape" target="_blank" rel="noopener">Tutte le nuove funzionalità di ONLYOFFICE Docs v.6.4</a> 

<a href="https://wpblog.onlyoffice.com//it/2021/08/nuovi-plugin-per-onlyoffice-languagetool-e-grammalecte/?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape" target="_blank" rel="noopener">Nuovi plugin per controllo ortografico: LanguageTool e Grammalecte</a>
<a href="https://wpblog.onlyoffice.com//it/2021/08/creare-diagramma-di-gantt-in-excel/?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape" target="_blank" rel="noopener">Come creare diagramme di Gantt in fogli di calcolo</a>
<a href="https://github.com/ONLYOFFICE/" target="_blank" rel="noopener">ONLYOFFICE su GitHub</a>
</div>

Come assegnare una macro ad un oggetto grafico in fogli di calcolo ONLYOFFICE

Post recenti

Utilizza la macro ONLYOFFICE per analizzare i dati del foglio di calcolo

Back to school 2023 con ONLYOFFICE: sconti per l’istruzione

I migliori scrittori di IA 2023