回覆 s20012797
我覺得vba 好難被淘汰，好多銀行證券行或其它行業都用緊，積累左N 年，如果要轉，工程 ...
bongbong3481 發表於 2023/11/6 12:07 
未必咁多公司跟得咁貼用新版office , 而excel formula 係excel 一個重要核心，唔預期會消失吧….. ...
eddy 發表於 2023/11/6 15:14
無聊寫兩個code玩下
python
- from math import sin, acos, degrees, tan, pi
- from datetime import datetime, timedelta
- # Define a class to calculate sunrise and sunset times
- class SunriseSunsetCalculator:
- # Earth's tilt angle
- EARTH_TILT = 23.44
- # Conversion factor from degrees to radians
- TO_RADIANS = pi / 180
- # Method to calculate solar declination
- def calculate_declination(self, day_of_year):
- return self.EARTH_TILT * sin(self.TO_RADIANS * ((360 / 365.0) * (day_of_year - 81)))
- # Method to calculate hour angle
- def calculate_hour_angle(self, latitude, declination):
- return degrees(acos(-tan(latitude * self.TO_RADIANS) * tan(declination * self.TO_RADIANS)))
- # Method to calculate sunrise and sunset times
- def calculate(self, latitude, longitude):
- # Check if latitude and longitude are within valid range
- if not (-90 <= latitude <= 90) or not (-180 <= longitude <= 180):
- raise ValueError("Invalid latitude or longitude value.")
- # Get the current time
- now = datetime.now()
- # Get the current day of the year
- day_of_year = (now - datetime(now.year, 1, 1)).days + 1
- # Approximate calculation of solar declination
- declination = self.calculate_declination(day_of_year)
- # Calculate the hour angle
- hour_angle = self.calculate_hour_angle(latitude, declination)
- # Calculate sunrise and sunset times
- solar_noon = datetime.combine(now.date(), datetime.min.time()) + timedelta(hours=12)
- sunrise = solar_noon - timedelta(minutes=8 * hour_angle)
- sunset = solar_noon + timedelta(minutes=8 * hour_angle)
- return sunrise, sunset
- # Define a class to calculate the length of day and night in the Edo period
- class EdoPeriodClock:
- # A day and a night in the Edo period each have 6 hours
- HOURS_IN_EDO_DAY = 6
- HOURS_IN_EDO_NIGHT = 6
- # Method to calculate the length of day and night in the Edo period
- def calculate(self, latitude, longitude):
- # Calculate sunrise and sunset times
- calculator = SunriseSunsetCalculator()
- sunrise, sunset = calculator.calculate(latitude, longitude)
- # Calculate the length of day and night
- day_length = (sunset - sunrise).total_seconds() / 3600
- night_length = 24 - day_length
- # Calculate the length of day and night in the Edo period
- edo_day_hour_length = day_length / self.HOURS_IN_EDO_DAY
- edo_night_hour_length = night_length / self.HOURS_IN_EDO_NIGHT
- return edo_day_hour_length, edo_night_hour_length
- # Example usage
- latitude = 37.7749 # Latitude of a location
- longitude = -122.4194 # Longitude of a location
- edo_clock = EdoPeriodClock()
- edo_day_hour_length, edo_night_hour_length = edo_clock.calculate(latitude, longitude)
- print(f"Length of Edo day hour: {edo_day_hour_length} hours")
- print(f"Length of Edo night hour: {edo_night_hour_length} hours")
VBA
- Option Explicit
- ' Define a class to calculate sunrise and sunset times
- Class SunriseSunsetCalculator
- ' Earth's tilt angle
- Private Const EARTH_TILT As Double = 23.44
- ' Conversion factor from degrees to radians
- Private Const TO_RADIANS As Double = 3.14159265358979 / 180
- ' Method to calculate solar declination
- Private Function CalculateDeclination(day_of_year As Integer) As Double
- CalculateDeclination = EARTH_TILT * Sin(TO_RADIANS * ((360 / 365.0) * (day_of_year - 81)))
- End Function
- ' Method to calculate hour angle
- Private Function CalculateHourAngle(latitude As Double, declination As Double) As Double
- CalculateHourAngle = WorksheetFunction.Degrees(Acos(-Tan(latitude * TO_RADIANS) * Tan(declination * TO_RADIANS)))
- End Function
- ' Method to calculate sunrise and sunset times
- Public Function Calculate(latitude As Double, longitude As Double) As Variant
- ' Check if latitude and longitude are within valid range
- If latitude < -90 Or latitude > 90 Or longitude < -180 Or longitude > 180 Then
- Err.Raise Number:=5, Description:="Invalid latitude or longitude value."
- Exit Function
- End If
- ' Get the current time
- Dim now As Date
- now = Now()
- ' Get the current day of the year
- Dim day_of_year As Integer
- day_of_year = DateDiff("d", DateSerial(Year(now), 1, 0), now)
- ' Approximate calculation of solar declination
- Dim declination As Double
- declination = CalculateDeclination(day_of_year)
- ' Calculate the hour angle
- Dim hour_angle As Double
- On Error GoTo ErrorHandler
- hour_angle = CalculateHourAngle(latitude, declination)
- On Error GoTo 0
- ' Calculate sunrise and sunset times
- Dim solar_noon As Date
- solar_noon = Date + TimeSerial(12, 0, 0)
- Dim sunrise As Date
- sunrise = solar_noon - TimeSerial(8 * hour_angle / 60, 0, 0)
- Dim sunset As Date
- sunset = solar_noon + TimeSerial(8 * hour_angle / 60, 0, 0)
- Calculate = Array(sunrise, sunset)
- Exit Function
- ErrorHandler:
- Err.Raise Number:=Err.Number, Description:="Error calculating hour angle. " & Err.Description
- End Function
- End Class
- ' Define a class to calculate the length of day and night in the Edo period
- Class EdoPeriodClock
- ' A day and a night in the Edo period each have 6 hours
- Private Const HOURS_IN_EDO_DAY As Integer = 6
- Private Const HOURS_IN_EDO_NIGHT As Integer = 6
- ' Method to calculate the length of day and night in the Edo period
- Public Function Calculate(latitude As Double, longitude As Double) As Variant
- ' Calculate sunrise and sunset times
- Dim calculator As New SunriseSunsetCalculator
- Dim sunrise_sunset As Variant
- sunrise_sunset = calculator.Calculate(latitude, longitude)
- Dim sunrise As Date
- sunrise = sunrise_sunset(0)
- Dim sunset As Date
- sunset = sunrise_sunset(1)
- ' Calculate the length of day and night
- Dim day_length As Double
- day_length = (sunset - sunrise) * 24
- Dim night_length As Double
- night_length = 24 - day_length
- ' Calculate the length of day and night in the Edo period
- Dim edo_day_hour_length As Double
- edo_day_hour_length = day_length / HOURS_IN_EDO_DAY
- Dim edo_night_hour_length As Double
- edo_night_hour_length = night_length / HOURS_IN_EDO_NIGHT
- Calculate = Array(edo_day_hour_length, edo_night_hour_length)
- End Function
- End Class
純睇code&寫code而言,至少我吾覺得vba"仲適合學生(或初學)去學",至於"舊腳本"問題,買個Ai來重寫&捉蟲花吾上多少$,咁都比吾起學咩人做生意,就係咁 |
簡體版
訂閱頻道
訂閱電子報
發表於 2023-11-6 12:07
| 
