Excel VBA Code: Compile Error in x64 Version ('PtrSafe' attribute required)

Question

I am using Win8 x64 + Office 2013 x64.

MY PROBLEM:

I have an excel file, which has some modules in it, and works flawlessly in Office (Excel) x86. It uses the Swiss Ephemeris file (swedll32.dll) to do a lot of astronomical calculations.

However, when I try to run it on my end, which is on Excel x64, ASAP it's opened, the VBA editor fires up and gives me the following error:

Compile error:
The code in this project must be updated for use on 64-bit systems.
Please review and update Declare statements and then mark them with the PtrSafe attribute.

As I said above, it works only in 32bit office (Excel) but it won't work in my x64 Excel However there seems to be a workaround but I am too much of a novice to get it.

I am no good in VBA, however, after digging over the Internet, a few websites proposed that for 64bit office (Excel), we should amend (for each declaration) the codes to something like that:

• Original Statement: Private Declare Function
• Amended Statement: Private Declare PtrSafe Function

Unfortunately, after adding 'PtrSafe' everywhere, though the VBA errors stopped, but the values are not being displayed/calculated properly in the cells.

From a post on 64 Bit in VBA from Microsoft, it would seem that we also need to modify some of the data types such as:

• Long → to → LongPtr
• LongPtr → to → LongLong
• and some "Long" are supposed to remain the same, depending on what they do.

This website (under "Which Longs should become LongPtr?") gives a clue though, as to how to know which one to modify.

I do not understand a thing about it and have no idea what should be done really to make this work in Office (Excel) x64. Someone who is pretty good at this, kindly please help me. Please.

note:

• Only the declarations that appeared in red, denoting that they contain errors have been dumped here. The remaining portion of the codes were not pasted here because they appear fine (green colored).
• For the Excel file to work properly on your computer, ensure that both the 'swedll32.dll' & the 'JCalc Ver 0.2d.xls' are in the same folder, after having extracting them.

Problem in Module Name: MainCalculations

'Swiss Ephemeris Release 1.60  9-jan-2000
'
' Declarations for Visual Basic 5.0
' The DLL file must exist in the same directory, or in a system
' directory where it can be found at runtime
'

    Private Declare Function swe_azalt Lib "swedll32.dll" _
    Alias "_swe_azalt@40" ( _
      ByVal tjd_ut As Double, _
      ByVal calc_flag As Long, _
      ByRef geopos As Double, _
      ByVal atpress As Double, _
      ByVal attemp As Double, _
      ByRef xin As Double, _
      ByRef xaz As Double _
    ) As Long  'geopos must be the first of three array elements
               'xin must be the first of two array elements
               'xaz must be the first of three array elements

    Private Declare Function swe_azalt_rev Lib "swedll32.dll" _
    Alias "_swe_azalt_rev@24" ( _
      ByVal tjd_ut As Double, _
      ByVal calc_flag As Long, _
      ByRef geopos As Double, _
      ByRef xin As Double, _
      ByRef xout As Double _
    ) As Long  'geopos must be the first of three array elements
               'xin must be the first of two array elements
               'xout must be the first of three array elements

Private Declare Function swe_calc Lib "swedll32.dll" _
    Alias "_swe_calc@24" ( _
      ByVal tjd As Double, _
      ByVal ipl As Long, _
      ByVal iflag As Long, _
      ByRef X As Double, _
      ByVal serr As String _
    ) As Long   ' x must be first of six array elements
                ' serr must be able to hold 256 bytes

Private Declare Function swe_calc_d Lib "swedll32.dll" _
    Alias "_swe_calc_d@20" ( _
      ByRef tjd As Double, _
      ByVal ipl As Long, _
      ByVal iflag As Long, _
      ByRef X As Double, _
      ByVal serr As String _
    ) As Long       ' x must be first of six array elements
                    ' serr must be able to hold 256 bytes

Private Declare Function swe_calc_ut Lib "swedll32.dll" _
    Alias "_swe_calc_ut@24" ( _
      ByVal tjd_ut As Double, _
      ByVal ipl As Long, _
      ByVal iflag As Long, _
      ByRef X As Double, _
      ByVal serr As String _
    ) As Long   ' x must be first of six array elements
                ' serr must be able to hold 256 bytes

Private Declare Function swe_calc_ut_d Lib "swedll32.dll" _
    Alias "_swe_calc_ut_d@20" ( _
      ByRef tjd_ut As Double, _
      ByVal ipl As Long, _
      ByVal iflag As Long, _
      ByRef X As Double, _
      ByVal serr As String _
    ) As Long       ' x must be first of six array elements
                    ' serr must be able to hold 256 bytes

Private Declare Function swe_close Lib "swedll32.dll" _
    Alias "_swe_close@0" ( _
    ) As Long

Private Declare Function swe_close_d Lib "swedll32.dll" _
    Alias "_swe_close_d@4" ( _
      ByVal ivoid As Long _
    ) As Long       ' argument ivoid is ignored

Private Declare Sub swe_cotrans Lib "swedll32.dll" _
    Alias "_swe_cotrans@16" ( _
      ByRef xpo As Double, _
      ByRef xpn As Double, _
      ByVal eps As Double _
    )

Private Declare Function swe_cotrans_d Lib "swedll32.dll" _
    Alias "_swe_cotrans_d@12" ( _
      ByRef xpo As Double, _
      ByRef xpn As Double, _
      ByRef eps As Double _
    ) As Long

Private Declare Sub swe_cotrans_sp Lib "swedll32.dll" _
    Alias "_swe_cotrans_sp@16" ( _
      ByRef xpo As Double, _
      ByRef xpn As Double, _
      ByVal eps As Double _
    )

Private Declare Function swe_cotrans_sp_d Lib "swedll32.dll" _
    Alias "_swe_cotrans_sp_d@12" ( _
      ByRef xpo As Double, _
      ByRef xpn As Double, _
      ByRef eps As Double _
    ) As Long

Private Declare Sub swe_cs2degstr Lib "swedll32.dll" _
    Alias "_swe_cs2degstr@8" ( _
      ByVal t As Long, _
      ByVal S As String _
    )

Private Declare Function swe_cs2degstr_d Lib "swedll32.dll" _
    Alias "_swe_cs2degstr_d@8" ( _
      ByVal t As Long, _
      ByVal S As String _
    ) As Long

Private Declare Sub swe_cs2lonlatstr Lib "swedll32.dll" _
    Alias "_swe_cs2lonlatstr@16" ( _
      ByVal t As Long, _
      ByVal pchar As Byte, _
      ByVal mchar As Byte, _
      ByVal S As String _
    )

Private Declare Function swe_cs2lonlatstr_d Lib "swedll32.dll" _
    Alias "_swe_cs2lonlatstr_d@16" ( _
      ByVal t As Long, _
      ByRef pchar As Byte, _
      ByRef mchar As Byte, _
      ByVal S As String _
    ) As Long

Private Declare Sub swe_cs2timestr Lib "swedll32.dll" _
    Alias "_swe_cs2timestr@16" ( _
      ByVal t As Long, _
      ByVal sep As Long, _
      ByVal supzero As Long, _
      ByVal S As String _
    )

Private Declare Function swe_cs2timestr_d Lib "swedll32.dll" _
    Alias "_swe_cs2timestr_d@16" ( _
      ByVal t As Long, _
      ByVal sep As Long, _
      ByVal supzero As Long, _
      ByVal S As String _
    ) As Long

Private Declare Function swe_csnorm Lib "swedll32.dll" _
    Alias "_swe_csnorm@4" ( _
      ByVal P As Long _
    ) As Long

Private Declare Function swe_csnorm_d Lib "swedll32.dll" _
    Alias "_swe_csnorm_d@4" ( _
      ByVal P As Long _
    ) As Long

Private Declare Function swe_csroundsec Lib "swedll32.dll" _
    Alias "_swe_csroundsec@4" ( _
      ByVal P As Long _
    ) As Long

Private Declare Function swe_csroundsec_d Lib "swedll32.dll" _
    Alias "_swe_csroundsec_d@4" ( _
      ByVal P As Long _
    ) As Long

Private Declare Function swe_d2l Lib "swedll32.dll" _
    Alias "_swe_d2l@8" ( _
    ) As Long

Private Declare Function swe_d2l_d Lib "swedll32.dll" _
    Alias "_swe_d2l_d@4" ( _
    ) As Long

Private Declare Function swe_date_conversion Lib "swedll32.dll" _
    Alias "_swe_date_conversion@28" ( _
      ByVal Year As Long, _
      ByVal Month As Long, _
      ByVal Day As Long, _
      ByVal utime As Double, _
      ByVal cal As Byte, _
      ByRef tjd As Double _
    ) As Long

Private Declare Function swe_date_conversion_d Lib "swedll32.dll" _
    Alias "_swe_date_conversion_d@24" ( _
      ByVal Year As Long, _
      ByVal Month As Long, _
      ByVal Day As Long, _
      ByRef utime As Double, _
      ByRef cal As Byte, _
      ByRef tjd As Double _
    ) As Long

Private Declare Function swe_day_of_week Lib "swedll32.dll" _
    Alias "_swe_day_of_week@8" ( _
      ByVal JD As Double _
    ) As Long

Private Declare Function swe_day_of_week_d Lib "swedll32.dll" _
    Alias "_swe_day_of_week_d@4" ( _
      ByRef JD As Double _
    ) As Long

Private Declare Function swe_degnorm Lib "swedll32.dll" _
    Alias "_swe_degnorm@8" ( _
      ByVal JD As Double _
    ) As Double

Private Declare Function swe_degnorm_d Lib "swedll32.dll" _
    Alias "_swe_degnorm_d@4" ( _
      ByRef JD As Double _
    ) As Long

Private Declare Function swe_deltat Lib "swedll32.dll" _
    Alias "_swe_deltat@8" ( _
      ByVal JD As Double _
    ) As Double

Private Declare Function swe_deltat_d Lib "swedll32.dll" _
    Alias "_swe_deltat_d@8" ( _
      ByRef JD As Double, _
      ByRef deltat As Double _
    ) As Long

Private Declare Function swe_difcs2n Lib "swedll32.dll" _
    Alias "_swe_difcs2n@8" ( _
      ByVal p1 As Long, _
      ByVal p2 As Long _
    ) As Long

Private Declare Function swe_difcs2n_d Lib "swedll32.dll" _
    Alias "_swe_difcs2n_d@8" ( _
      ByVal p1 As Long, _
      ByVal p2 As Long _
    ) As Long

Private Declare Function swe_difcsn Lib "swedll32.dll" _
    Alias "_swe_difcsn@8" ( _
      ByVal p1 As Long, _
      ByVal p2 As Long _
    ) As Long

Private Declare Function swe_difcsn_d Lib "swedll32.dll" _
    Alias "_swe_difcsn_d@8" ( _
      ByVal p1 As Long, _
      ByVal p2 As Long _
    ) As Long

Private Declare Function swe_difdeg2n Lib "swedll32.dll" _
    Alias "_swe_difdeg2n@16" ( _
      ByVal p1 As Double, _
      ByVal p2 As Double _
    ) As Double

Private Declare Function swe_difdeg2n_d Lib "swedll32.dll" _
    Alias "_swe_difdeg2n_d@12" ( _
      ByRef p1 As Double, _
      ByRef p2 As Double, _
      ByRef Diff As Double _
    ) As Long

Private Declare Function swe_difdegn Lib "swedll32.dll" _
    Alias "_swe_difdegn@16" ( _
      ByVal p1 As Double, _
      ByVal p2 As Double _
    ) As Long

Private Declare Function swe_difdegn_d Lib "swedll32.dll" _
    Alias "_swe_difdegn_d@12" ( _
      ByRef p1 As Double, _
      ByRef p2 As Double, _
      ByRef Diff As Double _
    ) As Long

Private Declare Function swe_fixstar Lib "swedll32.dll" _
    Alias "_swe_fixstar@24" ( _
      ByVal star As String, _
      ByVal tjd As Double, _
      ByVal iflag As Long, _
      ByRef X As Double, _
      ByVal serr As String _
    ) As Long       ' x must be first of six array elements
                    ' serr must be able to hold 256 bytes
                    ' star must be able to hold 40 bytes

Private Declare Function swe_fixstar_d Lib "swedll32.dll" _
    Alias "_swe_fixstar_d@20" ( _
      ByVal star As String, _
      ByRef tjd As Double, _
      ByVal iflag As Long, _
      ByRef X As Double, _
      ByVal serr As String _
    ) As Long       ' x must be first of six array elements
                    ' serr must be able to hold 256 bytes
                    ' star must be able to hold 40 bytes

Private Declare Function swe_fixstar_ut Lib "swedll32.dll" _
    Alias "_swe_fixstar_ut@24" ( _
      ByVal star As String, _
      ByVal tjd_ut As Double, _
      ByVal iflag As Long, _
      ByRef X As Double, _
      ByVal serr As String _
    ) As Long       ' x must be first of six array elements
                    ' serr must be able to hold 256 bytes
                    ' star must be able to hold 40 bytes

Private Declare Function swe_fixstar_ut_d Lib "swedll32.dll" _
    Alias "_swe_fixstar_ut_d@20" ( _
      ByVal star As String, _
      ByRef tjd_ut As Double, _
      ByVal iflag As Long, _
      ByRef X As Double, _
      ByVal serr As String _
    ) As Long       ' x must be first of six array elements
                    ' serr must be able to hold 256 bytes
                    ' star must be able to hold 40 bytes

Private Declare Function swe_get_ayanamsa Lib "swedll32.dll" _
    Alias "_swe_get_ayanamsa@8" ( _
      ByVal tjd_et As Double _
    ) As Double

Private Declare Function swe_get_ayanamsa_d Lib "swedll32.dll" _
    Alias "_swe_get_ayanamsa_d@8" ( _
      ByRef tjd_et As Double, _
      ByRef ayan As Double _
    ) As Long

Private Declare Function swe_get_ayanamsa_ut Lib "swedll32.dll" _
    Alias "_swe_get_ayanamsa_ut@8" ( _
      ByVal tjd_ut As Double _
    ) As Double

Private Declare Function swe_get_ayanamsa_ut_d Lib "swedll32.dll" _
    Alias "_swe_get_ayanamsa_ut_d@8" ( _
      ByRef tjd_ut As Double, _
      ByRef ayan As Double _
    ) As Long

Private Declare Sub swe_get_planet_name Lib "swedll32.dll" _
    Alias "_swe_get_planet_name@8" ( _
      ByVal ipl As Long, _
      ByVal pname As String _
    )

Private Declare Function swe_get_planet_name_d Lib "swedll32.dll" _
    Alias "_swe_get_planet_name_d@8" ( _
      ByVal ipl As Long, _
      ByVal pname As String _
    ) As Long

Private Declare Function swe_get_tid_acc Lib "swedll32.dll" _
    Alias "_swe_get_tid_acc@0" ( _
    ) As Double

Private Declare Function swe_get_tid_acc_d Lib "swedll32.dll" _
    Alias "_swe_get_tid_acc_d@4" ( _
      ByRef X As Double _
    ) As Long

Private Declare Function swe_houses Lib "swedll32.dll" _
    Alias "_swe_houses@36" ( _
      ByVal tjd_ut As Double, _
      ByVal geolat As Double, _
      ByVal geolon As Double, _
      ByVal ihsy As Long, _
      ByRef hcusps As Double, _
      ByRef ascmc As Double _
    ) As Long       ' hcusps must be first of 13 array elements
                    ' ascmc must be first of 10 array elements

Private Declare Function swe_houses_d Lib "swedll32.dll" _
    Alias "_swe_houses_d@24" ( _
      ByRef tjd_ut As Double, _
      ByRef geolat As Double, _
      ByRef geolon As Double, _
      ByVal ihsy As Long, _
      ByRef hcusps As Double, _
      ByRef ascmc As Double _
    ) As Long       ' hcusps must be first of 13 array elements
                    ' ascmc must be first of 10 array elements

Private Declare Function swe_houses_ex Lib "swedll32.dll" _
    Alias "_swe_houses_ex@40" ( _
      ByVal tjd_ut As Double, _
      ByVal iflag As Long, _
      ByVal geolat As Double, _
      ByVal geolon As Double, _
      ByVal ihsy As Long, _
      ByRef hcusps As Double, _
      ByRef ascmc As Double _
    ) As Long       ' hcusps must be first of 13 array elements
                    ' ascmc must be first of 10 array elements

Private Declare Function swe_houses_ex_d Lib "swedll32.dll" _
    Alias "_swe_houses_ex_d@28" ( _
      ByRef tjd_ut As Double, _
      ByVal iflag As Long, _
      ByRef geolat As Double, _
      ByRef geolon As Double, _
      ByVal ihsy As Long, _
      ByRef hcusps As Double, _
      ByRef ascmc As Double _
    ) As Long       ' hcusps must be first of 13 array elements
                    ' ascmc must be first of 10 array elements

Private Declare Function swe_houses_armc Lib "swedll32.dll" _
    Alias "_swe_houses_armc@36" ( _
      ByVal armc As Double, _
      ByVal geolat As Double, _
      ByVal eps As Double, _
      ByVal ihsy As Long, _
      ByRef hcusps As Double, _
      ByRef ascmc As Double _
    ) As Long       ' hcusps must be first of 13 array elements
                    ' ascmc must be first of 10 array elements

Private Declare Function swe_houses_armc_d Lib "swedll32.dll" _
    Alias "_swe_houses_armc_d@24" ( _
      ByRef armc As Double, _
      ByRef geolat As Double, _
      ByRef eps As Double, _
      ByVal ihsy As Long, _
      ByRef hcusps As Double, _
      ByRef ascmc As Double _
    ) As Long       ' hcusps must be first of 13 array elements
                    ' ascmc must be first of 10 array elements

Private Declare Function swe_house_pos Lib "swedll32.dll" _
    Alias "_swe_house_pos@36" ( _
      ByVal armc As Double, _
      ByVal geolat As Double, _
      ByVal eps As Double, _
      ByVal ihsy As Long, _
      ByRef xpin As Double, _
      ByVal serr As String _
    ) As Double
                    ' xpin must be first of 2 array elements

Private Declare Function swe_house_pos_d Lib "swedll32.dll" _
    Alias "_swe_house_pos_d@28" ( _
      ByRef armc As Double, _
      ByRef geolat As Double, _
      ByRef eps As Double, _
      ByVal ihsy As Long, _
      ByRef xpin As Double, _
      ByRef hpos As Double, _
      ByVal serr As String _
    ) As Long
                    ' xpin must be first of 2 array elements

Private Declare Function swe_julday Lib "swedll32.dll" _
    Alias "_swe_julday@24" ( _
      ByVal Year As Long, _
      ByVal Month As Long, _
      ByVal Day As Long, _
      ByVal hour As Double, _
      ByVal gregflg As Long _
    ) As Double

Private Declare Function swe_julday_d Lib "swedll32.dll" _
    Alias "_swe_julday_d@24" ( _
      ByVal Year As Long, _
      ByVal Month As Long, _
      ByVal Day As Long, _
      ByRef hour As Double, _
      ByVal gregflg As Long, _
      ByRef tjd As Double _
    ) As Long

Private Declare Function swe_lun_eclipse_how Lib "swedll32.dll" _
    Alias "_swe_lun_eclipse_how@24" ( _
      ByVal tjd_ut As Double, _
      ByVal ifl As Long, _
      ByRef geopos As Double, _
      ByRef attr As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_lun_eclipse_how_d Lib "swedll32.dll" _
    Alias "_swe_lun_eclipse_how_d@20" ( _
      ByRef tjd_ut As Double, _
      ByVal ifl As Long, _
      ByRef geopos As Double, _
      ByRef attr As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_lun_eclipse_when Lib "swedll32.dll" _
    Alias "_swe_lun_eclipse_when@28" ( _
      ByVal tjd_start As Double, _
      ByVal ifl As Long, _
      ByVal ifltype As Long, _
      ByRef tret As Double, _
      ByVal backward As Long, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_lun_eclipse_when_d Lib "swedll32.dll" _
    Alias "_swe_lun_eclipse_when_d@24" ( _
      ByRef tjd_start As Double, _
      ByVal ifl As Long, _
      ByVal ifltype As Long, _
      ByRef tret As Double, _
      ByVal backward As Long, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_nod_aps Lib "swedll32.dll" _
    Alias "_swe_nod_aps@40" ( _
      ByVal tjd_et As Double, _
      ByVal ipl As Long, _
      ByVal iflag As Long, _
      ByVal method As Long, _
      ByRef xnasc As Double, _
      ByRef xndsc As Double, _
      ByRef xperi As Double, _
      ByRef xaphe As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_nod_aps_ut Lib "swedll32.dll" _
    Alias "_swe_nod_aps_ut@40" ( _
      ByVal tjd_ut As Double, _
      ByVal ipl As Long, _
      ByVal iflag As Long, _
      ByVal method As Long, _
      ByRef xnasc As Double, _
      ByRef xndsc As Double, _
      ByRef xperi As Double, _
      ByRef xaphe As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_pheno Lib "swedll32.dll" _
    Alias "_swe_pheno@24" ( _
      ByVal tjd As Double, _
      ByVal ipl As Long, _
      ByVal iflag As Long, _
      ByRef attr As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_pheno_ut Lib "swedll32.dll" _
    Alias "_swe_pheno_ut@24" ( _
      ByVal tjd As Double, _
      ByVal ipl As Long, _
      ByVal iflag As Long, _
      ByRef attr As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_pheno_d Lib "swedll32.dll" _
    Alias "_swe_pheno_d@20" ( _
      ByRef tjd As Double, _
      ByVal ipl As Long, _
      ByVal iflag As Long, _
      ByRef attr As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_pheno_ut_d Lib "swedll32.dll" _
    Alias "_swe_pheno_ut_d@20" ( _
      ByRef tjd As Double, _
      ByVal ipl As Long, _
      ByVal iflag As Long, _
      ByRef attr As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_refrac Lib "swedll32.dll" _
    Alias "_swe_refrac@28" ( _
      ByVal inalt As Double, _
      ByVal atpress As Double, _
      ByVal attemp As Double, _
      ByVal calc_flag As Long _
    ) As Double

Private Declare Sub swe_revjul Lib "swedll32.dll" _
    Alias "_swe_revjul@28" ( _
      ByVal tjd As Double, _
      ByVal gregflg As Long, _
      ByRef Year As Long, _
      ByRef Month As Long, _
      ByRef Day As Long, _
      ByRef hour As Double _
    )

Private Declare Function swe_revjul_d Lib "swedll32.dll" _
    Alias "_swe_revjul_d@24" ( _
      ByRef tjd As Double, _
      ByVal gregflg As Long, _
      ByRef Year As Long, _
      ByRef Month As Long, _
      ByRef Day As Long, _
      ByRef hour As Double _
    ) As Long

Private Declare Function swe_rise_trans Lib "swedll32.dll" _
    Alias "_swe_rise_trans@52" ( _
      ByVal tjd_ut As Double, _
      ByVal ipl As Long, _
      ByVal starname As String, _
      ByVal epheflag As Long, _
      ByVal rsmi As Long, _
      ByRef geopos As Double, _
      ByVal atpress As Double, _
      ByVal attemp As Double, _
      ByRef tret As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Sub swe_set_ephe_path Lib "swedll32.dll" _
    Alias "_swe_set_ephe_path@4" ( _
      ByVal path As String _
    )

Private Declare Function swe_set_ephe_path_d Lib "swedll32.dll" _
    Alias "_swe_set_ephe_path_d@4" ( _
      ByVal path As String _
    ) As Long

Private Declare Sub swe_set_jpl_file Lib "swedll32.dll" _
    Alias "_swe_set_jpl_file@4" ( _
      ByVal file As String _
    )

Private Declare Function swe_set_jpl_file_d Lib "swedll32.dll" _
    Alias "_swe_set_jpl_file_d@4" ( _
      ByVal file As String _
    ) As Long

Private Declare Function swe_set_sid_mode Lib "swedll32.dll" _
    Alias "_swe_set_sid_mode@20" ( _
      ByVal sid_mode As Long, _
      ByVal t0 As Double, _
      ByVal ayan_t0 As Double _
    ) As Long

Private Declare Function swe_set_sid_mode_d Lib "swedll32.dll" _
    Alias "_swe_sid_mode_d@12" ( _
      ByVal sid_mode As Long, _
      ByRef t0 As Double, _
      ByRef ayan_t0 As Double _
    ) As Long

Private Declare Function swe_set_topo Lib "swedll32.dll" _
    Alias "_swe_set_topo@24" ( _
      ByVal geolon As Double, _
      ByVal geolat As Double, _
      ByVal altitude As Double _
    )

Private Declare Function swe_set_topo_d Lib "swedll32.dll" _
    Alias "_swe_set_topo_d@12" ( _
      ByRef geolon As Double, _
      ByRef geolat As Double, _
      ByRef altitude As Double _
    )

Private Declare Sub swe_set_tid_acc Lib "swedll32.dll" _
    Alias "_swe_set_tid_acc@8" ( _
      ByVal X As Double _
    )

Private Declare Function swe_set_tid_acc_d Lib "swedll32.dll" _
    Alias "_swe_set_tid_acc_d@4" ( _
      ByRef X As Double _
    ) As Long

Private Declare Function swe_sidtime0 Lib "swedll32.dll" _
    Alias "_swe_sidtime0@24" ( _
      ByVal tjd_ut As Double, _
      ByVal ecl As Double, _
      ByVal nut As Double _
    ) As Double

Private Declare Function swe_sidtime0_d Lib "swedll32.dll" _
    Alias "_swe_sidtime0_d@16" ( _
      ByRef tjd_ut As Double, _
      ByRef ecl As Double, _
      ByRef nut As Double, _
      ByRef sidt As Double _
    ) As Long

Private Declare Function swe_sidtime Lib "swedll32.dll" _
    Alias "_swe_sidtime@8" ( _
      ByVal tjd_ut As Double _
    ) As Double

Private Declare Function swe_sidtime_d Lib "swedll32.dll" _
    Alias "_swe_sidtime_d@8" ( _
      ByRef tjd_ut As Double, _
      ByRef sidt As Double _
    ) As Long

Private Declare Function swe_sol_eclipse_how Lib "swedll32.dll" _
    Alias "_swe_sol_eclipse_how@24" ( _
      ByVal tjd_ut As Double, _
      ByVal ifl As Long, _
      ByRef geopos As Double, _
      ByRef attr As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_sol_eclipse_how_d Lib "swedll32.dll" _
    Alias "_swe_sol_eclipse_how_d@20" ( _
      ByRef tjd_ut As Double, _
      ByVal ifl As Long, _
      ByRef geopos As Double, _
      ByRef attr As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_sol_eclipse_when_glob Lib "swedll32.dll" _
    Alias "_swe_sol_eclipse_when_glob@28" ( _
      ByVal tjd_start As Double, _
      ByVal ifl As Long, _
      ByVal ifltype As Long, _
      ByRef tret As Double, _
      ByVal backward As Long, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_sol_eclipse_when_glob_d Lib "swedll32.dll" _
    Alias "_swe_sol_eclipse_when_glob_d@24" ( _
      ByRef tjd_start As Double, _
      ByVal ifl As Long, _
      ByVal ifltype As Long, _
      ByRef tret As Double, _
      ByVal backward As Long, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_sol_eclipse_when_loc Lib "swedll32.dll" _
    Alias "_swe_sol_eclipse_when_loc@32" ( _
      ByVal tjd_start As Double, _
      ByVal ifl As Long, _
      ByRef tret As Double, _
      ByRef attr As Double, _
      ByVal backward As Long, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_sol_eclipse_when_loc_d Lib "swedll32.dll" _
    Alias "_swe_sol_eclipse_when_loc_d@28" ( _
      ByRef tjd_start As Double, _
      ByVal ifl As Long, _
      ByRef tret As Double, _
      ByRef attr As Double, _
      ByVal backward As Long, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_sol_eclipse_where Lib "swedll32.dll" _
    Alias "_swe_sol_eclipse_where@24" ( _
      ByVal tjd_ut As Double, _
      ByVal ifl As Long, _
      ByRef geopos As Double, _
      ByRef attr As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_sol_eclipse_where_d Lib "swedll32.dll" _
    Alias "_swe_sol_eclipse_where_d@20" ( _
      ByRef tjd_ut As Double, _
      ByVal ifl As Long, _
      ByRef geopos As Double, _
      ByRef attr As Double, _
      ByVal serr As String _
    ) As Long

Private Declare Function swe_time_equ Lib "swedll32.dll" _
    Alias "_swe_time_equ@16" ( _
      ByVal tjd_ut As Double, _
      ByRef E As Double, _
      ByRef serr As String _
    ) As Long

Kipp

The vast majority of them ought to stay a Long if not all of them. Names are pretty horrible so impossible to be sure. You'll need to contact the vendor for support if you still have trouble, looks like they are still in business. – Klehm

I thought so too. But it the values don't get computed well. I have no experience when it comes to dealing with changing 32bit data types to 64bit ones. The file is available for download there in the first post. See if you can help about doing something with it. thanks. – Kipp

Thanks for the offer, but no, Venus is in the wrong quadrant. Contact the vendor for help. – Klehm

I found this MSDN article to be very helpful. msdn.microsoft.com/en-us/library/ee691831(v=office.14).aspx – Consistence

Answer 1

I think all you need to do for your function is just add PtrSafe: i.e. the first line of your first function should look like this:

Private Declare PtrSafe Function swe_azalt Lib "swedll32.dll" ......

Randle

This is perfect, thank you! One quick note, I believe certain data types also need to be updated. For instance I think "ms" needs to be updated to "millisecs" – Mho

Answer 2

I'm quite sure you won't get this 32Bit DLL working in Office 64Bit. The DLL needs to be updated by the author to be compatible with 64Bit versions of Office.

The code changes you have found and supplied in the question are used to convert calls to APIs that have already been rewritten for Office 64Bit. (Most Windows APIs have been updated.)

From: http://technet.microsoft.com/en-us/library/ee681792.aspx:

"ActiveX controls and add-in (COM) DLLs (dynamic link libraries) that were written for 32-bit Office will not work in a 64-bit process."

Edit: Further to your comment, I've tried the 64Bit DLL version on Win 8 64Bit with Office 2010 64Bit. Since you are using User Defined Functions called from the Excel worksheet you are not able to see the error thrown by Excel and just end up with the #VALUE returned.

If we create a custom procedure within VBA and try one of the DLL functions we see the exact error thrown. I tried a simple function of swe_day_of_week which just has a time as an input and I get the error Run-time error '48' File not found: swedll32.dll.

Now I have the 64Bit DLL you supplied in the correct locations so it should be found which suggests it has dependencies which cannot be located as per https://mcmap.net/q/375449/-vba-cannot-find-my-dll-despite-hardcoding-location

I've got all the .NET frameworks installed which would be my first guess, so without further information from the author it might be difficult to find the problem.

Edit2: And after a bit more investigating it appears the 64Bit version you have supplied is actually a 32Bit version. Hence the error message on the 64Bit Office. You can check this by trying to access the '64Bit' version in Office 32Bit.

Velmaveloce

I have the 64bit compiled DLL (swedll.dll) as you are suggesting. But it wouldn't work well unless changes are made in the declarations by adding the PtrSafe attribute as well as some data types are modded (without modifying the data types, though the runtime errors are gone when PtrSafe are added to the declaration statements, but the values don't get computed well in the cells). – Kipp

Ok, I thought you were trying the 32Bit version in Office 64Bit. I'll have a look to see if I can get the 64Bit version working. – Velmaveloce

I'm sorry, I forgot to precise this: This link bit.ly/Z8Kqm9 contains the 32Bit version of swedll32.dll + the .xls file. However if you would like to download the 64bit version of the same dll only, it's downloadable here. Note: Both the 64 & 32 bit DLL files have the same names, i.e., swedll32.dll. But their file size are different - the 64bit one is 468KB while the 32bit DLL is 360 KB. – Kipp

Hey, it can get confusing with so many 32 and 64s floating can't it. I understand what you are saying but I believe the 468kb version is not 64Bit since it runs on office 32Bit (try it yourself). Since a 64Bit DLL will not run on Office 32Bit I think this is your problem. – Velmaveloce

@CuberBase you wrote: "Since a 64Bit DLL will not run on Office 32Bit I think this is your problem." I have Office 64Bit. It doesn't work here on my computer, the problem is with the VBA declaration statements, which needs to be modified by adding things such as PtrSafe attribute and some data types needs to be changed such as from Long to LongPtr. But I wonder which ones amongst the whole lot in that MainCalculation Module needs to be changed. – Kipp

The problem you are having at the moment is not due to the declarations. The problem is because the DLL you think is 64Bit is not 64Bit (again, you can check this by specifying it in your workbook and running it in Office 32Bit - it will work). If it was a 64Bit file then the declaration changes you specify would be adequate. – Velmaveloce

you told me earlier: Run-time error '48' File not found: swedll32.dll. Based from the run time error, it seems that it couldn't find the file. Just wanted to ask you, where did you kept that swedll32.dll? It should be in the same folder as the *.xls file and a copy should be in C:\Windows\System32. Thanks for your inputs. – Kipp

I only got the Run-time error when trying the '64Bit' DLL on the 64Bit Office. For all cases, I specified the exact path to the file - you can then place it anywhere. This worked fine for both DLLs on 32Bit Office. I'm guessing that it gives the '48' error because the file is not valid since there are other file not found errors with different numbers. Good luck! – Velmaveloce