CROSSPOSTED FROM HERE.
Hi everyone. I have a problem with my VBA code.
I have certain functions SUM_DIST and SUM_DISTN that require a range of cells as input. Both are working fine and in order. SUM_DIST has to be ran only once, then SUM_DISTN has to be run thrice, with SUM_DISTN requiring initial input from SUM_DIST, then the next two iterations would be from a previously calculated SUM_DISTN. I can manually get the results by plugging the functions in the spreadsheet.
My problem is when I want to go straight to getting the fourth iteration. What I did is to create another function SUM_DIST_COMP that will run the two functions - running the SUM_DIST function first, then run SUM_DISTN thrice using a for loop. While passing the contents of SUM_DIST_COMP to SUM_DIST was not a problem, I began to have problems in passing the results of SUM_DIST to SUM_DISTN. The code is as follows:
I have tried previous suggestions such as changing the input requirement for the receiving function SUM_DISTN's EG parameter to be Variant, but it still prints out #VALUE ! errors. An updated file is attached in the previous forum I crossposted from (BYREF_ERROR_NEW.xlsm).
Thanks in advance!
Hi everyone. I have a problem with my VBA code.
I have certain functions SUM_DIST and SUM_DISTN that require a range of cells as input. Both are working fine and in order. SUM_DIST has to be ran only once, then SUM_DISTN has to be run thrice, with SUM_DISTN requiring initial input from SUM_DIST, then the next two iterations would be from a previously calculated SUM_DISTN. I can manually get the results by plugging the functions in the spreadsheet.
My problem is when I want to go straight to getting the fourth iteration. What I did is to create another function SUM_DIST_COMP that will run the two functions - running the SUM_DIST function first, then run SUM_DISTN thrice using a for loop. While passing the contents of SUM_DIST_COMP to SUM_DIST was not a problem, I began to have problems in passing the results of SUM_DIST to SUM_DISTN. The code is as follows:
Code:
Option Explicit
Function SUM_DIST(xi As Variant, yi As Variant, zi As Variant, x As Double, y As Double, k As Double)
Dim arrx() As Variant: arrx = xi.Value
Dim arry() As Variant: arry = yi.Value
Dim arrz() As Variant: arrz = zi.Value
Dim di() As Variant
Dim ri() As Variant
Dim i As Integer
Dim i_count As Integer
Dim xn As Double
Dim yn As Double
Dim ri_total As Double
i = UBound(arrx) - LBound(arrx) + 1
xn = x
yn = y
ReDim di(1 To i, 1 To 1)
ReDim ri(1 To i, 1 To 1)
i_count = 1
For i_count = 1 To i
di(i_count, 1) = (arrx(i_count, 1) - xn) ^ 2 + (arry(i_count, 1) - yn) ^ 2 + (arrz(i_count, 1) - k) ^ 2
Next i_count
For i_count = 1 To i
ri(i_count, 1) = Sqr(di(i_count, 1))
Next i_count
ri_total = WorksheetFunction.Sum(ri)
SUM_DIST = ri_total
End Function
Function SUM_DISTN(xi As Variant, yi As Variant, zi As Variant, EG As Variant, x As Double, y As Double, k As Double)
Dim arrx() As Variant: arrx = xi.Value
Dim arry() As Variant: arry = yi.Value
Dim arrz() As Variant: arrz = zi.Value
Dim arrEG() As Variant: arrEG = EG.Value
Dim di() As Variant
Dim ri() As Variant
Dim ni() As Variant
Dim i As Integer
Dim i_count As Integer
Dim xn As Double
Dim yn As Double
Dim ri_total As Double
i = UBound(arrx) - LBound(arrx) + 1
xn = x
yn = y
ReDim di(1 To i, 1 To 1)
ReDim ri(1 To i, 1 To 1)
ReDim ni(1 To i, 1 To 1)
i_count = 1
For i_count = 1 To i
di(i_count, 1) = (arrx(i_count, 1) - xn) ^ 2 + (arry(i_count, 1) - yn) ^ 2 + (arrz(i_count, 1) - k) ^ 2
Next i_count
For i_count = 1 To i
ri(i_count, 1) = Sqr(di(i_count, 1))
Next i_count
For i_count = 1 To i
ni(i_count, 1) = Abs((ri(i_count, 1) - arrEG(i_count, 1)) / arrEG(i_count, 1))
Next i_count
SUM_DISTN = WorksheetFunction.Sum(ni)
End Function
Function SUM_DIST_COMP(xi As Variant, yi As Variant, zi As Variant)
Dim arrx() As Variant: arrx = xi.Value
Dim arry() As Variant: arry = yi.Value
Dim arrz() As Variant: arrz = zi.Value
Dim EG_i As Variant
Dim EG_i_prev As Variant
Dim i_count As Integer
Dim i As Integer
Dim iter_SUM_DIST As Integer
Dim iter_SUM_DIST_limit As Integer
Dim xi_val As Double
Dim yi_val As Double
i_count = 1
iter_SUM_DIST = 3 'Repeat 3 times to get SUM_DIST_N4
i = UBound(arrx) - LBound(arrx) + 1
iter_SUM_DIST_limit = 5
ReDim EG_i(1 To i, 1 To 1)
ReDim EG_i_prev(1 To i, 1 To 1)
'Run initial iteration for SUM_DIST_N1
For i_count = 1 To i
xi_val = xi(i_count, 1)
yi_val = yi(i_count, 1)
EG_i(i_count, 1) = SUM_DIST(arrx, arry, arrz, xi_val, yi_val, 0.1)
Next i_count
For iter_SUM_DIST = 1 To iter_SUM_DIST_limit
EG_i_prev = EG_i
For i_count = 1 To i
xi_val = xi(i_count, 1)
yi_val = yi(i_count, 1)
EG_i(i_count, 1) = SUM_DISTN(xi, yi, zi, EG_i_prev, xi_val, yi_val, 0.1) ' <<<< THIS IS THE PROBLEMATIC LINE
Next i_count
Next iter_SUM_DIST
SUM_DIST_COMP = EG_i
End FunctionI have tried previous suggestions such as changing the input requirement for the receiving function SUM_DISTN's EG parameter to be Variant, but it still prints out #VALUE ! errors. An updated file is attached in the previous forum I crossposted from (BYREF_ERROR_NEW.xlsm).
Thanks in advance!
