|
对一个EXCEL使用的新手而言，想通过它复杂的函数功能来简化工作量是一件费力的事情。本文作者在运用函数功能时，遇到了很多问题，可能对于新手而言有 较好的借鉴作用。因为问题很多，这里仅仅以格式转化（十六进制、十进制、八进制和二进制）作为论述的方面，其他问题也将于日后逐一讨论分析。 对于进制之间的转化功能，EXCEL已经提供了HEX2DEC,DEC2HEX等一些简单的宏，它们能够通过加载“分析工具库”来获取，但是遗憾的是，这 些相当漂亮的宏不能在VBA中被调用。在很多日常的运用中，它们仅仅是作为函数的一个部分而存在，比如说，将每个域中的全部十六进制代码异或，它需要 HEX2DEC等能够在VBA中被调用，然而...。我也使用通过类似于Rang(i,j).Formula＝"=HEX2DEC(""A6"")"来使 用，但是它返回的仅仅是公式字符串而非结果（因为是新手，所以也没有搞懂如何进一步处理才能成功。如果某位网友是高手，请指教）。在万般无奈之下，只能自 己动手来实现这些功能。因为以前对于VBA的语法一窍不通，所以在VBA的帮助，写了一段实现代码，我想肯定很多地方是能优化，但是现在只能是将就将就 了:-(. Public Function WYQConvert(srcData As String, srcBase As Integer, dstBase As Integer) As String Dim idx As Long Dim length As Long Dim tmp As Long Dim value As Long Dim s As String Dim rs As String 'Check whether the formats are same between source and destination If srcBase = dstBase Then WYQConvert = srcData Exit Function End If 'Check whether the base of destination is valid If dstBase < 2 Then WYQConvert = Null Exit Function End If 'Convert String to Long in the light of decimal system length = Len(srcData) value = 0 For idx = 1 To length s = Mid(srcData, idx, 1) Select Case s Case "0" To "9" tmp = CLng(s) Case Is = "A" tmp = 10 Case Is = "a" tmp = 10 Case Is = "B" tmp = 11 Case Is = "b" tmp = 11 Case Is = "C" tmp = 12 Case Is = "c" tmp = 12 Case Is = "D" tmp = 13 Case Is = "d" tmp = 13 Case Is = "E" tmp = 14 Case Is = "e" tmp = 14 Case Is = "F" tmp = 15 Case Is = "f" tmp = 15 End Select value = value * srcBase + tmp Next 'MsgBox "Value = " & value 'Convert Long to String in the light of requesting base rs = "" While value > 0 tmp = value Mod dstBase value = value / dstBase Select Case tmp Case 0 To 9 s = "" & tmp 'Trim(str(tmp)) Case Is = 10 s = "A" Case Is = 11 s = "B" Case Is = 12 s = "C" Case Is = 13 s = "D" Case Is = 14 s = "E" Case Is = 15 s = "F" End Select rs = s & rs Wend 'Check whether value equals zero to assign the proper value If rs = "" Then rs = "0" End If 'MsgBox "Result = " & rs WYQConvert = rs End Function 这个函数能够方便的实现16，10，8，2进制之间的转化，比如： 16->10:WYQConvert( "A6", 16, 10) 16->10:WYQConvert( "A6", 16, 8) 10->16:WYQConvert("166", 10, 16) 上面的方法仅仅是一个初学者的权宜之计，对于EXCEL的使用者而言，最优雅的做法是通过它提供的现有函数的组合来完成自己的功能而非是每次都顷力顷为，你说呢？ |
|
|
