in VBA how to retreive cell values 1 byte at a time?

How would I write VBA to retrieve (get at) the value in a cell byte by
byte from its natural form? i.e. no conversions are to be performed.
E.g., if a string, then I may get more bytes than for a number.

Thanks.

0
d_penny (3)
1/28/2005 8:12:36 PM
excel 39879 articles. 2 followers. Follow

3 Replies
456 Views

Similar Articles

[PageSpeed] 25

Penny,

If the cell contains text, you can retrieve characters (bytes) with the MID 
function.
=MID(A2, CharPosition, 1).  You can get the actual byte values with the CODE 
function
=CODE(MID(A2, CharPosition, 1)).


If a number, you can extract the decimal digits of the number in the same 
way, but you're not getting actual bytes (as stored by Excel).  Excel stores 
numbers in an IEEE floating point standard format, the IEEE number of which 
I can't recall at the moment.  All such numbers use 8 bytes for storage in 
that format.  If you want the ASCII code values for the decimal digits of 
the number, use the formula above, as with text.



-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

<d_penny@usa.net> wrote in message 
news:1106943156.716897.8170@c13g2000cwb.googlegroups.com...
> How would I write VBA to retrieve (get at) the value in a cell byte by
> byte from its natural form? i.e. no conversions are to be performed.
> E.g., if a string, then I may get more bytes than for a number.
>
> Thanks.
> 


0
nothanks4548 (968)
1/28/2005 10:16:39 PM
Dim myVal

    For Each cell In Selection
        myVal= cell.Text
    Next cell

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


<d_penny@usa.net> wrote in message
news:1106943156.716897.8170@c13g2000cwb.googlegroups.com...
> How would I write VBA to retrieve (get at) the value in a cell byte by
> byte from its natural form? i.e. no conversions are to be performed.
> E.g., if a string, then I may get more bytes than for a number.
>
> Thanks.
>


0
bob.phillips1 (6510)
1/29/2005 12:44:23 AM
Penny,

Oops.  You said you wanted to do this in VBA.

For a character:
Mid(Range("A2"), CharPosition, 1)

For the ASCII code value:
Asc(Mid(Range("A2"), CharPosition, 1))
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Earl Kiosterud" <nothanks@nospam.com> wrote in message 
news:%23$RYOcYBFHA.4028@TK2MSFTNGP15.phx.gbl...
> Penny,
>
> If the cell contains text, you can retrieve characters (bytes) with the 
> MID function.
> =MID(A2, CharPosition, 1).  You can get the actual byte values with the 
> CODE function
> =CODE(MID(A2, CharPosition, 1)).
>
>
> If a number, you can extract the decimal digits of the number in the same 
> way, but you're not getting actual bytes (as stored by Excel).  Excel 
> stores numbers in an IEEE floating point standard format, the IEEE number 
> of which I can't recall at the moment.  All such numbers use 8 bytes for 
> storage in that format.  If you want the ASCII code values for the decimal 
> digits of the number, use the formula above, as with text.
>
>
>
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> <d_penny@usa.net> wrote in message 
> news:1106943156.716897.8170@c13g2000cwb.googlegroups.com...
>> How would I write VBA to retrieve (get at) the value in a cell byte by
>> byte from its natural form? i.e. no conversions are to be performed.
>> E.g., if a string, then I may get more bytes than for a number.
>>
>> Thanks.
>>
>
> 


0
nothanks4548 (968)
1/29/2005 1:24:30 AM
Reply:

Similar Artilces:

AutoUpdate Not Seeing 12.0.1
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Is anyone having the problem where AutoUpdate does not see Office 2008 Update 12.0.1? I've run it manually, and it shows no needed updates. I've also, manually downloaded the AutoUpdate 2.1.1 update and applied it manually again, but still no updates available. I'm a little at a loss. On 3/11/08 10:16 PM, in article ee93612.-1@webcrossing.caR9absDaxw, "Zerocool@officeformac.com" <Zerocool@officeformac.com> wrote: > Version: 2008 > Operating System: Mac OS X 10.5 (Leopard) > Process...

default appointment times problems
When i re-open outlook and go to the calender, all the appointments times have changed to 1am on the start day - 1am on the day after it waqs meant to finish. So a 12 day appointment is now shown as over 2 days. How do you change the default appointment time settings so an appointment which is just typed into the day is from 9am - 5pm for example and will stay as a 1 day event when opening and closing outlook? All day events are 12 -12, not 9-5. If you want it 9-5, you need to make it for 9-5. All day appointment change to 1 -1 if you change the time zone or DST settings aft...

Show which cell has MAX, MIN values?
At the bottom of a couple thousand rows of data, I have =MAX and =MIN formulas. Is there some way I could make the cells beneath my MAX and MIN formulas show me the address of which cell has the displayed MAX or MIN value? At least the row number? Ed Ed, To return the row =MATCH(cell with Max or Min value,range starting in row 1,false) or to return the address, say, in Cell N3000, for a value given in N2999 =ADDRESS(MATCH(N2999,N1:NN2998,FALSE),COLUMN(N1)) or to return other matching information, like a name in column A =INDEX(A:A,MATCH(N2999,N1:NN2998,FALSE)) HTH, Bernie MS E...

Most logical value
I would like to know if there is a method of determining "the most logical" value in a table. These are some data that I put in the table below. These numbers are obtained from some dedicated software to compute the influence of an investment on some projects. I would like to fill in the gaps myself. I can add the data from column 10 en 20 and divide it by 2 to compute the value for column 15, but is there a better method? investment year 0 5 10 15 20 25 30 2005 72,8 72,8 72,8 72,8 72,8 72,8 72,8 2006 79,4 76,7 71,5 60,9 50,3 2007 92,3 87,4 76,9 55,6 33,5 2008 108,6 102,3 8...

Want use results of Drop down list to look up a value
I am using Excel 2002 and I have a multisheet workbook and on the first sheet I have created a series of 6 drop down lists pulling from predefined named ranges. I also have limited subsequent choices based on what is selected in the other lists. The end result is the user will be selecting parameters that identify a product and now I am stuck with an easy way to look up the associated part number based on the parameters they selected. Essentially, once they have chosen the parameters in the list, I would like to create a macro that uses those values to locate the associated part...

100% Stacked Bar chart, two bars, want percent as well as real value
I am attempting to chart the following: Series 1 - Values over the prior 30 days Series 2 - Values over the prior 12 months I would like to show both data series as 100% bar charts, so users of the information can compare percentage of the last 30 days with percentage of the same piece of data over the last 12 months. So, for example the two data series might look like this: Series 1 20 25 45 65 Series 2 211 324 403 822 I want to display both the actual value, and the percentage of the total, for each series, in the labels. For some reason, showing percentage is not an option. Why is th...

Problem with vba directory function
Hi We have an access application that has a button when clicked opens an excel worksheet showing all exported data based on code The access sql system is in Citrix with a single front end copy. Here is the code part that is throwing the error for just one user. Rest all are working fine Dim strSourceFileName As String Dim WorkBookName As String strSourceFileName = "c:\CarLogOutPut\CarLogExportTemplate.xls" WorkBookName = "c:\CarLogOutPut\CarLogExport.xls" If Dir(strSourceFileName) = "" Then MsgBox "CarLogExportTemplate.xls doe...

IIS OWA HTTP/1.1 503 Service Unavailable
We rebooted the Windows 2000 server. Now when we try to access OWA, after the log on screen we get the error page ""HTTP/1.1 503 Service Unavailable". IIS 5 was restarted but we still get this error. I tried to create another virtual HTTP server in Exchange to make another web site but again we get the "HTTP/1.1 503 Service Unavailable". Do you know what we can do to fix this? Here is the fix!! http://support.microsoft.com/default.aspx?scid=kb;en- us;883380 >-----Original Message----- >We rebooted the Windows 2000 server. Now when we try to >ac...

require cell completion in a form?
I have a very simple form in Excel. I'm not sure if it matters, but the machine I'm on uses Excel 2002, and most of the users are on Excel 2000. The first 2 questions on my form require that the user select from a list of names, and then select their relationship to that person. Then the users answer a series of questions about this person's job performance. I'm using data validation to manage the two drop-down lists - the names and the relationships. However, about a third of my users end up leaving one or both of these cells blank. I have a comment box set up as a remin...

Default numeric value
I am creating a spreadsheet dealing with dollars. There are several formulas that I have created using percentages. One particular formula multiplies a percent in one cell by the $ value in another. If I have no $ value in the cell the formula uses a '1' instead of what I would expect '0'. How can I set up my sheet so that if a cell is blank the default value will be '0'? Would help to see your formula, but: =if(isblank(a1),"",a1*b1) ************ Anne Troy VBA Project Manager www.OfficeArticles.com "thomson" <thomson@discussions.microsof...

Last cell with data in a range
I enter weekly data into a spreadsheet with a summary page at the front. After every week, when new data is entered, I want the formula at the front to use the cell with the new data instead of me having to change the existing formula. For example, my data looks like this: A B C 276 300 421 175 0 0 0 I need a formula that will automatically detect the last number >0 in column C. Thank you for your assistance! =Countif(C2:C1000,">0") so your main page formula might be something like: =...

Average range of discontinuous cells
I have a block of columns (P-CT) that I need to average but only every 4th one. Example: I need to average P, T, X, AB, AF, AJ, AN, AR, AV, AZ, BD, BH, BL, BP, BT, BX, CB, CF, CJ, CN, CR. If I just type =AVERAGE(selecting these cells) it gives me 40238 when the only one with a value is cell P. These cells contain dates if anything. They are payment dates. I need the average number of dates it took to pay something. Any ideas for Excel 2003? Thanks. Never mind. I forgot to include my beginning date. It works. Just user error. "Nadine" wrote: > ...

time calculation
I need to do a check on the time.. if it is after midnight but before 5am then I need to minus 1 day off of the date. How could I do this? Hi If the Date and Time is in cell A1, use =IF(AND(MOD(A1,1)>VALUE("12:00 AM"),MOD(A1,1)<VALUE("05:00 AM")),A1+1,A1) ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** ...shortened versions would be =A1+AND(MOD(A1,1)>VALUE("12:00 AM"),MOD(A1,1)<VALUE("05:00 AM")) ** Posted via: http://www.ozgrid...

Excel macros page breaks but not on row 1 and column value in footer
Hi, I have seen some posts that are similar, but no one seems to have the problem that I am having. I will post my macro in this, for anyone that is interested. My users get a csv file every month, and we have to clean it up. This macro does that. My last issues are this: 1) having the spreadsheet create page breaks whenever the value in column B changes. Below is just that code. Code: col = 2 LastRw = ActiveSheet.UsedRange.Rows.Count For X = 2 To LastRw If Cells(X, col) <> Cells(X - 1, col) And Cells(X, col) <> Range("B1") Then ActiveWindow.SelectedSheets.HPageBreaks...

RMS should allow you to choose more than one item at a time
RMS should allow you to choose more than one item at a time, this would be helpful in creating PO's. Mutiple item selection is a standard in every software; you really dropped the ball with this one. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.micr...

display cell value in msgbox formatted as %
Hi I have been trying to come up with a way to display a cell value in a msgbox so that it formats properly as a percent. I have tried: Productivity = Format(Range("A1").Value, "###,# %") Msgbox Productivity This always gives me a leading 0 (e.g 015%) and I want it to display 15.0%. So I tried this: Productivity = Format(CStr(Range("A1").Value) * 100, "#,###.0") & "%" and it works ok but... I want to use the value of productivity in computations - which I can't formatted as a string ... Am I missing something - or is it as simple...

Can multiple cell results be displayed in a single cell?
Is it possible to display the contents of multiple separate cells in another single cell? If the results of several cells say B1, B2, and B3 are the amounts $10, $15 and $20, I would like to display them in cell A1 as follows: $10 $15 $20 I could do this statically by using text and the 'Alt|Enter' function within the cell. However, these amounts will change from time to time so I'd like a link. Is this possible or not? Thanks for any help. Brett Brett One way: In A1: ="$"&B1&CHAR(10)&"$"&B2&CHAR(10)&"$"&B3&am...

Using color to ignore cells in a formula?????
Is it possible to set up a formula in excel to ingore certain cells if you should change the color of that cell to a color. Example: =sum(a1:a50) if cell A25 was blue how can I ignore it so that it is not calculated? Mike A. Hi only with VBA. See: http://www.cpearson.com/excel/colors.htm and http://www.xldynamic.com/source/xld.ColourCounter.html -- Regards Frank Kabel Frankfurt, Germany "Mike A." <MikeA@discussions.microsoft.com> schrieb im Newsbeitrag news:65C99320-9FE3-4E11-81C2-C262B82B565B@microsoft.com... > Is it possible to set up a formula in excel to ingore cer...

Appending data in cells that utilize a Dropdown list.
Hello all, I would like to append entries in cells that utilize a dropdown list. Currently, when I select (in this case names) from my dropdown list I cannot append them if I want to add more than one to a cell. If I select another name from the dropdown, it erases the first entry. Is there some way to append entries within cells that use this form of validation so that I can enter more than one in a cell from the dd list? -- Thanks, Geek using Office XP Professional Hi Geek Debra Dalgleish has an example workbook showing how to do this http://www.contextures.com/excelfiles.html...

Worksheet Change Event log for multiple cells
Dear All, I have a spreadsheet that is available here: http://www.filefactory.com/file/b02e5h4/n/Worksheet_change_event.zip (although I scanned it for viruses please make sure you do it again prior to opening it as I cannot guarantee it's worm free). I would like to create a log file in an additional sheet (hidden probably) that would record every activity from column E after clicking a button assigned to a cell in that column and show these records in a worksheet Totals in a specific row. For example: Column A from a worksheet „Totals” corresponds with column A in ...

getting max value
select TrainDetails.ch_TrainDetailsID_pk, TrainDetails.ch_TrainDetailsID_fk, TrainDetails.ch_CurrentYrNo, TrainDetails.ch_CurrentWkNo, TrainDetails.ch_PlanYr, TrainDetails.ch_WON_WkNo, TrainDetails.ch_PPSwrksiteTSR_ref, TrainDetails.ch_NROL_PTO_ref, TrainDetails.ch_VehicleType, TrainDetails.ch_TrainID, TrainDetails.ch_RunDate from traindetails GROUP BY TrainDetails.ch_TrainDetailsID_pk having ch_currentWkNo > max(ch_currentWkNo) - 3; Hi all I'm trying to output data out by comparing the current week no (which is an integer type not a date type) with the maximum week n...

Excel: When printing some cells will not print text in them
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) When I print an Excel 08 spread sheet, two of the cells that have text in them will not print the text in the cell. All the other cells with text prints fine. The text does not even show up on print preview in the print dialogue box. Any suggestions on how to get it to include the text in these two cells? ...

Find Future Value of a Savings Account
I am trying to set up a spreadsheet that determines the future value of a savings account. Take a look at the FV function in XL Help. In article <EC8EEB72-AF22-47D8-A266-839CA9356725@microsoft.com>, "gingerjane" <gingerjane@discussions.microsoft.com> wrote: > I am trying to set up a spreadsheet that determines the future value of a > savings account. ...

#N/A Values : Returned by Formulas vs Entered Manually
Hello; When some cells of the data series have #N/A values returned by formulas, the corresponding chart fails. But if the #N/A values are manually entered into those same cells, the chart works fine !! To my understanding, Excel Charts treat cells with #N/A values as empty cells, so one may select the relevant chart option to "leave gaps", which is perfect. With this apparent different interpretation (by Excel Chart) of the same #N/A values in the data series, how can I make the #N/A values returned by formulas acceptable by the chart ?? Thank you for your help. The #N/A ...

Connecting Cell with Lines
I have seen MSExcel printouts that have dotted or solid lines of different colors connecting different cells in a sheet. Is that something that can be done in MSExcel or would they have been added outside of MSExcel? If it can be done in MSExcel, how? In article <QbYYg.13615$GR.1871@newssvr29.news.prodigy.net>, "jerry" <jerryc314@sbcglobal.net> wrote: >I have seen MSExcel printouts that have dotted or solid lines of different >colors connecting different cells in a sheet. Is that something that can be >done in MSExcel or would they have been added outside of...