Excel : Validating Octal Data Input

  daba 23:04 22 Sep 2006

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 2006


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 2006

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........


  daba 23:23 22 Sep 2006

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

  VoG II 00:15 23 Sep 2006

You can try this

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 2006

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
ValidEntry = True
End If

GoTo End_Handler


ValidEntry = False


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
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 2006

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

  silverous 00:45 23 Sep 2006

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.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

Best Keyboards for Designers & Artists

What to ask Siri on the HomePod

Meilleurs VPN (2018)