Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default 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


  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9
Default 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
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Syntax for AutoCad File Type *.dwg Benjamin Excel Programming 2 July 7th 09 09:10 PM
cell reference syntax Fred Excel Worksheet Functions 2 December 16th 08 05:01 PM
VBA Import Convert Data Type from Type 1 to Type 2 u473 Excel Programming 3 October 21st 08 08:22 PM
cell value syntax miek Excel Programming 2 August 12th 08 09:19 PM
Worksheet().Range(Cell(), Cell()) syntax? Ouka[_6_] Excel Programming 10 July 27th 05 12:50 AM


All times are GMT +1. The time now is 11:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"