Getting different calculations in Excel VBA than Formula Calculation in the Cells

[mmadani]

'Declarations

Private Sub CommandButton2_Click()
Dim inputValue As Double
Dim unitType As String
Dim result As Double
Dim UserResponse As Long
Dim nconntors As Double
Dim nsplices As Double
Dim ConnectLoss As Double
Dim SpliceLoss As Double
Dim MaxAttn As Double

result = 0
nconntors = 0
nsplices = 0
MaxAttn = 0
AttnCoeff = 0

Would like to do this in a From to input in the underlying Sheet, not too sure how to do it in Excel. I know how in Access

inputValue = Application.InputBox("Enter the distance value to convert:", "Input Value", Type:=1)


' Prompt user to select the unit type
unitType = InputBox("Enter the unit type in LCase (meters, feet, or kilometers):", "Unit Selection")

nconntors = Application.InputBox("Enter the Number of Connectors", "Input Value", Type:=1)
ConnectLoss = Application.InputBox("Enter TIA/EIA Connector Loss:", "Input Value", Type:=1)
nsplices = Application.InputBox("Enter the Number of Splices:", "Input Value", Type:=1)
SpliceLoss = Application.InputBox("Enter TIA/EIA Splice loss per Splice:", "Input Value", Type:=1)
AttnCoeff = Application.InputBox("Enter the Attenuation Coefficient:", "Input Value", Type:=1)


calc:s here

Select Case LCase(unitType)

Case "meters"

'result = inputValue / 1000 ' Convert meters to Kilometers

' result = Application.Convert(inputValue, "m", "km")

result = Application.WorksheetFunction.Convert(inputValue, "m", "km")


Case "feet"
'result = inputValue * 0.000304801 ' Convert feet to Kilometers

'result = Application.Convert(inputValue, "ft", "km")

result = Application.WorksheetFunction.Convert(inputValue, "ft", "km")

Case "kilometers"

result = inputValue ' Already in kilometers

'result = Application.Convert(inputValue, "km", "km")

result = Application.WorksheetFunction.Convert(inputValue, "km", "km")

MsgBox "Excuse Me Doc !...The Distance is Already in Kilometers : " & result, vbInformation, "Conversion Result"

MsgBox "I am Done !", vbExclamation

GoTo AfterSelect ' Exit the Select Case block


Doing a Display in a message box of the reults

MaxAttn = (nconntors * ConnectLoss) + (nsplices * SpliceLoss) + (AttnCoeff * result)

Unload UserForm1

MsgBox "The distance in kilometers is: " & result & vbCrLf & _
"The Maximum Insertion Loss (Attenuation) is: " & MaxAttn & " dB/km" & vbCrLf & _
"The Attenuation coefficient is: " & AttnCoeff & vbCrLf & _
"The Connector loss is: " & ConnectLoss * 2 & vbCrLf & _
"The Splice Loss is: " & SpliceLoss * nsplices, vbInformation, "Maximum dB/Km Loss"


Basically converting a value from Feet to Kilometers
Adding Number of splices * Splice loss factor
Adding Number of connectors * Connector loss Factor

Summing all up to get Attenuation Value (which is MaxAttn)

first 3 to 4v digits after the floating point are okay then they are off, I don't think it is rounding, but my head is tired


Thanks for your time

 

[arnelgp]

then use only 1 calculation and adapt it(I am thinking you have calculation on userform and in the worksheet).

 

[The_Doc_Man]

Part of the problem is that Excel cells actually don't have a data type. They have a format. VBA variables have an intrinsic type AND can have a very different format. If your cell computation doesn't include a typecast function (i.e INT(x) to cast the type as INTEGER) then I believe it is always either DOUBLE or a string expression that can evaluate as DOUBLE. If you use VBA with explicitly typed variables and the type is other than DOUBLE, you could indeed get different results than the cell formula.

 

[mmadani]

then use only 1 calculation and adapt it(I am thinking you have calculation on userform and in the worksheet).

Hello
I don't have a form in Excel . That's why I was using input fields. I could use an excel form if I have a sample. or I can go back to access, but it is a long shot as my client uses excel. Thanks for your time

 

[mmadani]

Part of the problem is that Excel cells actually don't have a data type. They have a format. VBA variables have an intrinsic type AND can have a very different format. If your cell computation doesn't include a typecast function (i.e INT(x) to cast the type as INTEGER) then I believe it is always either DOUBLE or a string expression that can evaluate as DOUBLE. If you use VBA with explicitly typed variables and the type is other than DOUBLE, you could indeed get different results than the cell formula.

Thanks Doc
So is there a work around ? Or do I have to start over?

 

[The_Doc_Man]

Stick with one method or the other, but mixing both computational methods will lead to confusion and rounding errors. As to which one is more accurate, Excel always uses IEEE 754 as its numeric standard, which is 15 digits of precision. (I looked that up after my previous post.) This is the same as VBA's DOUBLE variables. Therefore, understand that if you switch to VBA and the variable is NOT a DOUBLE, discrepancies can creep in. If your VBA formulas use only DOUBLE variable then the cell value and the variable value should track correctly. But any function or typecast on either side of that issue can cause them to diverge, and that's why I suggest sticking with one and only one computational method.