Stores as array but not as variable

V

VeeBeeIntern

New Member
Joined
Jun 19, 2014
Messages
6
I have the following snippet of code which matches values from two arrays that are not indexed the same:

Code: 
For i = 2 To NumTrades
                
                For j = 0 To 2 * TheRange
                
                    If Abs(ActCDF(i, 1) - TheCDF(j, 0)) < 0.001 Then
                        TheCDF(j, 3) = Abs(ActCDF(i, 2) - TheCDF(j, 2))
                        TheCDF(j, 4) = Abs(ActCDF(i - 1, 2) - TheCDF(j, 2))
                    End If
                                           
                Next j
            Next i

This enters the values into the array TheCDF. However, if I wish to store the values as variables (called "Diff1" and "Diff2") and then enter these values into the corresponding array locations it returns zeros. How does this make any sense? Any help is greatly appreciated
 
Sort by date Sort by votes
The whole code? Parts have been commented out because of this issue I'm having, but the functional bits are in there

Code: 
Sub anything()


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
StartTime = Timer


Dim LoLoc As Single, UpLoc As Single, LoScale As Single, UpScale As Single
Dim LoSkew As Single, UpSkew As Single, LoKurt As Single, UpKurt As Single
Dim TheRange As Single
Dim Diff1 As Integer, Diff2 As Integer


Dim ActCDF() As Variant
Dim TheCDF(2000, 5) As Single
Dim RunSum As Double, nsum As Double
Dim x As Single, D As Single
Dim i As Integer, a As Integer, j As Integer


'Define range of variables to search in
ActCDF = Worksheets("Distribution Generator").Range("e3:f602").Value
LoLoc = Range("J5").Value:    HiLoc = Range("K5").Value
LoScale = Range("J6").Value:  HiScale = Range("K6").Value
LoSkew = Range("J7").Value:   HiSkew = Range("K7").Value
LoKurt = Range("J8").Value:   HiKurt = Range("K8").Value
NumTrades = Range("I10").Value
inc = Range("I11").Value
TheRange = Round(Range("I12").Value, 2) * 100


BestD = 1
lok = -0.35
Skale = 2.44
Kurt = 3
Skew = 0.45




'For l = LoLoc / inc To HiLoc / inc
'
'    lok = l * inc + 0.0001
'
'    For s = LoScale / inc To HiScale / inc
'
'    Skale = s * inc + 0.0001
'
'        For k = LoKurt / inc To HiKurt / inc
'
'        Kurt = k * inc + 0.0001
'
        
        


            For i = 0 To 2 * TheRange
                x = -TheRange / 100 + i / 100
                TheCDF(i, 0) = x
                signX = x / Abs(x + 0.00001)
                SignSkew = Skew / Abs(Skew)
                C = (1 + (Abs(Skew) ^ Abs(1 / (x + 0.0000001) - lok)) * signX * -SignSkew) ^ 0.5
                Y = (1 / (Abs((x + 0.0000001 - lok) * Skale) ^ Kurt + 1)) ^ C
                TheCDF(i, 1) = Y


            Next i
            
            RunSum1 = 0
            RunSum2 = 0
            nsum = 0
            D = 0
            Sig = 0


            'Calculate area under entire CDF
            For a = 0 To TheRange * 2
                nsum = nsum + TheCDF(a, 1)
            Next a
'
            'Calculate area up to each x value; Find Maximum D value
            For i = 1 To TheRange * 2


                RunSum1 = RunSum1 + TheCDF(i - 1, 1)
                RunSum2 = RunSum1 + TheCDF(i, 1)
                PercentofCFD = (RunSum1 + RunSum2) / 2 / nsum
                TheCDF(i, 2) = PercentofCFD
                
            Next i
            
            For i = 2 To NumTrades
                
                For j = 0 To 2 * TheRange
                
                    If Abs(ActCDF(i, 1) - TheCDF(j, 0)) < 0.001 Then
                        TheCDF(j, 3) = Abs(ActCDF(i, 2) - TheCDF(j, 2))
                        TheCDF(j, 4) = Abs(ActCDF(i - 1, 2) - TheCDF(j, 2))
                    End If
                                           
                Next j
            Next i
'
'            For j = 1 To 200
'
'                Sigg = ((j Mod 2) * 4 - 2) * Exp(-2 * j ^ 2 * (NumTrades ^ 0.5 * D) ^ 2)
'                Sig = Sig + Sigg
'
'            Next j
'
'            If D < BestD Then
'                BestD = D
'                BestLoc = lok
'                BestScale = Skale
'                BestKurt = Kurt
'                BestSig = Sig
'            End If
   
'        Next k
'
'   Next s
'
'Next l






Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
SecondsElapsed = Round((Timer - StartTime), 3)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation


Range("R3:v1251").Value = TheCDF
Range("s1").Value = TheRange
Range("i14").Value = nsum
Range("i15").Value = D
'Range("H19").Value = BestSig
'Range("I5").Value = BestLoc
'Range("I6").Value = BestScale
'Range("I8").Value = BestKurt
    
















End Sub
 
Upvote 0
Post the code using Diff1 and Diff2 that you tried that didn't work.
 
Upvote 0
Code: 
Sub anything()


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
StartTime = Timer


Dim LoLoc As Single, UpLoc As Single, LoScale As Single, UpScale As Single
Dim LoSkew As Single, UpSkew As Single, LoKurt As Single, UpKurt As Single
Dim TheRange As Single
Dim Diff1 As Integer, Diff2 As Integer


Dim ActCDF() As Variant
Dim TheCDF(2000, 5) As Single
Dim RunSum As Double, nsum As Double
Dim x As Single, D As Single
Dim i As Integer, a As Integer, j As Integer


'Define range of variables to search in
ActCDF = Worksheets("Distribution Generator").Range("e3:f602").Value
LoLoc = Range("J5").Value:    HiLoc = Range("K5").Value
LoScale = Range("J6").Value:  HiScale = Range("K6").Value
LoSkew = Range("J7").Value:   HiSkew = Range("K7").Value
LoKurt = Range("J8").Value:   HiKurt = Range("K8").Value
NumTrades = Range("I10").Value
inc = Range("I11").Value
TheRange = Round(Range("I12").Value, 2) * 100


BestD = 1
lok = -0.35
Skale = 2.44
Kurt = 3
Skew = 0.45




'For l = LoLoc / inc To HiLoc / inc
'
'    lok = l * inc + 0.0001
'
'    For s = LoScale / inc To HiScale / inc
'
'    Skale = s * inc + 0.0001
'
'        For k = LoKurt / inc To HiKurt / inc
'
'        Kurt = k * inc + 0.0001
'
        
        


            For i = 0 To 2 * TheRange
                x = -TheRange / 100 + i / 100
                TheCDF(i, 0) = x
                signX = x / Abs(x + 0.00001)
                SignSkew = Skew / Abs(Skew)
                C = (1 + (Abs(Skew) ^ Abs(1 / (x + 0.0000001) - lok)) * signX * -SignSkew) ^ 0.5
                Y = (1 / (Abs((x + 0.0000001 - lok) * Skale) ^ Kurt + 1)) ^ C
                TheCDF(i, 1) = Y


            Next i
            
            RunSum1 = 0
            RunSum2 = 0
            nsum = 0
            D = 0
            Sig = 0


            'Calculate area under entire CDF
            For a = 0 To TheRange * 2
                nsum = nsum + TheCDF(a, 1)
            Next a
'
            'Calculate area up to each x value; Find Maximum D value
            For i = 1 To TheRange * 2


                RunSum1 = RunSum1 + TheCDF(i - 1, 1)
                RunSum2 = RunSum1 + TheCDF(i, 1)
                PercentofCFD = (RunSum1 + RunSum2) / 2 / nsum
                TheCDF(i, 2) = PercentofCFD
                
            Next i
            
            For i = 2 To NumTrades
                
                For j = 0 To 2 * TheRange
                
                    If Abs(ActCDF(i, 1) - TheCDF(j, 0)) < 0.001 Then
                        Diff1 = Abs(ActCDF(i, 2) - TheCDF(j, 2))
                        TheCDF(j, 3) = Diff1
                        Diff2 = Abs(ActCDF(i - 1, 2) - TheCDF(j, 2))
                        TheCDF(j, 4) = Diff2
                    End If
                                           
                Next j
            Next i
'
'            For j = 1 To 200
'
'                Sigg = ((j Mod 2) * 4 - 2) * Exp(-2 * j ^ 2 * (NumTrades ^ 0.5 * D) ^ 2)
'                Sig = Sig + Sigg
'
'            Next j
'
'            If D < BestD Then
'                BestD = D
'                BestLoc = lok
'                BestScale = Skale
'                BestKurt = Kurt
'                BestSig = Sig
'            End If
   
'        Next k
'
'   Next s
'
'Next l






Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
SecondsElapsed = Round((Timer - StartTime), 3)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation


Range("R3:v1251").Value = TheCDF
Range("s1").Value = TheRange
Range("i14").Value = nsum
Range("i15").Value = D
'Range("H19").Value = BestSig
'Range("I5").Value = BestLoc
'Range("I6").Value = BestScale
'Range("I8").Value = BestKurt
    
















End Sub
 
Upvote 0
Edit: I had Diff1 and Diff2 dim'd as integers and they were therefore returning 0. Thank you both for your timely responses
 
Upvote 0
You must log in or register to reply here.

Similar threads

montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
195
montecarlo2012
montecarlo2012
H
  • Solved
VBA Code to Enter Formula in the Cell as Array Formula
Replies
5
Views
233
MARK858
MARK858
Z
  • Solved
VBA: Fill Array2 from Array1 with >= Values
Replies
7
Views
280
zero269
Z
C
  • Solved
Help! Can't pass a range of cell strings into a variant array/can't reference the strings in this array - myarray(I,1) gives subscript error
Replies
3
Views
113
Peter_SSs
Peter_SSs
R
  • Question Question
VBA to automate calculation
Replies
1
Views
223
HighAndWilder
H

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top