how do i pass parameters into function in vba-CodePudding

As the title, I wrote a function in VBA but the parameter didn't pass into the function. This's my code:

thanks you so much

    Function Solar_altitude_angle() As Single
    'Declare
Dim n As Integer
Dim Longtitude As Single
Dim Latitude As Single
Dim Altitude As Single
Dim LSM As Integer
Dim LST As Single
Dim ET As Single
Dim H As Single
Dim AST As Single
Dim i_SolarDeclination As Single
Dim s_Gama As Single

    'Equation time

n = DateDiff("d", "01/01/2021", ThisWorkbook.Worksheets("Room_Load").Range("D62"))
Longtitude = ThisWorkbook.Worksheets("Room_Load").Range("E61")
Latitude = ThisWorkbook.Worksheets("Room_Load").Range("B61")
Altitude = ThisWorkbook.Worksheets("Room_Load").Range("G61")
LSM = 105 'oE Greenwich
LST = ThisWorkbook.Worksheets("Room_Load").Range("H62")

i_SolarDeclination = 23.45 * Sin((n   284) / 365 * 2 * 3.14159)

s_Gama = ((n - 1) / 365) * 2 * 3.14159
ET = 2.2918 * (0.0075 _
              0.1868 * Cos(s_Gama) _
            - 3.2077 * Sin(s_Gama) _
            - 1.4615 * Cos(2 * s_Gama) _
            - 4.089 * Sin(2 * s_Gama))
   
AST = LST * 24   ET / 60   (Longtitude - LSM) / 15

H = 15 * (AST - 12)

Solar_altitude_angle = (Application.Asin(Cos(Longtitude) * Cos(i_SolarDeclination) * Cos(H)   _
                        Sin(Longtitude) * Sin(i_SolarDeclination))) * 180 / 3.1412
                        
End Function

as the picture above, in the watches window show variables invalid.

CodePudding user response：

I'm guessing you want to pass the ranges from Room_Load to your function.
Without any thought to what the parameter types should be except to get it working - I had to change n from a single to a date so DateDiff could calculate.

Each parameter to be passed is used in the function name.

Public Function Solar_altitude_angle(n As Date, Longtitude As Single, Latitude As Single, _
                                     Altitude As Single, LST As Single) As Single

    Dim n1 As Integer
    n1 = DateDiff("d", DateSerial(Year(Date), 1, 1), n)
    
    Dim LSM As Integer
    LSM = 105

    Dim i_SolarDeclination As Single
    i_SolarDeclination = 23.45 * Sin((n   284) / 365 * 2 * 3.14159)
    
    Dim s_Gama As Single
    s_Gama = ((n - 1) / 365) * 2 * 3.14159
    
    Dim ET As Single
    ET = 2.2918 * (0.0075 _
                  0.1868 * Cos(s_Gama) _
                - 3.2077 * Sin(s_Gama) _
                - 1.4615 * Cos(2 * s_Gama) _
                - 4.089 * Sin(2 * s_Gama))
       
    Dim AST As Single
    AST = LST * 24   ET / 60   (Longtitude - LSM) / 15
    
    Dim H As Single
    H = 15 * (AST - 12)
    
    Solar_altitude_angle = (Application.Asin(Cos(Longtitude) * Cos(i_SolarDeclination) * Cos(H)   _
                            Sin(Longtitude) * Sin(i_SolarDeclination))) * 180 / 3.1412

End Function

This can then be called from VBA using something like:

Sub Test()

    Dim MyResult As Single
    MyResult = Solar_altitude_angle(ThisWorkbook.Worksheets("Room_Load").Range("D62"), _
                                    ThisWorkbook.Worksheets("Room_Load").Range("E61"), _
                                    ThisWorkbook.Worksheets("Room_Load").Range("B61"), _
                                    ThisWorkbook.Worksheets("Room_Load").Range("G61"), _
                                    ThisWorkbook.Worksheets("Room_Load").Range("H62"))
                                

End Sub

or as a worksheet function:

=Solar_altitude_angle(Room_Load!D62,Room_Load!E61, Room_Load!B61, Room_Load!G61, Room_Load!H62)

=Solar_altitude_angle(D62,E61, B61,G61, H62)