我该如何编码更好的方法,而不是像这样的VBA编码 [英] How do I code better way instead of coding like this VBA
本文介绍了我该如何编码更好的方法,而不是像这样的VBA编码的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在用excel创建仪表板,但是我想知道是否有比这更好的编码方法.我想对其进行模块化,而不是使它更简洁.
I am creating a dash board in excel but however i would like to know if there is a better way of coding rather than this. I would like to moduleriz it instead of doing this to make it more neat.
Private Sub Afford()
If af.Value = True Then
af = afr.Value
Sheet1.Cells(35, "C").Value = Sheet1.Cells(57, "C").Value
Sheet1.Cells(35, "D").Value = WorksheetFunction.Round((Sheet1.Cells(35, "D").Value * 1.3), 0)
Sheet1.Cells(35, "E").Value = WorksheetFunction.Round((Sheet1.Cells(35, "E").Value * 1.02), 0)
Sheet1.Cells(35, "F").Value = WorksheetFunction.Round((Sheet1.Cells(35, "F").Value * 1.9), 0)
Sheet1.Cells(35, "G").Value = WorksheetFunction.Round((Sheet1.Cells(35, "G").Value * 1.9), 0)
Sheet1.Cells(35, "H").Value = WorksheetFunction.Round((Sheet1.Cells(35, "H").Value * 1.5), 0)
推荐答案
好吧,仅基于您的特定代码,您可以尝试以下方法:
Well, just purely based on this particular piece of code of yours, you could try something along the following lines:
Option Explicit
Sub Sample()
Dim rng As Range
Dim x As Long
Dim arr As Variant
With Sheet1
'Input static values
.Cells(35, "C").Value = .[C57]
'Prepare for dynamic values
Set rng = .Range("D35:H35")
arr = Array(1.3, 1.02, 1.9, 1.9, 1.5)
'Insert dynamic values
For x = 1 To rng.Cells.Count
rng.Cells(1).Value = WorksheetFunction.Round(rng.Cells(1).Value * arr(x - 1), 0)
Next x
End With
End Sub
或者,如果您的串联字符串不会超过255个字符,则
Or, if your concatenated string won't go over 255 characters:
Sub Sample()
Dim str As String
With Sheet1
'Input static values
.Cells(35, "C").Value = .[C57]
'Prepare for dynamic values
str = "{1.3,1.02,1.9,1.9,1.5}"
'Insert dynamic values
.Range("D35:H35").Value = .Evaluate("D35:H35*" & str)
End With
End Sub
这篇关于我该如何编码更好的方法,而不是像这样的VBA编码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文