Calculate a ratio from two numbers in vba

[Av8tordude]

=num1/GCD(num1,num2)&":"&num2/GCD(num1,num2)


This formula is a ratio between two numbers. Is there a way to do this in vba? I have 3 labels (Label1 , Label2 , Label3) in a userform. Label1 & Label2 will have the numbers and I would like to output the ratio value to Label3. Can anyone assist

Thank you for kindness

 

[Leith Ross]

Hello Av8tordude,

Here is the Greatest Common Denominator function in VBA. Add this to either your UserForm or place in a Standard Module.

' Written:  June 12, 2017
' Author:   Leith Ross
' Summary:  Returns the Greatest Common Divisor of two positive integers.


Function GCD(n1 As Double, n2 As Double) As Double


    Dim div     As Double   ' Divsor
    Dim dvd     As Double   ' Divedend
    Dim r       As Double   ' Remainder
    
        ' // Do not divide the numbers if one of them is zero.
        If n1 = 0 Or n2 = 0 Then
            GCD = n1 + n2
            Exit Function
        End If
        
        ' // Assign the dividend and divisor.
        dvd = n1
        div = n2
        
        ' // Swap the numbers if the dividend is smaller than the divsor.
        If n1 < n2 Then
            div = n1
            dvd = n2
        End If
        
        ' // Apply the Euclid's algorthm.
        Do
            r = dvd Mod div
            If r = 0 Then Exit Do
            dvd = div
            div = r
        Loop
        
        GCD = div
        
End Function

 

[Av8tordude]

Hi Leith, Thank you so kindly for this, but how do I call this to output the value to my userform label3. Forgive me, but its a little advance for my knowledge.

Thanks

 

[Rick Rothstein]

Hello Av8tordude,

Here is the Greatest Common Denominator function in VBA. Add this to either your UserForm or place in a Standard Module.

' Written:  June 12, 2017
' Author:   Leith Ross
' Summary:  Returns the Greatest Common Divisor of two positive integers.


Function GCD(n1 As Double, n2 As Double) As Double


    Dim div     As Double   ' Divsor
    Dim dvd     As Double   ' Divedend
    Dim r       As Double   ' Remainder
    
        ' // Do not divide the numbers if one of them is zero.
        If n1 = 0 Or n2 = 0 Then
            GCD = n1 + n2
            Exit Function
        End If
        
        ' // Assign the dividend and divisor.
        dvd = n1
        div = n2
        
        ' // Swap the numbers if the dividend is smaller than the divsor.
        If n1 < n2 Then
            div = n1
            dvd = n2
        End If
        
        ' // Apply the Euclid's algorthm.
        Do
            r = dvd Mod div
            If r = 0 Then Exit Do
            dvd = div
            div = r
        Loop
        
        GCD = div
        
End Function

I am guessing that you used Doubles for your variable data type in order to maximize the size of the numbers your function could handle. If so, I think you will be interested in this variation (something I wrote and posted to the old newsgroups when they existed) which will handle numbers up to 28 digits long! If any number being passed in contains more than 15 digits, you must surround that number with quote marks (you can be safe and do that will all passed in numbers) in order to protect the value from being coerced to a Long before the function gets a chance to see it.

Function GCD(ByVal Num1 As Variant, ByVal Num2 As Variant) As Variant
  Dim Remainder As Variant
  If Num1 = 0 Or Num2 = 0 Then
    GCD = Num1 + Num2
  ElseIf Len(Num1) < 29 And Len(Num2) < 29 Then
    Do
      Remainder = Num2 - Int(CDec(Num2) / Num1) * Num1
      Num2 = Num1
      Num1 = Remainder
    Loop While Remainder
    GCD = Num2
  End If
End Function

 

[Leith Ross]

Hello Av8tordude,,

Assuming you have num1 and num2 defined in your UserForm then you would assign the ratio like this...

Label3.Caption = num1/GCD(num1,num2)&":"&num2/GCD(num1,num2)

 

[Av8tordude]

Thank you both. Greatly appreciate your kindness and help