Gruppe
DatumZeit
Problem
Wie kann ich Dezimalzahlen in Uhrzeiten und Uhrzeiten in Dezimalzahlen umwandeln und addieren?
StandardModule: Modul1
Sub LinkInfo()
Dim arrDetails As Variant
Dim rng As Range, rngSel As Range
Dim iCounter As Integer, iRow As Integer, sText As String
Set rngSel = Range("A1:E9")
Workbooks.Add 1
Range("A1").Value = "LinkAddress:"
Range("B1").Value = "Path:"
Range("C1").Value = "Workbook:"
Range("D1").Value = "Worksheet:"
Range("E1").Value = "Range:"
Range("A1:E1").Font.Bold = True
iRow = 1
For Each rng In rngSel
If rng.HasFormula Then
If InStr(rng.Formula, "\[") Then
arrDetails = GetDetails(rng.Formula)
iRow = iRow + 1
Cells(iRow, 1).Value = rng.Address
For iCounter = 1 To 4
Cells(iRow, iCounter + 1).Value = arrDetails(iCounter)
Next iCounter
End If
End If
Next rng
Columns.AutoFit
End Sub
Private Function GetDetails(sTxt As String) As Variant
Dim sWkb As String, sWks As String, sRng As String
Dim sPath As String
Dim arr(1 To 4) As String
arr(1) = Mid(sTxt, InStr(sTxt, "'") + 1, _
InStr(sTxt, "[") - InStr(sTxt, "'") - 2)
arr(2) = Mid(sTxt, InStr(sTxt, "[") + 1, _
InStr(sTxt, "]") - InStr(sTxt, "[") - 1)
arr(3) = Mid(sTxt, InStr(sTxt, "]") + 1, _
InStr(sTxt, "'!") - InStr(sTxt, "]") - 1)
arr(4) = Right(sTxt, Len(sTxt) - InStr(sTxt, "!"))
GetDetails = arr
End Function