vba 将字符串写入excel中的单元格

Writing a string to a cell in excel

提问by knightrider

I am trying to write a value to the "A1" cell, but am getting the following error:

我正在尝试向“A1”单元格写入一个值，但出现以下错误：

Application-defined or object-defined error '1004'

应用程序定义或对象定义的错误“1004”

I have tried many solutions on the net, but none are working. I am using excel 2007 and the file extensiton is .xlsm.

我在网上尝试了很多解决方案，但都没有奏效。我使用的是 excel 2007，文件扩展名为 .xlsm。

My code is as follows:

我的代码如下：

Sub varchanger()
On Error GoTo Whoa
Dim TxtRng  As Range

Worksheets("Game").Activate
ActiveSheet.Unprotect

Set TxtRng = ActiveWorkbook.Sheets("Game").Cells(1, 1)
TxtRng.Value = "SubTotal"

'Worksheets("Game").Range("A1") = "Asdf"

LetsContinue:
    Exit Sub
Whoa:
    MsgBox Err.number
    Resume LetsContinue
End Sub

Edit: After I get error if I click the caution icon and then select show calculation steps its working properly

编辑：如果我点击警告图标，然后选择显示计算步骤，它会正常工作，出现错误后

回答by Jon Crowell

I think you may be getting tripped up on the sheet protection. I streamlined your code a little and am explicitly setting references to the workbook and worksheet objects. In your example, you explicitly refer to the workbook and sheet when you're setting the TxtRng object, but not when you unprotect the sheet.

我想你可能会被床单保护绊倒。我稍微简化了您的代码，并明确设置了对工作簿和工作表对象的引用。在您的示例中，您在设置 TxtRng 对象时显式引用工作簿和工作表，但在取消保护工作表时则不会。

Try this:

尝试这个：

Sub varchanger()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim TxtRng  As Range

    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Sheet1")
    'or ws.Unprotect Password:="yourpass"
    ws.Unprotect

    Set TxtRng = ws.Range("A1")
    TxtRng.Value = "SubTotal"
    'http://stackoverflow.com/questions/8253776/worksheet-protection-set-using-ws-protect-but-doesnt-unprotect-using-the-menu
    ' or ws.Protect Password:="yourpass"
    ws.Protect

End Sub

If I run the sub with ws.Unprotectcommented out, I get a run-time error 1004. (Assuming I've protected the sheet and have the range locked.) Uncommenting the line allows the code to run fine.

如果我在ws.Unprotect注释掉的情况下运行 sub ，我会得到一个运行时错误 1004。（假设我已经保护了工作表并锁定了范围。）取消注释该行允许代码正常运行。

NOTES:

笔记：

1. I'm re-setting sheet protection after writing to the range. I'm assuming you want to do this if you had the sheet protected in the first place. If you are re-setting protection later after further processing, you'll need to remove that line.
2. I removed the error handler. The Excel error message gives you a lot more detail than Err.number. You can put it back in once you get your code working and display whatever you want. Obviously you can use Err.Description as well.
3. The Cells(1, 1)notation can cause a huge amount of grief. Be careful using it. Range("A1")is a lot easier for humans to parse and tends to prevent forehead-slapping mistakes.

1. 写入范围后，我正在重新设置工作表保护。如果您首先保护了工作表，我假设您想这样做。如果您稍后在进一步处理后重新设置保护，则需要删除该行。
2. 我删除了错误处理程序。Excel 错误消息为您提供了比 Err.number 更详细的信息。一旦您的代码工作并显示您想要的任何内容，您就可以将其放回原处。显然，您也可以使用 Err.Description。
3. 这种Cells(1, 1)符号会引起巨大的悲伤。小心使用它。Range("A1")对人类来说更容易解析，并且往往可以防止拍额头的错误。

回答by UberNubIsTrue

I've had a few cranberry-vodkas tonight so I might be missing something...Is setting the range necessary? Why not use:

今晚我喝了几杯蔓越莓伏特加，所以我可能会遗漏一些东西......是否需要设置范围？为什么不使用：

Activeworkbook.Sheets("Game").Range("A1").value = "Subtotal"

Does this fail as well?

这也会失败吗？

Looks like you tried something similar:

看起来你尝试过类似的东西：

'Worksheets("Game").Range("A1") = "Asdf"

However, Worksheets is a collection, so you can't reference "Game". I think you need to use the Sheets object instead.

但是，Worksheets 是一个集合，因此您不能引用“游戏”。我认为您需要改用 Sheets 对象。

回答by T. I. Troll

replace Range("A1") = "Asdf" with Range("A1").value = "Asdf"

用 Range("A1").value = "Asdf" 替换 Range("A1") = "Asdf"

回答by whytheq

try this instead

试试这个

Set TxtRng = ActiveWorkbook.Sheets("Game").Range("A1")

Set TxtRng = ActiveWorkbook.Sheets("Game").Range("A1")

ADDITION

添加

Maybe the file is corrupt - this has happened to me several times before and the only solution is to copy everything out into a new file.

也许文件已损坏 - 这在我身上发生过几次，唯一的解决方案是将所有内容复制到一个新文件中。

Please can you try the following:

请您可以尝试以下操作：

• Save a new xlsm file and call it "MyFullyQualified.xlsm"
• Add a sheet with no protection and call it "mySheet"
• Add a module to the workbook and add the following procedure

• 保存一个新的 xlsm 文件并将其命名为“MyFullyQualified.xlsm”
• 添加没有保护的工作表并将其命名为“mySheet”
• 将模块添加到工作簿并添加以下过程

Does this run?

这运行吗？

 Sub varchanger()

 With Excel.Application
    .ScreenUpdating = True
    .Calculation = Excel.xlCalculationAutomatic
    .EnableEvents = True
 End With

 On Error GoTo Whoa:

    Dim myBook As Excel.Workbook
    Dim mySheet As Excel.Worksheet
    Dim Rng  As Excel.Range

    Set myBook = Excel.Workbooks("MyFullyQualified.xlsm")
    Set mySheet = myBook.Worksheets("mySheet")
    Set Rng = mySheet.Range("A1")

    'ActiveSheet.Unprotect


    Rng.Value = "SubTotal"

    Excel.Workbooks("MyFullyQualified.xlsm").Worksheets("mySheet").Range("A1").Value = "Asdf"

LetsContinue:
        Exit Sub
Whoa:
        MsgBox Err.Number
        GoTo LetsContinue

End Sub