VBA Help

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
950
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Good day everyone,

I need help with the following code below. The code takes a 5 digit number from 00000 through 99999 and calculates the 5 digit number to the smallest ascending order. For example, 98765 using the vba code below is 56789.

I receive a "value" error if the 5 digit value is too high.
For example in cell H20 is the value 65745
In cell J20 is the formula lowform_5(H20).
In cell H21 is the value 26546. Cell J21 gives me the correct value of 24566.
Not sure why certain values in cell column J are giving me "Value" errors.
Seems to be if a value in cell column H is higher than a certain number.

Thank you in advance.

Function lowform_5(five_digit)

Dim channel() As Integer
Dim slot() As Integer
Dim num As Integer, pos As Integer
Dim c0, c1, c2, c3, c4, c5

c0 = five_digit
c1 = Int(c0 / 10000)
c2 = Int((c0 Mod 10000) / 1000)
c3 = Int((c0 Mod 1000) / 100)
c4 = Int((c0 Mod 100) / 10)
c5 = c0 Mod 10



ReDim channel(9)
ReDim slot(5)
channel(c1) = channel(c1) + 1
channel(c2) = channel(c2) + 1
channel(c3) = channel(c3) + 1
channel(c4) = channel(c4) + 1
channel(c5) = channel(c5) + 1
pos = 1
For num = 0 To 9
While channel(num) > 0
slot(pos) = num
channel(num) = channel(num) - 1
pos = pos + 1
Wend
Next num
lowform_5 = slot(1) * 10000 + slot(2) * 1000 + slot(3) * 100 + slot(4) * 10 + slot(5)

End Function
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
I think it is because Integers only go up to 32767.

This change worked for me (changing all variables declarations from Integer to Long and delaring the function itself as Long):
VBA Code:
Function lowform_5(five_digit) As Long

Dim channel() As Long
Dim slot() As Long
Dim num As Long, pos As Long
Dim c0, c1, c2, c3, c4, c5

c0 = five_digit
c1 = Int(c0 / 10000)
c2 = Int((c0 Mod 10000) / 1000)
c3 = Int((c0 Mod 1000) / 100)
c4 = Int((c0 Mod 100) / 10)
c5 = c0 Mod 10



ReDim channel(9)
ReDim slot(5)
channel(c1) = channel(c1) + 1
channel(c2) = channel(c2) + 1
channel(c3) = channel(c3) + 1
channel(c4) = channel(c4) + 1
channel(c5) = channel(c5) + 1
pos = 1
For num = 0 To 9
While channel(num) > 0
slot(pos) = num
channel(num) = channel(num) - 1
pos = pos + 1
Wend
Next num
lowform_5 = slot(1) * 10000 + slot(2) * 1000 + slot(3) * 100 + slot(4) * 10 + slot(5)

End Function
 
Upvote 0
Solution
Thanks so much. Works like a charm.
Good to know Interger max is 32767
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,728
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top