I have a function that will be used across 10K+ records at a time, so I was taking a look at optimizing it a bit. At the start of the routing I have a string with a 9 to 15 digit numeric value in it. I wanted to load each individual digit into a byte array. I started out using:
byVal = StrConv(sVal,vbFromUnicode)
But that actually loads the ASCII Character codes into the array not the digits themselves. From there I considered looping through the array applying CByte(Chr$(byVal(i))) but then I wondered if it would make more sense to just load the array with Mid$(sVal,i,1)... Does anyone have tested knowledge on which performs better? Is there an option C I have missed?
I am trying to make an encode/decode function to use in excel from base64 to array of bytes
I want to put string encoded in base64 in cell1 and get result in cell2 with decoded array of bytes and vice versa i find some codes [URL]....
and a code here
[URL]....
using the code above to convert A1 A1 = a03060fa564665 ENCODEBASE64(A1) -> YTAzMDYwZmE1NjQ2NjU= (which is correct result for string "a03060fa564665" not the array of bytes) while correct result i need is oDBg+lZGZQ==
i know the issue is converting string to array of bytes but i couldn't fix it myself
example if cell1= a03060fa564665 result1= oDBg+lZGZQ== if cell2= oDBgm1BGaQ== result2= a030609b504669
I have a byte array that contains a 4 byte floating point number. How would I convert this byte array to the single typed floating point number it contains?
I am trying to do a sort of index match thing using VBA. What I am attempting to do is to use the prefix of a long number and try to find that exact prefix in a string array, and output that string array value. So this is what I have so far as a test:
[Code].....
So I can match the text exactly so if I put PREFIXB in cell A1 in this example, i will get the msg box saying "YES", but if I make it PREFIXB1231k4j3jj1kj32cj, it will display "NO". I want to get it so that PREFIXB will be displayed in the cell that I put the formula in. So if A1 = "PREFIX1AAA100CF" and cell B1 = "=ABC(A1)", cell B1 will display "PREFIX1AAA".
Now the thing is that these prefixes can have different lengths, but will never encompass the exact prefix of another. So if I had a prefix of: PRE1AB, I won't have a prefix of PRE1A.
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
in C a string is nothing more than an Array of characters ending with a null character.
in VBA this does not seem to be the case.I am trying to use the BlowFish code from David Midkiff for some encryption, however the code sometimes fails:
When encrypting a string a string of a specific length should be returned. however sometimes one of the characters of the encrypted string is a null character, and the returned encrypted string (with a embedded null character) is causing me problems. I want to check for the null character and if present redo the encryption. But how do I check for the presence of this null character in a unicode (double-byte) string?
Checking with Len(encrypted) gives the correct length (!) but trying to move each (unicode)character into an array fails when using the Mid() function past the null character in the string.
Now I can use
byteArray() = StrConv(unicodetext,vbFromUnicode)
to get the lower byte of each character into an array easily, but how do I do this for both bytes of the unicode characters?
Using an integer array does not work other than through
intArray(j) = CInt(AscW(Mid(Outp, j, 1)))
which fails on the nullstring in my encrypted text.
I have tried with variants but so far no luck. I thought if the string was entered into a variant then I could also address the variant as an array of bytes, but that does not seem to be accepted by VBA.
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
I'm currently trying to copy a dynamic range based on the row number of a selected listbox item. I'm able to get the row number of the selected listbox item from the data tab in my file. I, however, am stuck when it comes to copying a fixed column / varying row range from this tab and copying it over to another tab.
Private Sub CommandButton2_Click() Dim ID As Long Dim Row As Long Dim Cnt As Long Dim SearchTermsStr As String Dim SearchTermsRng As Range
[Code] ....
SearchTermsRng is returing "Nothing" but the SearchTermsStr returns "F3:O3"
I have a problem in converting text to columns for a string that has 1000+ entry's. Excel bumps me out after 851 using the Split function.
The data I can only download in a text format has >1000 fields that are seperated by ",". Not a problem so far but when I attempt to convert to columns there is a maximum limitation of 851.
I have a set of letters in a single row DISATAGFOORCBORDQFINFCOBBKIEHDSMCNRSKARDFCJSLCQCECFVSBCBOYKPVYKC. I want to distibute them across 5 columns such that each letter occupies one cell. I might want to specify a different column width later on so the solution should be flexible.
I am trying to check my sheet names to see if they convert to a valid date format. If it doesn't, then I want it to check for the next criteria (ex - if it equals "Page2"). With the code below I keep getting an error of type mismatch. Code is below.
I need to be able to locate a date in a spreadsheet bassed uppon a user input date. I am looking for the week that contains the user's date. However it seems even if I initialize the value as Date, it still reads the user's input as a string.
Is there an easy way make sure the system automaticaly recognizes the user's input as a date, or is there a way to quicly convert the string to a date?
I have a spreadsheet that has 3 different dates in 3 different cells:
B4 - 3/24/2014 B5 - 4/6/2014 B6 - 4/14/2014
From that information I need once cell to display "Mar 24 - Apr 6 paid Apr 14"
Reason being is that I am importing journal entries into an accounting program and I am allowed one cell each row for the description. This would let me change the dates only and my descriptions will fill out before import. It is important that the months are abbreviated to 3 letters as I am limited on how many characters the description can be.
I have hit an issue when tranfering a string date from VBA to excel, excel thinks the string is US format and swaps around the days and months even though the system and excel are setup for UK date format:
Simple example of the problem:
Sub test() Dim testy As String testy = Now 'testy will equal 04/10/2007 17:20 Range("A1").Value = testy 'A1 will equal 10/04/2007 17:20 End Sub
Now I know in this example that if I used DateValue(testy) it would work, but our situation is a bit more complex as we are running this is a loop testy is not always a date.
I have an ACD program that gives me a time in "mm:ss" format, or in ":ss" fomat. I have 2-3 problems with charting this data.
1 I am inheriting the production of charts from someone else, with old data being displayed by seconds only I am supposed to keep this display.
2 the data that is in mm:ss format is recognized as being in full date format, for example 2:14 is recognized as 2:14:00 AM instead of 2 minutes, 14 seconds.
3 the data that is in :ss, ie :44, is recognized as a string.
Any thoughts on how I can convert both cases to a integer seconds? I know for example that I could mid() the second condition, but I am stuck thinking about the first condition.
I can't adjust the way the time comes in, unfortunately. It is an auto-generated export of the data from a switch.
I have a userform that reads in a list of names into a combobox. When you choose a name from the combobox it then shows the that persons details from a sheet containing all the names.
I have some text boxes that allows the data to be modified and returns it to the sheet which works fine. What i also want it to do it put the data from the form into a sheet for that individual. For example the name of Ford would go into the Ford worksheet.
How do i read in the value from the combobox on the worksheet and use it as a variable to use in my code. For example
Sheets(Value from the combobox).Cells(NoOfRows, 3).Value = Me.TextBoxChangePosistion.Value
My question is about converting a Literal String to Character Code. I'm using the following coded InputBox. And it prompts the user for what characters to search for in a string.
I have a cell that contains parts that are comma separated. I want to assign each of these parts to an element in an array so I can process them using a loop function.
we would use a byte data type as a counter in a For Loop, but just today I have been having trouble with this. When I try to count backwards with " Step -1" I get an "error 6: overflow" on the FOR LOOP line. When I count forwards it works fine.
I recieve a spreadsheet each month with the following column of data:
9 Paddington House PO Box 73 Whiteleys of Bayswater 9-10 Grove Road 134/138 Drymen Road 44-46 The High Street 1 Midland Road 48 Donegall Place
As you can see, it consists of addresses, each line is a separate address. I need it to be converted so that the data is split into the following 5 fields:
Street number from street number letter from street number to street number letter to street
If there is no street number , then it should just enter the data into the street field. If there is a range such as 44-46, it should enter the 2 numbers into the from and to fields. Sometimes this is in the format 44/46. Also, sometimes there are letters, eg 44a-44b, in which case each section (ie 44, a, 44, b) needs to be in a spearate field.
I have a string of data coming from a SQL Server data connection into my workbook. The value in the column is a text string that is pipe delimited. I need a macro to parse the data from that column into applicable separate columns. I would typically use text to columns for this and parse it out manually... but the tool I'm creating is one of the automated variety, so that will not suffice for this application. I need it to do this automatically when the data connection refreshes.
U:U AQ AR AS AT AU AV to Produce|Fruit|apple|banana|cherry|date Produce Fruit apple banana cherry date
I have list of strings to be searched in column A2 to A150 (A1 has column title). The array that needs to be searched is in B2 to AG1000 (B1 to AG1 has column title). I want each string in column A to be searched in the full array. If match is found the corresponding column title (B1 or C1 and so.) and cell address needs to written to a new result columns in AY and AZ. it should work for duplicates as well i.e. the string can be in all the columns of array from B to AG. and all of them needs to be written to result column.
I'm trying to verify if a cellvalue is available in an array. Here is the code:
First I tried to check if the cellval is in the array by returning a -1, 0 or 1:
Code: If UBound(txt, cellval) = 0 Then -> returns zero for value "h3", but also for value "3" (where cellval the value in the cell is) (with txt being the array containing "h3,r8,ty657", etc)
But I need only the If to be true when there is an exact match.
