[VBA] Creazione di UDF in Excel e passaggio celle come parametri

[ing82]

La domanda e' di tipo generale sulla programmazione in VBA per excel (spero di essere al posto giusto).
Al lavoro abbiamo ancora il vecchio excel, per intenderci quello che salva nel formato .xls.
Ho imparato a creare le UDF da impiegare nei fogli di calcolo (ho una discreta della programmazione ad oggetti con C++, mentre sono assolutamente principiante col VBA).
Il problema e' che avrei bisogno, oltre al risultato finale della UDF, anche alcuni valori di altre variabili interne alla funzione stessa da riportare in un foglio excel.
E' possibile passare alla funzione tra i parametri una cella in cui poi andra' a scrivere quel valore?
O per fare questo e' per forza necessario impiegare le SUB?
Se dovesse essere necessario passare inevitabilmente per le sub, e' possibile usarle senza dover aggiungere bottoni/pulsanti o cose varie?
Grazie

[patel]

io utilizzerei una sub che puoi chiamare con
call miasub(variabile1, variabile1, ...)
però puoi utilizzare anche una cella, non c'è bisogno di passarla alla function, la puoi utilizzare direttamente nella function
Le UDF sono comode perché possono essere utilizzate come funzioni del foglio, ma per il resto meglio le sub.

[ing82]

io utilizzerei una sub che puoi chiamare con
call miasub(variabile1, variabile1, ...)
però puoi utilizzare anche una cella, non c'è bisogno di passarla alla function, la puoi utilizzare direttamente nella function
Le UDF sono comode perché possono essere utilizzate come funzioni del foglio, ma per il resto meglio le sub.

Qui subentra la mia ignoranza, mai usata una sub.

diciamo che io abbia la seguente udf

Function miafunction(parametri)
  faccio qualcosa
  dim x as double
  faccio qualcosa su x che assume un nuovo valore
  questo nuovo valore di x mi servirebbe riportato in excel, oltre al valore finale restituito dalla funzione
  miafunction = risultato
end function

Non riesco a capire come sfruttare il call miasub che mi hai proposto, anche perche' da quello che ho capito, una function no puo' modificare il foglio di calcolo, quindi posso anche richiamare una sub da una function, ma a patto che non modifichi il foglio di calcolo, o sbaglio?
Ho fatto un tentativo in merito, richiamavo la sub dalla function mostrando un Msgbox, ma non c'è stato verso di scrivere un valore in una cella del foglio excel.

Grazie

Dimenticavo, la cella in cui voglio scrivere il risultato, vorrei che non fosse 'preimpostata', tipo cella A1, ma vorrei che passando la cella come parametro alla function (o sub), di questa venga preso l'indirizzo in cui poi scrivere il risultato, risulta molto piu' flessibile per il mio scopo.

Inoltre, c'e' un modo per far partire la sub senza aggiungere pulsanti, bottoni, ma che funzioni come una udf, cioe' viene rieseguita in automatico come tutte le funzioni di excel?

Grazie di nuovo

[gibra]

@ing82

Una Sub funziona esattamente come un Function, la sola differenza è che la Function può,e dovrebbe, restituire un valore che indica o meno il successo del codice eseguito, la Sub non restituisce alcun valore.

Public Function myFunction(parametri) As Boolean
    ' se il codice da eseguire ha successo ritorno Vero
    myFunction = True
End Function

Public Sub MySub(parametri)
    ' codice da eseguire
End Sub

Detto per inciso, il parametro restituito dovrebbe essere tipizzato (es. .. As Boolean, ... As Long, ... As Integer) mentre tu non lo dichiari.

[patel]

Probalmenente non ho capito cosa vuoi ottenere, non rimanere sul vago, allega un esempio di UDF funzionante a cui vuoi aggiungere le funzionalita che hai detto.

[patel]

Per chiarire meglio una function non può compiere azioni tipo scrivere in una cella diversa da quella in cui è chiamata oppure visualizzare un messaggio, la sub invece sì, ma non può essere chiamata come una formula del foglio.

[ing82]

Per chiarire meglio una function non può compiere azioni tipo scrivere in una cella diversa da quella in cui è chiamata oppure visualizzare un messaggio, la sub invece sì, ma non può essere chiamata come una formula del foglio.

ok, e' proprio questo il problema.

La funzione in questione e' piuttosto lunga, poco più di 200 righe di codice, e toglierebbe l'attenzione dal problema.
Raccontata a parole, la funzione prende n parametri in ingresso, e restituisce un valore, risultato finale dell'elaborazione.
L'elaborazione prevede anche delle variabili temporanee necessarie al suo completamento, di cui sarebbe utile conoscere il valore, ma la funzione puo' restituire un solo valore, per gli altri che fare?
La risposta dovrebbe essere uso una sub, ma:
- vorrei che la sub funzionasse come fosse una funzione, ammesso sia possibile, nel senso che venisse richiamata e ricalcolata in automatico (vorrei evitare pulsanti, form e cose del genere...);
- dato che la sub non funziona come una funzione, come faccio a passarle le celle da cui estrarre i dati (parametri), senza che la loro posizione nel foglio di calcolo sia fissata a priori? (in altre parole, i parametri che passo alla funzione possono stare una volta nella cella A1, l'altra in C543, lo decido io al momento, con la sub non mi pare sia possibile questo...

Sperando di aver chiarito, ringrazio nuovamente.

[patel]

- dato che la sub non funziona come una funzione, come faccio a passarle le celle da cui estrarre i dati (parametri), senza che la loro posizione nel foglio di calcolo sia fissata a priori? (in altre parole, i parametri che passo alla funzione possono stare una volta nella cella A1, l'altra in C543, lo decido io al momento, con la sub non mi pare sia possibile questo...

con una sub puoi leggere il contenuto della cella attiva e gestendo gli eventi puoi anche lanciare una sub semplicemente cliccando sulla cella con singolo o doppio clic

...La funzione in questione e' piuttosto lunga, poco più di 200 righe di codice, e toglierebbe l'attenzione dal problema.

infatti io ho parlato di esempio.

[gibra]

L'elaborazione prevede anche delle variabili temporanee necessarie al suo completamento, di cui sarebbe utile conoscere il valore, ma la funzione puo' restituire un solo valore, per gli altri che fare?

Si usano dei parametri per riferimento, dichiarati ByRef, esempio:

Public Function myFunction(Byval par1 As Long, ByVal par2 As Long, ByRef parRif1 As Boolean, ByRef parRif2 As String) As Long
     Dim lngRet As Long
     If (par1 > 0) And (Par2>0) Then
          lngRet = (par1 * par2)
          parRif1 = True
          parRif2 = "Moltiplicazione eseguita con successo"
      Else
           parRif1 = False
          parRif2 = "Moltiplicazione fallita"
      End If
      MyFunction = lngRet
End Function

Rem -------------------------------------
Rem Uso della funzione
Rem -------------------------------------
Dim lngRet As Long
Dim bEsito As Boolean
Dim sMsg As String

lngRet = MyFunction(15, 2 , bEsito, sMsg)
Debug.Print lngRet, besito, sMsg

[ing82]

Si usano dei parametri per riferimento, dichiarati ByRef, esempio:

Public Function myFunction(Byval par1 As Long, ByVal par2 As Long, ByRef parRif1 As Boolean, ByRef parRif2 As String) As Long
     Dim lngRet As Long
     If (par1 > 0) And (Par2>0) Then
          lngRet = (par1 * par2)
          parRif1 = True
          parRif2 = "Moltiplicazione eseguita con successo"
      Else
           parRif1 = False
          parRif2 = "Moltiplicazione fallita"
      End If
      MyFunction = lngRet
End Function

Rem -------------------------------------
Rem Uso della funzione
Rem -------------------------------------
Dim lngRet As Long
Dim bEsito As Boolean
Dim sMsg As String

lngRet = MyFunction(15, 2 , bEsito, sMsg)
Debug.Print lngRet, besito, sMsg

Vediamo se inizio a capirci qualcosa.
La funzione postata da gibra, puo' essere usata cosi' come e' in un foglio di calcolo, ma l'unico valore che ottengo nel foglio di calcolo e' lngRet, tutto il resto va perso in quanto sto usando una function.
Se voglio ottenere anche quei valori, allora credo che dovro' fare una sub del genere (prendetelo come pseudocodice, serve solo per proseguire nella discussione, la sintassi del VBA devo ancora digerirla essendo alle prime armi), rimanendomi i seguenti dubbi, per come vorrei poterla usare:
- i 4 parametri da passare alla sub, quindi alla function, vorrei che fossero delle celle da definire 'al momento', non predefinite (con le funzioni, selezionerei col mouse ad esempio la cella in cui c'e' il parametro che sto per passare, con la sub non so se e' possibile questo)
- vorrei che la sub venisse richiamata in automatico, come le funzioni di excel, o almeno quando una delle celle contenente i parametri a cui si riferisce viene modificata
- molto probabilmente i byval e byref della sub sono da sistemare, ma per ora mi interessa capire se quello che ho in mente e' possibile o no senza scomodare pulsanti, form e altro, altrimenti torno al C++

sub miasub(Byval par1 As Long, ByVal par2 As Long, ByRef parRif1 As Boolean, ByRef parRif2 As String)
  call MyFunction(par1, par2, parRif1, parRif2)
end sub

Grazie