questions

I

icestationzbra

julie,

here is the code that i am using:

Option Explicit

Sub test1()

Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim x As String

Dim started As Date, ended As Date

started = Timer

For i = 1 To 9999

j = Len(Trim(i))

l = 0
For k = 1 To j

l = l + Mid(i, k, 1) ^ 3

Next k

If i = l Then

x = x & ", " & i

End If

Next i

Sheet1.Range("a1").Value = x

ended = Timer

Sheet1.Range("a2").Value = Format(ended - started, "000.00000")

End Sub

*****

this is based out of a mathematical puzzle. take for example, th
number 153. cube every individual digit in it, and add them. the resul
is 153. so is 370, 371 and 407. i was trying to render this into
code.

thanks,

mac
 
D

Dave Peterson

Inside VBA's help for Long, you can see this:

Long (long integer) variables are stored as signed 32-bit (4-byte) ....

So when you did this:
j = len(i)

You essentially wrote:
j = 4

(try putting "msgbox i & "--" & len(i)" in your code and you'll see that it
always returns a 4.)

when you did this:
j = Len(Trim(i))
you converted i to a string (trim(i) returns a string, that's why your code
worked ok.
This would have worked as well:
j = Len(CStr(i))

====
J.E.'s code from:
http://www.mcgimpsey.com/excel/accumulator.html

The event that you're using is the Worksheet_Change event. Each time that
something gets changed (by typing--not calculating) on that worksheet, the
_change event fires and the code runs again.

So if you change a value in the worksheet via code, that change causes that same
event to fire, which changes the value, which causes the event to fire, which
changes the values, which causes the event to fire, ....(until excel gets tired
and throws up its hands and just gives up and stops!).

By using application.enableevents = false, you tell excel to stop looking for
any changes. So the change that the code doesn't cause the event to fire.

Next time, try it with the .enableevents commented out, but put a break point in
the code--so you can step (using F8) throught the code. You'll see how many
times excel actually does it before it gets tired.

And J.E. puts application.enableevents at the end to make sure that excel is
ready for your next (manual) change.
 
Top