时间戳

首页 > 解决方案 > 有没有一种有效的方法来结合大量的替换和替换？

excel - 有没有一种有效的方法来结合大量的替换和替换？

问题描述

我有很多用代码编写的数据，我需要使其更具可读性。为此，我在vba中使用了replace，特别是因为有很多可能的代码需要删除或替换为不同的符号，我仍然需要添加更多可能的文本。

以下 UDF 有效，但每当我发现代码中可能有新的部分需要替换时，我必须编写另一个完整的 'Let#' 来弥补。我觉得有更好的方法来编写这个函数，所以我想知道是否有人可以帮忙。

Function Unit(Req_Header As String) As String

Dim Func As String
Dim Amount As String

Func = AkeFind(Req_Header)     'AkeFind is a different UDF that looks up the correct string in a dataset (and returns as a string)

Dim LetA As String
Dim LetB As String
Dim LetC As String
Dim LetD As String
Dim LetE As String
Dim LetF As String

LetA = Replace(Func, "formaat_papier_", "")
LetB = Replace(LetA, "motief_", "")
LetC = Replace(LetB, "materiaal_", "")
LetD = Replace(LetC, "vorm_", "")
LetE = Replace(LetD, "_", " ")
LetF = Replace(LetE, "•", "-")

Amount = LetF

    Select Case Amount
    Case "CENTIMETER", "Centimeter", "centimeter"
        Unit = "cm"
    Case "MILLIMETER", "Millimeter", "millimeter"
        Unit = "mm"
    Case "METER", "Meter", "meter"
        Unit = "m"
    Case "CENTILITER", "Centiliter", "centiliter"
        Unit = "cl"
    Case "MILLILITER", "Milliliter", "milliliter"
        Unit = "ml"
    Case "LITER", "Liter", "liter"
        Unit = "l"
    Case "KILOGRAM", "Kilogram", "kilogram"
        Unit = "kg"
    Case "MILLIGRAM", "Milligram", "milligram"
        Unit = "mg"
    Case "GRAM", "Gram", "gram"
        Unit = "g"
    Case "PIECE", "Piece", "piece"
        Unit = "Stuks"
    Case "plastic", "kunstof"
        Unit = "Kunststof"
    Case "metaal", "staal"
        Unit = "Metaal"
    Case Else
        Unit = Amount
    End Select

End Function

标签： excelvbareplacecaseuser-defined-functions

解决方案

你可以使用这些方面的东西。需要对新单位进行维护，所以我也可能会采用表格方法。可能最好在从 aReplace 获取之前分离出函数的匹配部分并测试错误。

Function GetReplacementUnits(strInputUnit As String)

Dim aOrig() As Variant
Dim aReplace() As Variant

aOrig = Array("CENTIMETRE", "KILOGRAM", "METER")
aReplace = Array("cm", "Kg", "m")

GetReplacementUnits = Application.Index(aReplace, Application.Match(UCase(strInputUnit), aOrig, 0), 1)

End Function

像这样使用

GetReplacementUnits("CENTIMETRE")返回厘米

推荐阅读