更新命名范围内的特定值？

I have the following function which retrieves a value from a named range:

我有以下函数从命名范围中检索一个值:

Function getSetting(settingName As String)
    getSetting = Application.VLookup(settingName, Range("rSettings"), 2, True)
End Function

Now I can easily say myColor = getSetting("myColor") and it will return "blue" or whatever.

现在我可以很容易地说myColor = getSetting(“myColor”),它将返回“蓝色”或其他什么。

What I want to do now is update that value.

我现在要做的是更新该值。

Function updateSetting(settingName As String, newValue As String)
    ?????
End Function

Ideas?

2 个解决方案

#1

One you will need to use a sub not a function.

你需要使用一个sub而不是一个函数。

Two you can use the Range.Find method to find the cell. Then use offset to change the value.

您可以使用Range.Find方法找到两个单元格。然后使用offset来更改值。

Sub updateSetting(settingName As String, newValue As String)
    Dim c As Range
    Set c = Range("rSettings").Find(settingName)
    c.Offset(, 1).Value = newValue
End Sub

Also as to your first function, personal preference is not to use worksheet function where vba function can be used. It is slower.

另外,对于您的第一个功能,个人偏好不使用可以使用vba功能的工作表功能。它比较慢。

Function getSetting(settingName As String)
     Dim c As Range
    Set c = Range("rSettings").Find(settingName)
    getSetting = c.Offset(, 1).Value 
End Function

#2

You can determine the location of each settingName in the range using the Match function:

您可以使用匹配功能确定范围中每个settingName的位置:

WorksheetFunction.Match method

Returns the relative position of an item in an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.

返回数组中项的相对位置(数组:用于构建生成多个结果的单个公式或对按行和列排列的一组参数进行操作。数组范围共享一个公共公式;数组常量为一组用作参数的常量。)以指定的顺序匹配指定的值。当您需要某个范围内的项目位置而不是项目本身时,请使用MATCH而不是LOOKUP函数之一。

dim index
index = Match(settingName,Range("settings"),0)

#1