Free Microsoft Excel 2013 Quick Reference

VBA - Hex To ASCII

Group,
Is there a function in VBA that will convert hex into ascii. Example assume I have 0x39 and I want it to convert it to ascii, or 9. Same goes for the alpha characters as well A thru Z both lower case and upper case.

Tony


Post your answer or comment

comments powered by Disqus
I'm searching for possibly an add-in for converting hex to ASCII in Excel.

Hi,

We would like to convert the below hex file(in the first tab) to the attached output file(in the second tab) which is ascii. Could you please give us a macro which will convert the hex to ascci data in the below formats. This will be very helpful for us in troubleshooting an issue.

Hope someone can help.
I did an export of our users from Active Directory using CSVDE.exe. This generated a csv file which I then work with in Excel and word.
My problem is that any field that has either a carrige return, or an accent in the name come out as a long HEX string with an x' at the start and an ' at the end instead of the proper word.
For example Stephane (The e should have an accent, but I can't display that here) comes out as X'5374c3a97068616e65'.
What I need help with is a formula/macro/vba code, that would search for all cells whos data starts with x'. Once found I need to remove the x' from the begining and the ' from the end. Then pair each 2 characters of hex together and convert then to ascii. If we take the example above, X'5374c3a97068616e65' would become 5374c3a97068616e65, then 53 becomes S, 74 becomes t, c3 and a9 form e with an accent, 70 is p... and so on and so forth. Then once that's all done, put the converted name/title back into the orginal field.
I thank everyone in advance for any help they can give me with this.
If you need more information, examples, or know of a better way for me to do this, please feel free to let me know.
Thanks
James

Hi,

Wondering where this code can be adjusted to be much more efficient. Right now it is going through 1000 rows and 14 columns and it is taking about 10 minutes. I eventually need it to go through 6000 rows and 32 columns..

I already stored the HexToDec(Cells(x, 1)) result to variable H2D and it didn't really make too much of a noticeable difference.

Sub HexConversion()
Dim x As Integer, y As Integer
Dim H2D As Long

With Sheets("Floats")
    For x = 1 To 1000 'row number
       y = 11 'column K
       H2D = HexToDec(Cells(x, 1))
       Cells(x, y) = -(H2D > 32768)
       Cells(x, y + 1) = -((H2D - Cells(x, y) * 32768) >= 16384)
       Cells(x, y + 2) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384) >= 8192)
       Cells(x, y + 3) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192) >= 4096)
       Cells(x, y + 4) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) *
4096) >= 2048)
       Cells(x, y + 5) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) *
4096 - Cells(x, y + 3) * 2048) >= 1024)
       Cells(x, y + 6) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) *
4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024) >= 512)
       Cells(x, y + 7) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) *
4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512) >= 256)
       Cells(x, y + 8) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) *
4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512 - Cells(x, y + 6) * 256) >= 128)
       Cells(x, y + 9) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) *
4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512 - Cells(x, y + 6) * 256 - Cells(x, y + 7) *
128) >= 64)
       Cells(x, y + 10) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) *
4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512 - Cells(x, y + 6) * 256 - Cells(x, y + 7) *
128 - Cells(x, y + 8) * 64) >= 32)
       Cells(x, y + 11) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) *
4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512 - Cells(x, y + 6) * 256 - Cells(x, y + 7) *
128 - Cells(x, y + 8) * 64 - Cells(x, y + 9) * 32) >= 16)
       Cells(x, y + 12) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) *
4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512 - Cells(x, y + 6) * 256 - Cells(x, y + 7) *
128 - Cells(x, y + 8) * 64 - Cells(x, y + 9) * 32 - Cells(x, y + 10) * 16) >= 8)
       Cells(x, y + 13) = -((H2D - Cells(x, y) * 32768 - Cells(x, y + 1) * 16384 - Cells(x, y + 2) * 8192 - Cells(x, y + 3) *
4096 - Cells(x, y + 3) * 2048 - Cells(x, y + 4) * 1024 - Cells(x, y + 5) * 512 - Cells(x, y + 6) * 256 - Cells(x, y + 7) *
128 - Cells(x, y + 8) * 64 - Cells(x, y + 9) * 32 - Cells(x, y + 10) * 16 - Cells(x, y + 11) * 8) >= 4)
    Next x
End With
End Sub
I am using this Function to convert Hex to Dec since there is no VBA built in function, which I got from VBAExpress.com: http://www.vbaexpress.com/kb/getarticle.php?kb_id=307

Public Function HexToDec(Hex As String) As Double
     
    Dim i               As Long
    Dim j               As Variant
    Dim k               As Long
    Dim n               As Long
    Dim HexArray()      As Double
     
    n = Len(Hex)
    k = -1
    ReDim HexArray(1 To n)
    For i = n To 1 Step -1
        j = Mid(Hex, i, 1)
        k = k + 1
        Select Case j
        Case 0 To 9
            HexArray(i) = j * 16 ^ (k)
        Case Is = "A"
            HexArray(i) = 10 * 16 ^ (k)
        Case Is = "B"
            HexArray(i) = 11 * 16 ^ (k)
        Case Is = "C"
            HexArray(i) = 12 * 16 ^ (k)
        Case Is = "D"
            HexArray(i) = 13 * 16 ^ (k)
        Case Is = "E"
            HexArray(i) = 14 * 16 ^ (k)
        Case Is = "F"
            HexArray(i) = 15 * 16 ^ (k)
        End Select
    Next i
    HexToDec = Application.WorksheetFunction.Sum(HexArray)
     
End Function
Thanks

I'm a novice at using macros in excel and I need to convert 3 columns and 255 rows of decimal numbers into hexidecimal numbers. I've done the easy part and created buttons on my excel sheet, one button to convert to hex and the other to convert back to decimal. Can anyone help me with the VBA code to get these buttons working?

I am converting Hex to ieee754 floating point numbers using the following
attached code using VBA. Is this a 'fast' way to do this operation? My code
spends 90% of the time performing this operation. I'm hoping there is a
faster way. Any comments are apprectiate.

Thanks!
Mark

'To type cast in VB you need to use the API to copy the contents of one data
type into another....
'turns hex data into floating point ieee754 standard
Option Explicit
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory"
(Destination As Any, Source As Any, ByVal Length As Long)
Function Hex2Ieee754(b1, b2, b3, b4)

Dim bytArray(0 To 3) As Byte
Dim fResult As Single

' load your data (40 B8 00 00) into a byte array creates 5.75
' note the order is reversed

bytArray(3) = "&H" + b1
bytArray(2) = "&H" + b2
bytArray(1) = "&H" + b3
bytArray(0) = "&H" + b4

' copy into the float
CopyMemory fResult, bytArray(0), 4
' print the result (5.75)
'Debug.Print fResult
Hex2Ieee754 = fResult

End Function

Hi all,

I have a question that I am not sure there is an answer for - but hopefully someone may have come across this somewhere or has some knowledge.

I am trying to get excel to read serial data from a COMS port as ASCII. In this instance, a cheque reader which scans the MICR on the bottom of the cheque.

There are third party programs that will allow you to do this (many are even shareware) - but I need to try and find a method of having excel perform the function of reading the data from the port, and converting it to ASCII characters.

Has anyone ever come across an article/plug in or other interface with excel that will help? Also, can anyone comment on whether this may be possible with VBA?

Thanks in advance for the help.

Cheers

Rob

http://www.excelforum.com/excel-prog...data-sets.html

Hi All,

I have reported the above question in forum (excelforum.com) with the attachment.

Any help would be greatly appreciated.

Many Regards
Manav

Hi All,

Respected Members please accept my sincere thanks for all your esteemed help given through this channel.

I have attached a sample worksheet which I use in work. My aim is to find job nuumbers on sheet1 in the dataset on sheet 2(column A) and return the corresponding values iolumn B & C on sheet 2. Please note that one job number can be paid more than one time in the past months(eg. 3300023104), and therefore the requirement is to total the amount of money paid in all months, on a particular job reference. The output i require on sheet 1 is "month paid in" and "amount paid ". If a job is paid just once then simply get the month and amount on sheet 1. But i dont know if a paricular job like 3300023104 has been paid loads of times in several months, how can we list the months as well as the amount. If months is not possible then only amount can do...

My objective is to find out how much money has been paid before on the jobs mentioned on sheet 1 column A (my current month invoice). I already have a vba code to trace any duplicates within column A on sheet 1. For instance job ref 3300023104 has been repeated a few times on sheet 1.

Please advise at your convenience.

Thank you
Manav

I would like to know if there is a VBA code to help me sum up the values by date for the following fields (Begin, In, Out, Ending) based on the "Indicator" and "Type" criteria?

The details are in the attached spreadsheet. Thanks in advance for your help!

Hi,

1. I require VBA code to overwrite excel file in destination folder if exist.

2.
Actually my macro code first sort,save and close the file, then it copy and paste this excel file to destiny network drive.

All things are working here except copy and paste. I am able to copy and then move file to destiny but I want to paste it and not move.
Help in this regard really appreciated

Hi all,

Had a quick look and couldn't find anything like this that has been answered already.

I would like to do the following.

Background.

We have approx 30 technical claims handlers who each use a spreadsheet to record their individual caseloads with various dates etc. All these spreadsheets live in a communual folder on a network drive.
To assist the team manager I want to build something that will analyse each persons data for purposes of monthly 1 to 1's etc. That in itself is a doddle.
Due to Head Office and their silly rules I cannot do the obvious and add a worksheet in (don't even go into the why and why nots) so I plan on a standalone spreadsheet to copy across an individuals data and work things out there. (Again a doddle)
The team managers are not exactly excel savvy, so the idea is to automate as much as possibly using VBA and macro. What I plan is a dropdown box - they select the persons name, click a button and hey presto it all happens. (99% of that I can do)

The problem

I envisage a vlookup off the name in the dropdown box, the second column would contain the filepath and filename of that persons spreadsheet. So if I picked Josh I would get s:filepathjosh.xls

What I don't know how to do in VBA is to say "take the contents of cell C1, and use that as the name of the spreadsheet to open

e.g. Workbooks.Open Filename:="S:filepathjosh.xls."

what code do i put in so it reads C1 as the link and puts it in.

I hope that makes some kind of sense.

Thanks

Lynton

Thank you for this forum. This is my first question, so I appologize if it is not perfect. Please let me know if you need more info.

I am using Excel 2007.

I have a group of large databases that were misconfigured, and now I need to edit the formula's in each cell. There are more than 12,000 rows of 6 collumns each, on up to 11 sheets per workbook, and 8 workbooks that I need to fix. Within a workbook, there are up to 42 different formula's that need to be replaced. I know how to do this with the built in Find/Replace, however that is too much work, so I want to create a workbook level VBA macro to do this for me. The problem is that I am just beginning to learn how to create VBA macro's, so I don't really know how to do this.

Here is an example of what I need to do. On all sheets within the workbook, I first need to replace the formula in any cells that contain the formula "=A4" with the formula "=B1". Then I need to replace the formula in any cells containing formula "=A5" with formula "=C1". This continue's until formula "=A45" is replaced by formula "=AQ1". Please help me to create a "Sub FindAndReplaceFormula()"

Thank you for your kind assistance

JMDIGS

I seem to being having trouble copying the results I obtain from the Kickbutt VBA function to a new sheet. The code I have used should work but it gives me a runtime error '1004' The information cannot be pasted because the COpy area and the paste area are not the same size and shape. try one of the following: Click a single cell, and then paste. Or select a rectangle that's the same size an shape, and then paste.

Below is the code I have used. The results of the function call return an unknown amount of rows with 10 columns.


	VB:
	
 
     
    mon = month(DateValue("1-" & Target.Value & "-2000")) 
     
    Call Find_Range(mon, Sheets(6).Columns("A")).EntireRow.Copy(Sheets(3).Range("F10")) 
     
Else: Exit Sub 
     
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As you can see the destination I use is only one cell so it should paste with no problem but it doesn't. The code runs fine when I paste to any row in column A but once I paste to a different column it bugs out. I don't understand why it is creating these errors.

USD $10 by paypal vba code to delete rows in workbooks

Hi again brilliant forum

i am an excel novice

had some great work done by Wigi and Krishnu in the past

I have about 120 excel files (each has about 1 million rows and between 6 and 30 Columns).

the files are 200mb + so too big to upload

i want a vba macro to delete unwanted rows

is it possible to have a message or input box that i can enter up to 5 words and if any of these words are contained in any rows then i wish to keep these rows only (and if any of these words dont appear i wish to delete the whole row)

the macro will be deleting about 950 thousand rows and keeping about 50 thousand (hopefully).

i can do it very slowly with custom sort but if a macro can be done it will make life easier

Good luck if you fancy the challenge

Paul

How to Use a VBA Macro to Sum Only Visible Cells

For more information about creating custom functions in Microsoft Excel 97, from the Visual Basic Editor, click the Office Assistant, type function statement , click Search, and ...

Okay bear with me here, I know it seems like I’m trying to do excel-inception but I’m not. I’m relatively new to VBA so I’m trying to understand if this is possible. Can I write a vba code to be run upon the user pressing a button in a sheet that has yet to be created?

Excel sheets attached to explain.

The original sheet is titled “With code 1.xlsm”. The code is associated with the button “Click me” and once the button is clicked, sheets “1” and “2” are created (click the button to see).

What I would like is for, when sheets “1” and “2” are created, for them to be created with a button “Button 1” linked to another code as shown in file “With code 1 and 2.xlsm” which multiplies the age value by two (click the button to see)

Is this possible?

Thanks thanks ((:

VBA code to search for and delete Form Controls on an Excel 2007 spreadsheet

Hi,

I have IP addresses from cell A1 to cell A200 (this range will change from time to time). Is there a VBA macro to sort the IP addresses?

Thank you.

BUG: Cannot Add a Secured VBA Module to SourceSafe.
When adding a password protected Visual Basic for Applications (VBA) project to SourceSafe, you will see one of the following errors. The error you see depends on whether the VBA ...

I am looking for vba code to move data from one spreadsheet tab to another. I need it to copy based on greater than or equal to and less than or equal to a certain column. I saw other related threads with just specific values; I need a range of numbers.

Attached is an example of the before with the data download, and the after when I just manually move them over to each tab. The tabs noted GT100K or GT50K means in column I values over 100,000 or 50,000, etc.

So the code would pull based on column C or C & I from the data tab, and copy to the other tabs. After moved If you could sort the tabs by B&C too, that would be perfect. If this just pulls from the top row down, then you can ignore the prior sentence since it will be sorted before running the code.

I'm not sure if my files are working so here is kind of an brief example. If you can give me the base, maybe I could manipulate the vba for my specifics.

Example :
Account Var. Amount
45000 $105
45005 $50
56100 $25
79008 $125
67002 $25

The code would move the data (first 2 rows) in one tab for accounts 45000-56009. Then move to another tab for accounts 45000-56009 and variance amounts greater than $100 (just the first row). Move data from accounts 56000-56999 to a separate tab (copy the third row into new tab). Etc.

Let me know if you have any questions.
Thanks,
Preston

I am working on a VBA application for Excel that should work in Windows versions 2000 thru 2010, and Mac 2004.

In my dBase3+ and FoxBase+ days, I would create and store a line of code to a variable based on If...Else...EndIf conditions and then execute the code via the variable. For example:


	VB:
	
 County = "KERN" 
mIndex = "Index On Well_Number For County = "KERN"" 
Else 
    mIndex="" 'for do nothing
    EndIf 
     
     'the first character "&" means execute code stored in mIndex
    &mIndex 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can this be done in VBA to get around compile errors?

First example is for showing a Userform Modeless-Model which Mac Excel hates (ie, compile error)


	VB:
	
 
    mUserform = "Userform1.Show" 
Else 
    mUserform = "Userform1.Show vbModeless" 
End If 
 
&mUserform 'variable to execute the code in mUserform

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The second example is to deactivate the autosave and-or autobackup setting. Mac Excel hates ThisWorkbook.EnableAutoRecovery (compile error: method or data member not found)


	VB:
	
 
    mSaveSet = "Application.SaveInterval = 0" 
Else 
    If application.version = "9.0" Then 
        mSaveSet = "Application.AddIns("Autosave Add-in").Installed = False" 
    Else 
        mSaveSet = "ThisWorkbook.EnableAutoRecover = False" 
    End If 
End If 
 
&mSaveSet 'variable to execute the code in mSaveSet

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Again, I want to save VBA code to a variable based on condition(s) for later execution, and to avoid compiler errors

I have 2 workbooks, named Cross Sales Summary and Total Daily Sales. Currently I am updating both workbooks manually. Since the business is growing, it will be very tedious in near future. Therefore I would like to use the VBA coding to ease my burden.

I plan to have an "Upload Data" button in Total Daily Sales workbook, where once I click on it, it will prompt me to choose a file. Once selected the file (eg Cross Sales Summary), it will extract data from Cross Sales Summary and write it into Total Daily Sales workbook, according to branches. It has to be done on a daily basis except on Saturday and Sunday. Maybe it could extract data into the designated rows according to dates and branches?

Can anyone help me?

Thanks.

I need to solve a problem on my work and need help to start.

I am preparing a spredsheet that on columns, I am inserting numbers as texts (in order to show 0 (zero) before). They are tracking number. So, before create a huge list, those numbers could not be duplicated. My aim is to create a formula or a VBA code to check:
1 - When finalize to insert numbers advise that same number has already been used; or
2 - Create a macro that after pressed the bottom, check the ones that are duplicated.

The easier way would be whenever I type the numbers and changed cell, a popup appear saying that number has already been used.

Any ideas to help me?

I appreciate all attention, since I am not a expert programmer and I am fascinating for VBA on Excel.

I want to know is there any special function in Excel for converting the Decimal to ASCII character.

If yes what is that

Regards

Vishal


No luck finding an answer? You could always try Google.