Altri Linguaggi - Trasformare u codice vba in formula excel 2003

16/05/11 11:10

È possibile trasformare questo codice in una semplice formula

Sub minuti()
Dim AG As Range
Dim AH As Range
Dim AI As Range
Dim F As Range
Dim G As Range
Dim H As Range
Dim vero As Boolean

Set AG = ThisWorkbook.ActiveSheet.Range("AG31")
Set AH = ThisWorkbook.ActiveSheet.Range("AH31")
Set AI = ThisWorkbook.ActiveSheet.Range("AI31")
Set F = ThisWorkbook.ActiveSheet.Range("F10")
Set G = ThisWorkbook.ActiveSheet.Range("G10")
Set H = ThisWorkbook.ActiveSheet.Range("H10")

vero = False
If ActiveSheet.Name = "RIEP" Then vero = True
If Not vero Then

Select Case True

'1° CASO AGGIUNTO
'TUTTE E 3 LE CELLE MINORI O UGUALI A 30
Case VBA.Minute(AG) <= 30 And VBA.Minute(AH) <= 30 And VBA.Minute(AI) <= 30
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)
'--------------------------------------------------------------------------------
'2° CASO AGGIUNTO
'1 CELLA su 3 MINORE O UGUALE A 30 (le altre 2 uguali a 0)
Case VBA.Minute(AG) <= 30 And VBA.Minute(AH) = 0 And VBA.Minute(AI) = 0
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) = 0 And VBA.Minute(AH) <= 30 And VBA.Minute(AI) = 0
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) = 0 And VBA.Minute(AH) = 0 And VBA.Minute(AI) <= 30
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)
'--------------------------------------------------------------------------------------
'3° CASO AGGIUNTO
'2 CELLE SU 3 MINORI O UGUALI A 30 (l'altra uguale a 0)
Case VBA.Minute(AG) <= 30 And VBA.Minute(AH) <= 30 And VBA.Minute(AI) = 0
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) <= 30 And VBA.Minute(AH) = 0 And VBA.Minute(AI) <= 30
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) = 0 And VBA.Minute(AH) <= 30 And VBA.Minute(AI) <= 30
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)
'------------------------------------------------------------------------------------------

'PRIMO CASO
Case VBA.Minute(AG) > 30 And VBA.Minute(AH) = 0 And VBA.Minute(AI) = 0
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) + 1 Else F = VBA.Hour(AG) + 1
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) = 0 And VBA.Minute(AH) > 30 And VBA.Minute(AI) = 0
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) = 0 And VBA.Minute(AH) = 0 And VBA.Minute(AI) > 30
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) + 1 Else H = VBA.Hour(AI) + 1
'---------------------------------------------------------------------------------

'SECONDO CASO
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) = 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) > 30 And VBA.Minute(AG) _
   + VBA.Minute(AH) <= 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) + 1 Else F = VBA.Hour(AG) + 1
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)
    
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) = 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) > 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)
'----------------------------------------------------------------------------------

'TERZO CASO
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) = 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AI) > 30 And VBA.Minute(AG) _
   + VBA.Minute(AI) <= 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) > 0 And VBA.Minute(AH) = 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AI) > 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) + 1 Else H = VBA.Hour(AI) + 1
'-------------------------------------------------------------------------------------

'QUARTO CASO
Case VBA.Minute(AG) = 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AH) + VBA.Minute(AI) > 30 And VBA.Minute(AH) _
   + VBA.Minute(AI) <= 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) = 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AH) + VBA.Minute(AI) > 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) + 1 Else H = VBA.Hour(AI) + 1
'-----------------------------------------------------------------------------------------

'OTTAVO CASO
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) > 150
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) + 2 Else H = VBA.Hour(AI) + 2
'------------------------------------------------------------------------------------------

'SETTIMO CASO
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) > 120 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) <= 150
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) + 2 Else H = VBA.Hour(AI) + 2
'--------------------------------------------------------------------------------------------

'SESTO CASO
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) > 60 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) <= 90
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) + 1 Else H = VBA.Hour(AI) + 1

Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) > 90 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) <= 120
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) + 1 Else H = VBA.Hour(AI) + 1
'-----------------------------------------------------------------------------------------

'QUINTO CASO
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) > 30 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) <= 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) > 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) + 1 Else H = VBA.Hour(AI) + 1
'--------------------------------------------------------------------------------------------


'---------------------------------------------------------------------------------------------
End Select
End If
Set AG = Nothing
Set AH = Nothing
Set AI = Nothing
Set F = Nothing
Set G = Nothing
Set H = Nothing
Application.EnableEvents = True
End Sub

aaa

16/05/11 20:04

lorenzo

vuoi la risposta irritata oppure la risposta di gentile irritazione???

Secondo te si può trasformare un codice così incasinato in una "Semplice" formula excel? Un minimo di decenza nello scrivere richieste ci andrebbe anche....

aaa

16/05/11 20:46

trittico69

altri forum mi hanno dettto di si e me lo stanno facendo

aaa

17/05/11 7:12

lorenzo

beati loro che hanno tempo da perdere...io di incasinarmi con excel non ci penso neanche e non capisco nemmeno il motivo.

Hai un codice vba, inseriscilo in un bottone, perché fare casino con una formula...

aaa

17/05/11 18:05

trittico69

Non so quale dei due esempi è corretto
Pimo caso

 =SE(E(MINUTO(AG31)<=30;MINUTO(AH31)<=30;MINUTO(AI31)<=30);SE(AG31>1;INT(AG31*24);ORA(AG31));0) +

Secondo caso

SE(E(MINUTO(AG31)<=30;MINUTO(AH31)=0;MINUTO(AI31)=0);SE(AG31>1;INT(AG31*24);ORA(AG31));0)……

oppure

primo caso

=SE(E(MINUTO(AG31)<=30;MINUTO(AH31)<=30;MINUTO(AI31)<=30);SE(AG31>1;INT(AG31*24);ORA(AG31));0) +
SE(E(MINUTO(AG31)<=30;MINUTO(AH31)<=30;MINUTO(AI31)<=30);SE(AG31>1;INT(AH31*24);ORA(AH31));0) +
SE(E(MINUTO(AG31)<=30;MINUTO(AH31)<=30;MINUTO(AI31)<=30);SE(AG31>1;INT(AI31*24);ORA(AI31));0) +

Secondo caso

SE(E(MINUTO(AG31)<=30;MINUTO(AH31)=0;MINUTO(AI31)=0);SE(AG31>1;INT(AG31*24);ORA(AG31));0)+
SE(E(MINUTO(AG31)<=30;MINUTO(AH31)=0;MINUTO(AI31)=0);SE(AG31>1;INT(AH31*24);ORA(AH31));0)+
SE(E(MINUTO(AG31)<=30;MINUTO(AH31)=0;MINUTO(AI31)=0);SE(AG31>1;INT(AI31*24);ORA(AI31));0)………………..

aaa