How to round to a certain number of significant figures using Excel VBA?

Edit: This part is solved!

So I have a bunch of calculations in a program I'm making, and the numbers are getting so long that I'm getting overflow errors. It's resulting from things like dividing a 50 digit number by another 50 digit number. I need a way to round specific numbers to a certain amount of significant digits.

Here's some more information. First of all, all of the numbers I will be rounding will always be less than 1. The numbers can get as small as to the 1E-50. I only care about the first 10 or so significant digits. I just need a way to round it off to around ten sig figs before moving on to the next calculation in order to prevent overflow errors. That, and having a number go out to 50 figures is very useless.

I was thinking of maybe looping through the number, digit by digit? The program could add 1 to a counter when the number is equal to zero, and then break out of the loop once it hits anything other than zero. Then the next step could truncate the number to that counter plus however many significant digits I want.

So for .000001234567812345678 for example. It would return 5 zeroes. If I wanted ten significant figures, I would do five + ten = 15. The program would then only keep the first 15 digits after the decimal, so the number would be truncated to .000001234567812.

Another idea might be using the log. Then the power the new number would be raised to would be the number of zeroes in front of the significant digits.

What I don't know is, first of all, how to go through a number digit by digit, and checking if each number is a zero. I also don't know how to truncate a number to a certain amount of digits.

Any help would be greatly appreciated!

Edit: This part is not solved.

I would feel bad making another post so soon, so I'm going to ask the question here. If it's not answered in 20 minutes or so, maybe I'll make another post. Why isn't this working?

Sub Rounding()
    Tracker = 0
    For i = 1 To 10
        Tracker = Tracker + 1
        Cells(1, Tracker) = Rnd
        Cells(1, Tracker) = Application.Evaluate("=Round(Cells(1, Tracker), 4 - (Int(Log(Cells(1,Tracker))) + 1))")
    Next
    Columns("A:J").EntireColumn.AutoFit
End Sub

I keep getting an error, #NAME?, in every single cell. This should round each number to four significant digits.

Solution

Assuming your value is in A2, try either of these:

=ROUND(A2, 3-(INT(LOG(A2))+1))

=ROUND(A2,3-LEN(INT(A2)))

Taken from here.