code for Calculate the following series 1 + ½ + ¼ + 1/8 + 1/16 ... "

[petrwurfs]

Hello,

I would like some help with the following.

I'm working on an assignment in VBA code.

The results must be calculated from a set of numbers. The calculation starts with 1 and then it goes on with ½ + ¼ + 1/8 + 1/16.....
So the assignment is: Calculate 1 + ½ + ¼ + 1/8 + 1/16.....
Ask the user through msgbox beforehand how many terms must be calculated.

What is the best way to write this code?

With kind regards,

Peter

[shg]

We don't do homework, but will help if you have a specific and narrow question, and show some effort of your own.

[petrwurfs]

Thanks for the response.

This is not homework. I try to develop myself in VBA code for my job/career. In my job I come across VBA code more and more.

What I had so far is (some things are in Dutch):
-------

Sub reeksdelen()
'Bereken de volgende reeks 1 + ½ + ¼ + 1/8 + 1/16 …'
'Vraag vooraf aan de gebruiker hoeveel termen er moeten opgeteld worden. Druk het resultaat af.'

Dim imput As Byte, som As Double, teller As Single

teller = 0.5

imput = InputBox("hoeveel termen moeten er opgeteld worden?")

teller = 1

Do While invoer = 0


som = 1 + teller + invoer

Loop

MsgBox (som)

End Sub

[shg]

Please Login or Register  to view this content.

[petrwurfs]

Thank you very much!

Now I can carry on and I get what my mistake was.

[shg]

You're welcome.

As the sum of a power series, you can also do this without a loop:

Please Login or Register  to view this content.

[joeu2004]

[This forum's editor has problems with quoting some content. So I had to re-enter the "quoted" part. I took the liberty of translating some of the Dutch.]

This is not homework. I try to develop myself in VBA code for my job/career. In my job I come across VBA code more and more. What I had so far is (some things are in Dutch):

Please Login or Register  to view this content.

Some binary number theory might help.

First, Excel and VBA use 64-bit binary floating-point to represent numbers. Consequently, the series is limited to 1 + 1/2 + 1/4 + ... + 1/2^52 if you want exact results.

Second, if "imput" is n (1 to 53), the sum of the series is simply 1 + (1 - 2^-(n-1)) [1], which is 2 - 2^-(n-1). So the VBA implementation can be simplified to:

Please Login or Register  to view this content.

Caveat: Excel and VBA formatting is limited to 15 significant digits. So some values of "som" will not appear exactly. For example, when "imput" is 53, "som" will appear to be 2. But it is actually 1.99999999999999,97779553950749686919152736663818359375 as intended. (I use period for the decimal point and comma to demarcate 15 significant digits.)

A subtle detail.... Generally, it is better to use type Long for integers and type Double for non-integers in order to avoid arithmetic limitations.

For example, type Byte cannot be negative, and it is limited to 255. You might have intended to take advantage of that to limit the range of input values. But exceeding those limits would result in VBA programming errors, which is not a good way to handle user errors.

And with type Single, you lose precision, which further limits the size of "imput" if you want exact results.

[EDIT] As a teachable moment, I was going to discuss the loop implementation, even though it is unnecessary. But I see that shg already provided an example, albeit without explanation.


-----
[1] 2 - 2^-52 is computable despite 64-bit binary floating-point limitations because the arithmetic actually uses an 80-bit binary floating-point representation in Intel-compatible computers, which I believe is all PCs and Macs these days.