我一直在编写一些宏来执行一些占星计算(计算星座、月亮宫、D9 和 D60)。原始数据的格式如下:
上图中的lng代表经度,以度、分、秒的格式表示。输出必须采用以下格式:
我编写了以下代码来从输入表中读取数据并格式化并将其复制到输出表中,然后使用每个行星的经度进行计算以计算所需的字段。
Sub prepareOutput()
Application.ScreenUpdating = False
Dim c, count, d, l, ll
Dim r As Range
Set r = Worksheets("Ephemerides").Range("a4:" & Worksheets("Ephemerides").Range("a4").End(xlDown).Address)
Worksheets("output").Range("a3").Value = "Date"
For Each d In r
Worksheets("output").Cells(d.Row, 1).Value = d.Value
Next
For Each c In Worksheets("Ephemerides").Range("d2:o2")
If Not IsEmpty(c) Then
count = count + 5
'MsgBox count
If count = 5 Then
Worksheets("output").Cells(2, 2).Value = c.Value
Worksheets("output").Cells(3, 2).Value = "Longitude"
Worksheets("output").Cells(3, 3).Value = "Sign"
Worksheets("output").Cells(3, 4).Value = "Nakshatra"
Worksheets("output").Cells(3, 5).Value = "Navamsa"
Worksheets("output").Cells(3, 6).Value = "D60"
For Each l In Worksheets("Ephemerides").Range(c.Offset(2, 0), c.End(xlDown).Address)
Worksheets("output").Cells(l.Row, 2).Value = l.Value
Worksheets("output").Cells(l.Row, 3).Value = calcSign(l.Value)
Next
count = 2
Else
Worksheets("output").Cells(2, count).Value = c.Value
Worksheets("output").Cells(3, count).Value = "Longitude"
Worksheets("output").Cells(3, count + 1).Value = "Sign"
Worksheets("output").Cells(3, count + 2).Value = "Nakshatra"
Worksheets("output").Cells(3, count + 3).Value = "Navamsa"
Worksheets("output").Cells(3, count + 4).Value = "D60"
For Each ll In Worksheets("Ephemerides").Range(c.Offset(2, 0), c.End(xlDown).Address)
Worksheets("output").Cells(ll.Row, count).Value = ll.Value
Worksheets("output").Cells(ll.Row, count + 1).Value = calcSign(ll.Value)
Next
End If
End If
Next
Application.ScreenUpdating = True
End Sub
Private Function deg2dec(deg As String) As Variant
d = Val(Mid(deg, 1, InStr(deg, "°") - 1))
m = Val(Mid(deg, InStr(deg, "°") + 1, 2)) / 100
deg2dec = d + m
End Function
Private Function calcSign(deg As String) As String
dec = deg2dec(deg)
Select Case dec
Case 0 To 30
calcSign = "Aries"
Case 30 To 60
calcSign = "Taurus"
Case 60 To 90
calcSign = "Gemini"
Case 90 To 120
calcSign = "Cancer"
Case 120 To 150
calcSign = "Leo"
Case 150 To 180
calcSign = "Virgo"
Case 180 To 210
calcSign = "Libra"
Case 210 To 240
calcSign = "Scorpio"
Case 240 To 270
calcSign = "Saggitarius"
Case 270 To 300
calcSign = "Capricorn"
Case 300 To 330
calcSign = "Aquarius"
Case 330 To 360
calcSign = "Pisces"
End Select
End Function
上面的代码不会计算所有 4 个计算字段,目前仅计算一个。
我遇到的问题是我的输入表中有 24000 行和 12 列,将这些数据复制到输出表然后对其进行计算以计算另一个值需要花费大量时间。从一个经度值计算另外 3 个字段。
因此,如果你们可以看一下代码并让我知道如何最大限度地减少这里的运行时间,那将会有很大帮助。
如果有人想看的话,这是该工作簿的链接。星图xlsm https://drive.google.com/file/d/0B8OgQf1g9iFqWXVUU001ZFNKVWM/edit?usp=sharing
预先感谢所有抽出时间回复的人。
Cheers