Use VBA in Excel to convert degrees, minutes, seconds, and decimal degrees

found that the VBA function of Excel is a sharp blade of batch processing, working small and medium-sized test sledgehammer, will excel in the form of minutes and seconds of the coordinate batch processing into 10 system form, after processing for GIS exhibition point mapping.

The original Excel data is as follows:

The VBA code is as follows:

1 SubCoorder ()2     DimLongitude, Latitude as String, arr3     DimNum as Integer4     DimLongcolidx as Integer5     DimLatcolidx as Integer6Longcolidx =37Latcolidx =88num = Application.counta (ActiveSheet.Range ("a:a"))9      fori =2  toNumTenLongitude =Cells (i, LONGCOLIDX) Onearr =Split(Longitude,"°") ACells (i, Longcolidx +1) = Arr (0) -Longitude = arr (1) -arr =Split(Longitude,"'") theCells (i, Longcolidx +2) = Arr (0) -Longitude = arr (1) -arr =Split(Longitude,"""") -Cells (i, Longcolidx +3) = Arr (0) +Cells (i, Longcolidx +4) = Cells (i, Longcolidx +1) + Cells (i, Longcolidx +2) / -+ Cells (i, Longcolidx +3) /3600 -          +Latitude =Cells (i, LATCOLIDX) Aarr =Split(Latitude,"°") atCells (i, Latcolidx +1) = Arr (0) -Longitude = arr (1) -arr =Split(Longitude,"'") -Cells (i, Latcolidx +2) = Arr (0) -Longitude = arr (1) -arr =Split(Longitude,"""") inCells (i, Latcolidx +3) = Arr (0) -Cells (i, Latcolidx +4) = Cells (i, Latcolidx +1) + Cells (i, Latcolidx +2) / -+ Cells (i, Latcolidx +3) /3600 to     NextI + End Sub

The results are processed as follows:

Conclusion:

1, for Excel large-scale data processing, very high efficiency.

① development environment, no need to build a specific development environment, in Excel can be encoded;

② coding, because it is directly parasitic in Excel itself, the equivalent of an embedded API, its API interface is simple, high integration, easy to invoke;

2, the above code can actually play an extra role, that is, the original degree of data in the text format to do quality inspection.

Attached: Using VBA to get the number of rows and columns in an Excel table

Use VBA in Excel to convert degrees, minutes, seconds, and decimal degrees