Code to obtain constant_numbers for xl types

zenigma

New Member
Joined
Oct 19, 2004
Messages
21
Ultimately I'm trying to go through a range of cell and attach to the left the corresponding return value. I've had some problems with the code in not getting what I want - so I inserted some Msgbox(s) for confirmation.
Code is as follows:

Sub FindCde()
'undernoted 3 lines are in range C3:C5
'xlLine
'xlLineMarkersStacked
'xlLineStacked
Dim cel As Range, Rng As Range
'Dim cde As Long 'causes error
Set Rng = Range("C3:C5")
For Each cel In Rng
cde = cel.Value
'expecting this msg to show "cell_ref" and "constant_number":
MsgBox cel & Space(10) & cde
'this message does return (as expected) "constant_numbers" for the undernoted types:
MsgBox xlLine & Space(5) & xlLineMarkersStacked & Space(5) & xlLineStacked
'ultimately next line should insert the "constant_number" in the cell to the left
'Range("cel").Offset(0, -1).Value = cde
Next
End Sub

Any help would be appreciated.
Thanks.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
and what goes wrong?

maybe changing cde from long to string or variant will work, or use:

cde = val(cel.Value)
 

zenigma

New Member
Joined
Oct 19, 2004
Messages
21
Thanks for your response Harvey,

The last line of code:
Range("cel").Offset(0, -1).Value = cde
produces an error.
Runtime error '1004': "Method 'Range' of object '_Global' failed"

In my attempt to correct this I commented it out and used the Msgbox feature to see what was being returned.

The second Msgbox (2): MsgBox xlLine & Space(5) & xlLineMarkersStacked & Space(5) & xlLineStacked
produces the information I want, namely: 4 66 63 the respetive return values for the xl types.

The first Msgbox (1): MsgBox cel & Space(10) & cde
produces: xlLine xlLine
when run with my code and the subsequent iterations produce the same results for the other items in the list. I've only shown the first three here out of a list of around 70 for this xl type.
What I'm expecting from this Msgbox is the 'cell reference' followed by the 'numerical value' the results of which I have established.

When I make your suggested change to the code the MsgBox(1)
produces: xlLine 0
while the first item changes in accordance with the list the second is always 0

Maybe this throws more light on the problem,
Thanks.

Edit:
Now managed to get the Cell Reference showing in Msgbox(1) by using cel.Address
Also changed: 'Range("cel").Offset(0, -1).Value = cde
To: cel.Offset(0, -1).Value = cde
but this just repeats the xl type as shown in cell to right.
Still not getting cde to show numeric value
 

zenigma

New Member
Joined
Oct 19, 2004
Messages
21
To recap on above:

Msgbox xlLine: 'produces 4
num = xlLine: Msgbox num: 'produces 4
however,
where cel represents the cell range containing xlLine
Msgbox cel.Address & space(5) & cel
produces $C$3 ....... xlLine : I want numeric value in place of xlLine
tried cel.value but same result.

Hoping someone can explain what I'm doing wrong.
Thanks.
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953

ADVERTISEMENT

Hi,

I understand now what you are trying to do.
The xlLine is a constant variable. What you are trying to do is to convert a string with "xlLine" to the value of the constant xlLine, which is 4.

What you can do is use a select case statement:
This example uses just one cell, but I am sure you can implement it in your code.
Code:
DIM cde AS STRING

cde = RANGE("C3").VALUE
SELECT CASE cde
  CASE "xlLine":
    cde = xlLine
  CASE "xlSquare":
    cde = xlSquare
  'insert the other options here
END SELECT

MSGBOX (cde)
 

zenigma

New Member
Joined
Oct 19, 2004
Messages
21
Thanks Harvey,
but the code you've given doesn't help. I know I can get the value from cde = xlLine but I don't want to have to input all the data into a vba procedure. I have the information on a spreadsheet and I want to build that into a table. I already have quite an array of xl types and values and Im looking for a quick way to convert some others including vbext....

From my spreadsheet I will be able to build functions very quickly like:

Function xlChTyp(cde)
Select Case cde
Case 4: xlChTyp = " Line"
Case 66: xlChTyp = " Stacked Line with Markers"
Case 63: xlChTyp = " Stacked Line"
Case n: xlChTyp = " Pie"
Case n: xlChTyp = " Pie of Pie"
Case n: xlChTyp = " Stacked Pyramid Bar"
End Select
End Function

to use in my vba procedures. Now I know that I can substitute these numbers with the strings but I prefer to have the returned codes, as I think it looks a bit neater.

Thanks again for your efforts.

Edit:
Perhaps what I am trying to do is not possible?
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953

ADVERTISEMENT

I guess there should be some function for evaluating a string as code. Unfortunately, I can't find it (evaluate only executes worksheet formula and is of no use in this case)
 

zenigma

New Member
Joined
Oct 19, 2004
Messages
21
Harvey,
What you posted got me thinking, it was an additional step, but from my spreadsheet I built the undernoted function:

Function xlChTypNo(cde)
Select Case cde
Case "xlLine": xlChTypNo = xlLine
Case "xlLineMarkersStacked": xlChTypNo = xlLineMarkersStacked
Case "xlLineStacked": xlChTypNo = xlLineStacked
Case "xlPie": xlChTypNo = xlPie
Case "xlPieOfPie": xlChTypNo = xlPieOfPie
Case "xlPyramidBarStacked": xlChTypNo = xlPyramidBarStacked
End Select
End Function

There are actually 73 types but I've only shown 6
Then ran my adjusted code:

Sub FindCde()
Dim cel As Range, Rng As Range
Dim cde As String
'Listing of xlChartType(s) in range c3:c75
Set Rng = Range("C3:C75")
For Each cel In Rng
cde = cel
cel.Offset(0, -1) = xlChTypNo(cel.Value)
Next
End Sub

It returned the codes I wanted to the adjacent cell. So works a treat.

Thanks again for your input.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,990
Messages
5,545,370
Members
410,679
Latest member
rolandbianco
Top