excel - 如何通过多行而不是硬代码单元循环 VBA 函数？

Question

我有 3 个功能:

一个将英尺和英寸的高度转换为米。

一个将石头和磅的重量转换为公斤。

一个人通过将体重除以高度的平方来计算BMI。

问题是，我的函数从精确的单元格中读取值并将计算的值放入精确的单元格中，这意味着我不能对下面的每一行重复计算。

看这个截图:



这是我的代码:

Option Explicit

Const KgRate As Double = 0.45359237     'number of kg in one pound
Const PoundsInStone As Integer = 14     'number of pounds in one stone

Const InchesInFeet As Integer = 12      'number of inches in one foot
Const CmsInInch As Double = 2.54        'number of centimetres in an inch

Public weightInKilograms As Double

Public finalHeight As Double

**' FUNCTION 1**
Public Function heightInMetres()

Dim numberOfFeet As Integer
Dim numberOfInches As Integer
Dim heightInInches As Integer
Dim heightInCms As Integer

numberOfFeet = Range("C4").Value
numberOfInches = Range("C5").Value

heightInInches = (numberOfFeet * InchesInFeet) + (numberOfInches)
heightInCms = heightInInches * CmsInInch
finalHeight = heightInCms / 100

Range("C7") = finalHeight

End Function

**' FUNCTION 2**
Public Function weightInKilos()

Dim stonesEntered As Integer
Dim poundsEntered As Double
Dim stonesToPounds As Double

stonesEntered = Range("C10").Value
poundsEntered = Range("D10").Value

stonesToPounds = stonesEntered * PoundsInStone

weightInKilograms = ((stonesToPounds + poundsEntered) * KgRate)

Range("E10") = weightInKilograms

End Function

**' FUNCTION 3**
Public Function calculateBMI()

Dim BMI As Double

BMI = weightInKilograms / (finalHeight ^ 2)

Range("F10") = BMI

End Function

**' MAIN PROCEDURE**
Public Sub BMICalculator()

heightInMetres
weightInKilos
calculateBMI

End Sub

1) 什么是 最简单的如何为第 1 周以下的 3 行(即 Excel 中的第 10 行)重复重量和 BMI 计算？

2)是否可以(简单地)连续运行代码，例如一旦我更新高度和体重单元格，计算就会重做？

3)如果您可以建议对代码进行任何(简单)增强，请执行。 :-)

提前致谢，

皮特

Answer 1

我在这里更改了您的一个功能，如果您可以遵循它，那么转换其他功能应该没有问题:

Public Function heightInMetres(numberOfFeet As Integer, numberOfInches As Integer)

 Dim heightInInches As Integer
 Dim heightInCms As Integer

 heightInInches = (numberOfFeet * InchesInFeet) + (numberOfInches)
 heightInCms = heightInInches * CmsInInch
 finalHeight = heightInCms / 100

 heightInMetres = finalHeight

End Function

所以现在，只需在单元格中输入以下内容，例如。 C7:
=heightinmetres(C4,C5)
C4 中的英尺数和 C5 中的英寸数(在您的示例中)