Working with fractions

kshora

Board Regular
Joined
Aug 2, 2006
Messages
211
Some months back I did receive the following code, can someone help me, how to use this code, I am not very good with VB or macros. What I want is to convert decimals to fractions (with denominators of 2, 4, 8, 16 or 32)

Help is highly appreciated

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' note that this implies that in Excel you did Insert | Name | Define...
' and defined a named range «ChangeToFractions» for those cells
' that you want converted from decimals to fractions
const c_strTestRangeName as string = "ChangeToFractions"
Dim rngCell As Range

If Intersect(Target, range(c_strTestRangeName)) Is Nothing Then Exit Sub

application.enableevents = false
for each rngCell in Intersect(Target, range(c_strTestRangeName)).cells
with rngcell
.value = Round(.Value * 32, 0) / 32
end with
next rngcell
application.enableevents = true

End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

select the range on your sheet where you want to apply the code
in the namebox type
ChangeToFractions

http://www.cpearson.com/excel/events.htm
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE


I added a IsNumber-check: else your code will bug when you type a non-number: this would cause that the event wouldn't be triggered anymore during the entire Excelsession
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' note that this implies that in Excel you did Insert | Name | Define...
' and defined a named range «ChangeToFractions» for those cells
' that you want converted from decimals to fractions
Const c_strTestRangeName As String = "ChangeToFractions"
Dim rngCell As Range

If Intersect(Target, Range(c_strTestRangeName)) Is Nothing Then Exit Sub

Application.EnableEvents = False
    For Each rngCell In Intersect(Target, Range(c_strTestRangeName)).Cells
        With rngCell
        If Application.IsNumber(.Value) Then .Value = Round(.Value * 32, 0) / 32
        End With
    Next rngCell
Application.EnableEvents = True

End Sub
kind regards,
Erik
 

kshora

Board Regular
Joined
Aug 2, 2006
Messages
211
I did as you advised, but nothing happens, the decimals did not change to fractions.

What do I do

Thanks
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
1.
be sure your code is in the correct SHEETmodule

2.
after the line
Dim rngCell As Range
put a testline
Code:
MsgBox "change event triggered"
you should get the popup
if not close Excel and open again
(there are other methods, but this will do for sure)
now it should popup

3.
be sure you changed a cell within the named range "ChangeToFractions"

best regards,
Erik
 

kshora

Board Regular
Joined
Aug 2, 2006
Messages
211

ADVERTISEMENT

I again did as you advised, nothing is happening

I opened a new worksheet, input some fractions and named the range as "ChangeToFractions" in the Name Box. Right clicked the Sheet1, selected view Code and pasted your code in it and also added the new line and then simply closed the VBE window.

Am I doing any mistake.

Thanks and regards
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
there is a misunderstanding

the code you displayed in your initial post is "event"code
it will work when you put the decimals AFTER you pasted the code

FIRST paste the code
THEN put some values in the range
 

kshora

Board Regular
Joined
Aug 2, 2006
Messages
211

ADVERTISEMENT

OOOO!! I See, my fault I did not understand. What I want is to convert to fractions in the existing workbook with already tonnes of data. How do I do it?

Thanks and regards
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
OK, we've learned something
the second option will be the fastest, talking about "tonnes of data"

you will need to choose a way to determine the range where the changes are to be made
as it is now you need to select the range
for the second code it has to be a "rectangular block" of data (technically spoken only one area)
Code:
Option Explicit

Sub test()
Dim cell As Range

Application.ScreenUpdating = False

    For Each cell In Selection
    'For Each cell In ActiveSheet.UsedRange
    'For Each cell In Range("whatever")
        With cell
        If Application.IsNumber(.Value) Then .Value = Round(.Value * 32, 0) / 32
        End With
    Next cell

Application.ScreenUpdating = True

End Sub

Sub testquick()
Dim rng As Range
Dim arr As Variant
Dim i As Long
Dim j As Integer

Set rng = Selection
'Set rng = ActiveSheet.UsedRange
'Set rng = Range("whatever")

arr = rng

On Error Resume Next
    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count
        arr(i, j) = Round(arr(i, 1) * 32, 0) / 32
        Next j
    Next i

rng = arr

End Sub

best regards,
Erik
 

kshora

Board Regular
Joined
Aug 2, 2006
Messages
211
Selecting the range will not be very difficuly, I can do that. I need to know how do I implement your code

I did exactly as I did previously, but nothing is happening

Please advise

Thanks and regards
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
sorry, I forgot to explain

but feel encouraged to test out yourself: don't sit there and wait :)

you can feel confident by making a copy of your file and then experiment as much as you want ...

you need to paste the code in a normal module
start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the your code

to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)


assign shortcutkey
menu Tools/Macro/Macros
select your macro
click options
choose a character as shortcut: example T
to run the macro press Ctrl+Shift+T
 

Forum statistics

Threads
1,136,649
Messages
5,676,991
Members
419,667
Latest member
MegEri

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
Top