Excel : Validating Octal Data Input

  daba 23:04 22 Sep 06
Locked

I regularly have to work with Octal (base 8) numbering systems.

Is there a way to validate data input into a cell is a valid Octal number ?

eg. 267 is valid : 268 is not

I am thinking a custom Data->Validation formula may be able to do it, but can't get the formula correct.

Perhaps I'll need a vba script ?...

  VoG II 23:15 22 Sep 06

Code:

Public Function ConvertOctalToDecimal(BinVal As String) As String
Dim iVal#, temp#, i%, Length%

Length = Len(BinVal)
For i = 0 To Length - 1
temp = CInt(Mid(BinVal, Length - i, 1))
iVal = iVal + (temp * (8 ^ i))
Next i
ConvertOctalToDecimal = iVal
End Function


If that falls over, presumably it is not a valid octal number.

  daba 23:17 22 Sep 06

tried ISNUMBER(OCT2DEC()) in the custom data validation and it rejected the input of 8. (OCT2DEC is a function in the Analysis Tool-Pack Add-In)

But it rejects any input value, even if a legal octal value........

BTTDB

  daba 23:23 22 Sep 06

how do i get the code to be triggered by entering a number into a cell ?

  VoG II 00:15 23 Sep 06

You can try this

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(False, False) <> "A1" Then Exit Sub
Dim iVal#, temp#, i%, Length%
Length = Len(Target.Value)
For i = 0 To Length - 1
temp = CInt(Mid(Target.Value, Length - i, 1))
iVal = iVal + (temp * (8 ^ i))
Next i
Range("B1").Value = iVal
End Sub

Right click the sheet tab > View code and copy and paste it in. Type a number in A1 and see what appears in B1. I'm not sure that it works however. There was a similar query on MrExcel today about hexadecimal numbers and no (sensible) reply so far.

  silverous 00:18 23 Sep 06

VoG - that function succeeds with 268 (an invalid number).

I was thinking NOT(ISERROR(OCT2DEC(A3))) but it doesn't seem to like using OCT2DEC in the validation.

Problem with user developed VBA function is that you apparently can't use them in data validation formulas.

To answer your last question and provide a solution, try this:

Press ALT and F11 to get into the VBA editor.
Right Click on where it says VBA Project on the left pane and pick "Insert" then "Module"

Copy & Paste this code in:

Public Function ValidEntry(BinVal As String) As Boolean

On Error GoTo Error_Handler

Dim eVal As Variant

eVal = Application.Evaluate("OCT2DEC(" & BinVal & ")")

If IsError(eVal) Then
ValidEntry = False
Else
ValidEntry = True
End If

GoTo End_Handler

Error_Handler:

ValidEntry = False

End_Handler:

End Function



Then, assuming you only want to validate column 8 in Sheet 1 (Quite fitting for Octal! Column H - I prefer to restrict these things if we can), Double click on Sheet1 in the pane on the left of the VBA editor, then copy and paste this code in:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 8 And IsNumeric(Target.Value) And Not (IsEmpty(Target.Value)) Then
If ValidEntry(Target.Value) Then
' Do Nothing
Else
MsgBox "Invalid Octal Number, please re-enter"
Target = ""
End If
End If

End Sub


That should work, if not let me know and I can send you it working (it does for me).

  VoG II 00:31 23 Sep 06

I'm re-evaluating my retirement package...

  silverous 00:45 23 Sep 06

Lol, don't be like that Vog. I learn something from you everyday, sometimes twice a day :) You are still Excel king, I'm just creative with VBA every now and then, done lots of it. Didn't even know about validation formulas til I read this thread.

This thread is now locked and can not be replied to.

LG G6 review: Hands-on with LG’s bold, big-screen shot at perfection

1995-2015: How technology has changed the world in 20 years

How the 2017 Oscar-winning VFX of The Jungle Book were created

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…