Syntax for Cell type
 

Hi

In VBA I want to get the cell information on a cell e.g. in the
Application itself, I can put:
=CELL("type",A1)
in Cell B1
However, I would like to extract the same information about (multiple)
cells directly from VBA itself i.e. in a procedure, without entering a
formula into an Excel cell.
Please can you give me the VBA syntax for that.

Many thanks

Tim

Syntax for Cell type
 

Hi

In VBA I want to get the cell information on a cell e.g. in the
Application itself, I can put:
=CELL("type",A1)
in Cell B1
However, I would like to extract the same information about
(multiple) cells directly from VBA itself i.e. in a procedure,
without entering a formula into an Excel cell.
Please can you give me the VBA syntax for that.

Many thanks

Tim

You can dupe that function in VBA as follows...

Dim c
For each c in Range("A1:A5")
c.Offset(0, 1) = c.NumberFormat
Next 'c

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Syntax for Cell type
 

"Tim Childs" wrote in message
Hi

In VBA I want to get the cell information on a cell e.g. in the
Application itself, I can put:
=CELL("type",A1)
in Cell B1
However, I would like to extract the same information about (multiple)
cells directly from VBA itself i.e. in a procedure, without entering a
formula into an Excel cell.
Please can you give me the VBA syntax for that.

CELL + "type" doesn't tell you much - the cell contains a string, is
empty, or anything else. Other VBA functions can tell you a lot more but
give some idea of what you're looking for and what you want to do with it.

Peter T

Syntax for Cell type
 

On 03-Mar-17 8:00 PM, Peter T wrote:
"Tim Childs" wrote in message
Hi

In VBA I want to get the cell information on a cell e.g. in the
Application itself, I can put:
=CELL("type",A1)
in Cell B1
However, I would like to extract the same information about (multiple)
cells directly from VBA itself i.e. in a procedure, without entering a
formula into an Excel cell.
Please can you give me the VBA syntax for that.

CELL + "type" doesn't tell you much - the cell contains a string, is
empty, or anything else. Other VBA functions can tell you a lot more but
give some idea of what you're looking for and what you want to do with it.

Peter T


Thanks for response

I want to distinguish between cells that have been output as labels and
those that are values. In the latter, dates are numbers and in the
former, I will use TextToColumns to convert the "date labels" to proper
date values. I hope that clarifies my request, if not, please do say so.

Tim

Syntax for Cell type
 

On 03-Mar-17 6:55 AM, GS wrote:
Dim c
For each c in Range("A1:A5")
c.Offset(0, 1) = c.NumberFormat
Next 'c

thanks although it is a characteristic of the cell that I want i.e. if
it is a label or value etc rather than the number format

Tim

Syntax for Cell type
 

"Tim Childs" wrote in message
...
On 03-Mar-17 8:00 PM, Peter T wrote:
"Tim Childs" wrote in message
Hi

In VBA I want to get the cell information on a cell e.g. in the
Application itself, I can put:
=CELL("type",A1)
in Cell B1
However, I would like to extract the same information about (multiple)
cells directly from VBA itself i.e. in a procedure, without entering a
formula into an Excel cell.
Please can you give me the VBA syntax for that.

CELL + "type" doesn't tell you much - the cell contains a string, is
empty, or anything else. Other VBA functions can tell you a lot more but
give some idea of what you're looking for and what you want to do with
it.

Peter T


Thanks for response

I want to distinguish between cells that have been output as labels and
those that are values. In the latter, dates are numbers and in the former,
I will use TextToColumns to convert the "date labels" to proper date
values. I hope that clarifies my request, if not, please do say so.

First thing to try is simply see if the string dates will coerce to date
values, if any 'numbers' return format as date.
=(A1)*1

and look for #VALUE! errors

If that doesn't fix them try this UDF

Function DataType(cel As Range)
Dim s As String
Select Case VarType(cel)
Case vbBoolean: s = "Boolean"
Case vbDate: s = "Date"
Case vbDouble: s = "Double"
Case vbEmpty: s = "Empty"
Case vbString: s = "String"
Case vbError: s = "Error"
Case Else: s = "other"
End Select

DataType = s
End Function

=DataType(A1)

You might also try F5, Special, Constants and//or Formulas and tick only the
Text

Peter T

Syntax for Cell type
 

hi Tim,

For more information about VarType Function like Peter to use,

https://msdn.microsoft.com/en-us/lib.../gg278470.aspx

Value Constant Description
0 vbEmpty Empty (uninitialized)
1 vbNull Null (no valid data)
2 vbInteger Integer
3 vbLong Long integer
4 vbSingle Single-precision floating-point number
5 vbDouble Double-precision floating-point number
6 vbCurrency Currency value
7 vbDate Date value
8 vbString String
9 vbObject Object
10 vbError Error value
11 vbBoolean Boolean value
12 vbVariant Variant (used only with arrays of variants)
13 vbDataObject A data access object
14 vbDecimal Decimal value
17 vbByte Byte value
20 vbLongLong LongLong integer (Valid on 64-bit platforms only.)
36 vbUserDefinedType Variants that contain user-defined types
8192 vbArray Array

isabelle

Le 2017-03-04 à 09:29, Peter T a écrit :

If that doesn't fix them try this UDF

Function DataType(cel As Range)
Dim s As String
Select Case VarType(cel)
Case vbBoolean: s = "Boolean"
Case vbDate: s = "Date"
Case vbDouble: s = "Double"
Case vbEmpty: s = "Empty"
Case vbString: s = "String"
Case vbError: s = "Error"
Case Else: s = "other"
End Select

DataType = s
End Function

=DataType(A1)

You might also try F5, Special, Constants and//or Formulas and tick only the
Text

Peter T

Syntax for Cell type
 

On 05-Mar-17 6:36 AM, isabelle wrote:
hi Tim,

For more information about VarType Function like Peter to use,

https://msdn.microsoft.com/en-us/lib.../gg278470.aspx

Value Constant Description
0 vbEmpty Empty (uninitialized)
1 vbNull Null (no valid data)
2 vbInteger Integer
3 vbLong Long integer
4 vbSingle Single-precision floating-point number
5 vbDouble Double-precision floating-point number
6 vbCurrency Currency value
7 vbDate Date value
8 vbString String
9 vbObject Object
10 vbError Error value
11 vbBoolean Boolean value
12 vbVariant Variant (used only with arrays of variants)
13 vbDataObject A data access object
14 vbDecimal Decimal value
17 vbByte Byte value
20 vbLongLong LongLong integer (Valid on 64-bit platforms only.)
36 vbUserDefinedType Variants that contain user-defined types
8192 vbArray Array

isabelle

Le 2017-03-04 à 09:29, Peter T a écrit :

If that doesn't fix them try this UDF

Function DataType(cel As Range)
Dim s As String
Select Case VarType(cel)
Case vbBoolean: s = "Boolean"
Case vbDate: s = "Date"
Case vbDouble: s = "Double"
Case vbEmpty: s = "Empty"
Case vbString: s = "String"
Case vbError: s = "Error"
Case Else: s = "other"
End Select

DataType = s
End Function

=DataType(A1)

You might also try F5, Special, Constants and//or Formulas and tick
only the
Text

Peter T



Hi Peter and Isabelle

Many thanks for the two posts which I will try out

Best wishes, Tim

Syntax for Cell type
 

"isabelle" wrote in message
hi Tim,

For more information about VarType Function like Peter to use,

I only inlcuded the 'vartypes' relevant for cells :)

Peter T