troubleshooting Question

Most efficient VBA function to display set of dollar bills given back to a customer like a cash register?

Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America asked on
Microsoft AccessProgrammingC#VBA
10 Comments5 Solutions85 ViewsLast Modified:
Four months back I had an interview programming aptitude test where I had to produce a C# program that would act like a cash register and given any amount produce how many ten, fives, one dollar bills with change as the result.

I got stressed and wanted to do it via VBA first and then I could've done it in C# but they wouldn't let for obvious reasons.  Then when I came home, nothing I tried worked in VBA cause I was still so disappointed.

So tonight I got it working.  The time it took me to complete is in the code below.

My main specific question - is there a more efficient way to do what I did below?  (I also included hundreds, fifties, twenties also but wasn't the original requirement).  

Will this function work for any combination of values submitted?  Does it truly work?

How would you have done it?  Thank you for any feedback.

Option Compare Database
Option Explicit

'1st phase-
'2/17/2020
'started:  9:37 pm
'ended:  10:21 p.m.
'
'2nd phase -
'6/18/2020-
'started:  12:30 am.
'completed:  1:13 a.m.
'
'sample tries:

'Debug.Print DollarValues("28")
'? DollarValues("$21,457.03")

Public Function DollarValues(sValue As String) As String

  Dim sNewValue As String
  '
  Dim lHundreds As Double
  Dim lFifties As Double
  Dim lTwenties As Double
  Dim lTens As Double
  Dim lFives As Double
  Dim lOnes As Double
  Dim lCents As Currency
  '
  Dim dTotalThusFar As Double
  Dim dLeftOver As Double
  Dim dFullValue As Double
  '
  'replace any symbols not necessary...
  
  sValue = Replace(sValue, "$", "")
  sValue = Replace(sValue, ",", "")
  
  dFullValue = Val(sValue)
  dLeftOver = dFullValue
  '--------------------------------------
  'determine no. of hundred dollar bills section
  If dLeftOver >= 100 Then
      lHundreds = dLeftOver / 100
      lHundreds = Int(lHundreds)
      'Debug.Print "Hundreds: " & lTens
      sNewValue = sNewValue & "Hundreds: " & lHundreds & vbNewLine
      'after number of Hundred dollar bills are identified - subtract the rest.
      dLeftOver = dLeftOver - (lHundreds * 100)
      'Debug.Print "Left over: " & dLeftOver
  End If
  '--------------------------------------
  'determine no. of fifty dollar bills section
  If dLeftOver >= 50 Then
      lFifties = dLeftOver / 50
      lFifties = Int(lFifties)
      'Debug.Print "Fifties: " & lTens
      sNewValue = sNewValue & "Fifties: " & lFifties & vbNewLine
      'after number of Fifty dollar bills are identified - subtract the rest.
      dLeftOver = dLeftOver - (lFifties * 50)
      'Debug.Print "Left over: " & dLeftOver
  End If
  '--------------------------------
  'determine no. of twenty dollar bills section
  If dLeftOver >= 20 Then
      lTwenties = dLeftOver / 20
      lTwenties = Int(lTwenties)
      'Debug.Print "Twenties: " & lTens
      sNewValue = sNewValue & "Twenties: " & lTwenties & vbNewLine
      'after number of Ten dollar bills are identified - subtract the rest.
      dLeftOver = dLeftOver - (lTwenties * 20)
      'Debug.Print "Left over: " & dLeftOver
  End If
  '--------------------------------
  'determine no. of ten dollar bills section
  If dLeftOver >= 10 Then
      lTens = dLeftOver / 10
      lTens = Int(lTens)
      'Debug.Print "Tens: " & lTens
      sNewValue = sNewValue & "Tens: " & lTens & vbNewLine
      'after number of Ten dollar bills are identified - subtract the rest.
      dLeftOver = dLeftOver - (lTens * 10)
      'Debug.Print "Left over: " & dLeftOver
  End If
  '--------------------------------
  'determine no. of five dollar bills
  If dLeftOver >= 5 Then
      lFives = dLeftOver / 5
      lFives = Int(lFives)
      'Debug.Print "Fives: " & lFives
      sNewValue = sNewValue & "Fives: " & lFives & vbNewLine
      'after number of Five dollar bills are identified - subtract the rest
      dLeftOver = dLeftOver - (lFives * 5)
      'Debug.Print "Left over: " & dLeftOver
      
  End If
  '--------------------------------
  'determine no. of one dollar bills
  If dLeftOver >= 1 Then
      'set true ones left...
      lOnes = dLeftOver
      If lOnes = Int(lOnes) Then
        'value is good.
      Else
        lOnes = Int(lOnes)
      End If
      If lOnes <> 0 Then
        'Debug.Print "Ones: " & lOnes
        sNewValue = sNewValue & "Ones: " & lOnes & vbNewLine
        'after number of one dollar bills are identified - subtract the rest
        dLeftOver = dLeftOver - (lFives * 1)
        'Debug.Print "Left over: " & dLeftOver
      End If
  End If
  '--------------------------------
  'determine no. of cents left over
  '
  'the following allows to double-check and verify the work
  'by starting over with adding all values and then subtracting the .decimal values left over.
  '
  dTotalThusFar = (lHundreds * 100) + (lFifties * 50) + (lTwenties * 20) + (lTens * 10) + (lFives * 5) + (lOnes * 1)
  If dTotalThusFar < dFullValue Then
    lCents = dFullValue - dTotalThusFar
    'Debug.Print "Cents: " & lCents
    sNewValue = sNewValue & "Cents: " & lCents
  End If

  DollarValues = sNewValue

End Function
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 5 Answers and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 5 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros