A problem for VBA?

BWMagee

Board Regular
Joined
Feb 18, 2014
Messages
112
ABCDEFGHI
3
250​
500​
750​
1000​
1250​
1500​
4NumeratorDenominatorDecimal
1​
2​
3​
4​
5​
6​
5
1​
1​
1​
250​
500​
750​
1000​
1250​
1500​
6
2​
1​
2​
500​
1000​
1500​
2000​
2500​
3000​
7
3​
2​
1.5​
375​
750​
1125​
1500​
1875​
2250​
8
4​
3​
1.333333333​
333.3333333​
666.6666667​
1000​
1333.333333​
1666.666667​
2000​
9
5​
3​
1.666666667​
416.6666667​
833.3333333​
1250​
1666.666667​
2083.333333​
2500​
10
5​
4​
1.25​
312.5​
625​
937.5​
1250​
1562.5​
1875​
11
6​
5​
1.2​
300​
600​
900​
1200​
1500​
1800​

I need to perform some complex equations which may require VBA, based on the above table.

If the formula below is called Formula1:

=EXP(-3.51*(0.24/((0.0207*MIN(D5:D$6))+18.96))*(MAX(D5:D$6)-MIN(D5:D$6)))-EXP(-5.75*(0.24/((0.0207*MIN(D5:D$6))+18.96))*(MAX(D5:D$6)-MIN(D5:D$6)))

And if Formula2 is exactly the same, but with different cell references, ditto Formula 3, then the complete formula will look like this:

=Formula1+Formula2+Formula3, etc.... +FormulaX+FormulaY+FormulaZ

If I wasn't using VBA, all this would need to be placed in a single cell.

We can see Formula1 references D5 and D6.
Formula2 would reference D5 and D7
Formula3 would reference D5 and D8
...all the way down to the end of the table. Then across:
FormulaX would reference D5 and E6
FormulaY would reference D5 and E7
FormulaZ would reference D5 and E8
...all the way across to the end of the table.

All that would be in a single cell, which means many additions.

For the next cell down:
Formula1 would reference E6 and E5
Formula2 would reference E6 and E7
Formula3 would reference E6 and E8
...all the way down. And:
FormulaX would reference E6 and D5
FormulaY would reference E6 and D7
...all the way across.

In other words, in each cell I place the formula, it would need to reference every cell in the table from D5:I11. And the total number of cells I placed the formula in would equal the number of cells in the table D5:I11, which is 42.

In order for this not to be very tedious, I'm wondering if there is a faster solution than entering all that data one cell at a time. Such as VBA?

If you are familiar with the languages MATLAB, C, or LISP, the code for those programs are given here: computer programs
You may be able to use the information there for an Excel VBA translation (although my above formula above does not take into account amplication, whereas the programs at the given link do).
Any advice on this is much appreciated!

Note: The solution does not have to be VBA.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
This looks like a summation attempt. Can you give a reference to the limit / summation equation you are trying to represent in this worksheet?

=Formula1+Formula2+Formula3, etc.... +FormulaX+FormulaY+FormulaZ

If I wasn't using VBA, all this would need to be placed in a single cell.
 
Upvote 0
This looks like a summation attempt. Can you give a reference to the limit / summation equation you are trying to represent in this worksheet?

Was this not stated in the original post?

Formula1 = EXP(-3.51*(0.24/((0.0207*MIN(D5:D$6))+18.96))*(MAX(D5:D$6)-MIN(D5:D$6)))-EXP(-5.75*(0.24/((0.0207*MIN(D5:D$6))+18.96))*(MAX(D5:D$6)-MIN(D5:D$6)))
Formula2 = the same but referencing different cells, as stated above. Ditto formula3, etc...
The range for reference cells is D5:I11.
 
Upvote 0
Yes, but there may be a way to get the same result using another methodology?
 
Upvote 0
How is this?

Code will go into a VBA module. It will create a macro name BWMagee you can run and it will use your data from range the B2:C11.

VBA Code:
Public Sub BWMagee()
  'Built from codebase found @ URL in this forum post
  Dim Source, Target, sRow As Range
  Dim alpha, startInt, endInt, idx, i, j, k, n As Integer
  Dim frq(10), ampl(10), g(10), disso(1500, 2), _
    ds, d, s1, s2, c1, c2, a1, a2, _
    fMin, fDif, i1, ar1, ar2, ex1, ex2, dn As Double
  ds = 0.24
  s1 = 0.0207
  s2 = 18.96
  c1 = 5#
  c2 = -5#
  a1 = -3.51
  a2 = -5.75
  idx = 0
  
  startInt = 100
  endInt = 230
  
  'Init freq(i) using Col B2:B11
  'Init amp(i) using Col C2:C11
  Set Source = Sheets("MrExcel Help - BWMagee").Range("B2:C11")
  Set Target = Sheets("MrExcel Help - BWMagee").Range("E2")
  n = Source.Rows.Count
  For Each sRow In Source.Rows
    frq(sRow.Row - Source.Row + 1) = sRow.Cells(1, 1)
    ampl(sRow.Row - Source.Row + 1) = sRow.Cells(1, 2)
  Next sRow
  For alpha = startInt To endInt
    idx = idx + 1
    d = 0
    For k = 1 To n
      g(k) = alpha * frq(k) / 100
    Next k
    ' Calculate dissonance between f and alpha*f
    For i = 1 To n
      For j = 1 To n
        fMin = myMin(g(j), frq(i))
        fDif = Abs(g(j) - frq(i))
        ar1 = a1 * ds / (s1 * fMin + s2) * fDif
        ar2 = a2 * ds / (s1 * fMin + s2) * fDif
        If ar1 < -88 Then ex1 = 0 Else ex1 = Exp(ar1)
        If ar2 < -88 Then ex2 = 0 Else ex2 = Exp(ar2)
        'dn = Min(ampl(i), ampl(j)) * (c1 * ex1 + c2 * ex2)
        d = d + myMin(ampl(i), ampl(j)) * (c1 * ex1 + c2 * ex2)
      Next j
    Next i
    disso(idx, 1) = d
    disso(idx, 2) = alpha / 100
  Next alpha
  'Place diss array into Target Range
  For n = 1 To idx
    Target.Offset(n - 1, 0).Value = disso(n, 2)
    Target.Offset(n - 1, 1).Value = disso(n, 1)
  Next n
  Set Target = Nothing
  Set Source = Nothing
End Sub

Public Function myMin(a1, a2 As Variant) As Variant
  If a1 < a2 Then myMin = a1 Else myMin = a2
End Function

Book1
ABCDEF
1
212180.87423014
3241.0181.4233841
4821.0281.96344667
5461.0382.49446156
61031.0483.01647211
7681.0583.5295213
8711.0684.03365173
9121.0784.52890566
10341.0885.01532497
11561.0985.49295119
121.185.9618255
131.1186.42198872
141.1286.87348131
MrExcel Help - BWMagee
 
Upvote 0
I really appreciate the effort on this!
In my initial table, D5:I11 were all frequencies, with Column D being the base, and E:I being multiplications of Row 4. I haven't listed any amplitudes. In your code I notice this:

'Init freq(i) using Col B2:B11
'Init amp(i) using Col C2:C11

Does this mean B2:B11 in your table = D5:I11 in mine?
Does it also mean I need to create a column (or row) for amplitude?
 
Upvote 0
B is freq
C is amp
I had no real idea for values...
If you need d5 thru I11 ( or more ) the code will need altered some
 
Upvote 0
B is freq
C is amp
I had no real idea for values...
If you need d5 thru I11 ( or more ) the code will need altered some

Ok in the VBA code I tried replacing B2:B11 with D5:I11 for the frequencies.
I also added a row for amplitude from D2:I2 and put this in VBA in place of C2:C11 [the idea is that the frequencies in column D will sound at the amplitude in column D, frequencies in column E will sound at the amplitude in column E, etc...]
But it gave me a 'runtime 9 error - subscript out of range'

In the above table there are no values yet given for amplitude, as I haven't properly worked out how it fits into the formula, but I will have to if it is going to be included in the VBA. I was essentially working on the premise that amplitude = 1. But a good starting point is that amp = 1/partial. The partials in the table above are D4:I4. So if amplitude is in D2:I2, then D2 = 1/D4, in E2 = 1/E4, in F2 amp = 1/F4, etc... The frequences are D5:I11
 
Upvote 0
Yup I did say it would need modified it was coded to only do 10

Also if you code your frequency data in column B and amplitude data in column C I will alter the code and resultant data will be in E& F
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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