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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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

S
  • Solved
Standard deviation of a subset in VBA
Replies
7
Views
245
SHW2022
S
B
  • Solved
VBA Loop Through an Array
Replies
6
Views
475
Peter_SSs
Peter_SSs
togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
449
MARK858
MARK858
P
  • Question
How to pass variable between subs that are stored as array element through CreateObject("Scripting.Dictionary")
Replies
1
Views
114
Micron
M
K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
203
Alex Blakenburg
A

Forum statistics

Threads
1,215,945
Messages
6,127,844
Members
449,411
Latest member
adunn_23

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