3-dimensional grid search

[Sunshine]

Hi!
I'm supposed to create a vba script that search for a specific combination of three values. The values I'm searching for should be in the range between 0,0 and 1,0 within 0,1 intervals (estimating 11*11*11 different combinations) and should minimize two cells with functions that already are present in one of my other worksheets. I have no idea how to get started, I've not even been able to create a 3-dimensional grid after hours of googling. Please help me?! :bow:

[Paul_Hossler]

I'd use a 3 dimensional array (which has to use integer indices) to model the 3D grid intervals

Little bit of math to translate a [0,1] number to the correct 3D coordinates

Not really tested


should minimize two cells with functions that already are present in one of my other worksheets

Can't help you there




Option Explicit
Sub ThreeDimGrid()
Dim X As Long, Y As Long, Z As Long
Dim CX As Long, CY As Long, CZ As Long

' 0.0-1.0 0.0-1.0 0.0-1.0
Dim aGrid(0 To 10, 0 To 10, 0 To 10) As Double

For X = LBound(aGrid, 1) To UBound(aGrid, 1)
For Y = LBound(aGrid, 2) To UBound(aGrid, 2)
For Z = LBound(aGrid, 3) To UBound(aGrid, 3)
aGrid(X, Y, Z) = Rnd
Next Z
Next Y
Next X

CX = pvtFindIntervalIndex(0.05)
CY = pvtFindIntervalIndex(0.55)
CZ = pvtFindIntervalIndex(0.95)

MsgBox CX & " --- " & CY & " --- " & CZ
MsgBox aGrid(CX, CY, CZ)

End Sub

Private Function pvtFindIntervalIndex(n As Double) As Long
pvtFindIntervalIndex = Application.WorksheetFunction.RoundDown(10# * n, 0)
End Function