Free Microsoft Excel 2013 Quick Reference

Binary Counter

I need to determine the posible outcomes of 4 football games. There are 2^4 (16) possible combinations which can be represented by the binary numbers 0000 to 1111. I need to cycle though each possible outcome to determine final standings. I know how to do this if I can come up with a sub that counts through those bianary numbers.

Any ideas?

Thanks in advance,

Tom


Have a small project where management, using third party software storing the data on SQLServ 2005, were storing files on a shared drive and recording path/filename in the database. I have an Excel VBA workbook that queries the Server for a list of filenames (at present 55+, soon to grow over 300), opens them all in turn, parses, and copies the data into one sheet for presentation to management. This is all working quite nicely.

Now management are (for security reasons) forcing the software to store the files internally for version control, and thus I can't read from the source documents anymore. The files are now stored in a Table as binary data. I can guarantee that all files stored are .xls, but does anyone know a way in either T-SQL or Excel VBA that I can extract these and either open, them or at worst save temporary copies?

I can generate the correct queries, picking up filesize, origninal path and name, but the code breaks when it attempts to display the 'binary' data. Ideally I'd love a ReadBinaryDataAndOpenAsWorkbook(MySqlQuery String) Method but any suggestions would certainly help.

Brad

(Original Read and Display Code Below (Edited for Security))

Additional Referencing;
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ActiveX Data Objects Recordset 2.8 Library

Code:
Public Sub DataExtract()
  ' Create a connection object.
  Dim cnPubs As ADODB.Connection
  Set cnPubs = New ADODB.Connection
  strConn = "PROVIDER=;Server=;Database=;INTEGRATED SECURITY=;"
  
  'Now open the connection.
  cnPubs.Open strConn
  
  ' Create a recordset object.
  Dim rsPubs As ADODB.Recordset
  Set rsPubs = New ADODB.Recordset

  With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
    .Open "SELECT path, filename FROM..."
    ' Copy the records into cell A1 on Sheet1.
    Sheet2.UsedRange.Clear
    Sheet2.Range("A1").CopyFromRecordset rsPubs
        
    .Close
  End With

  cnPubs.Close
  Set rsPubs = Nothing
  Set cnPubs = Nothing

End Sub

Code:
Sub Files2File()
  '
  Dim varFilenames As Variant
  Dim strActiveBook As String
  Dim strSourceDataFile As String
  Dim strTest As String
  Dim wSht As Worksheet
  Dim allwShts As Sheets
  Dim counter As Integer
  Dim lRows As Long
  Dim newSheet As Integer
  
    Workbooks.Add
    strActiveBook = ActiveWorkbook.Name
    
    ' Create array of filenames; the True is for multi-select
    On Error GoTo exitsub
    varFilenames = Application.GetOpenFilename(, , , , True)

    counter = 1

    ' ubound determines how many items in the array
    On Error GoTo quit
    Sheets.Add
    
    Application.ScreenUpdating = False
    While counter

I'm using an inventory tracking spreadsheet and I wanted to create a button with a macro which allows me to add 1 to the "Qty_Sold" column every time I press the button (running tally). I'm using this code:

Sub Count()
mycount = Range("a1") + 1
Range("a1") = mycount
End Sub

I could make a macro with an individual button for every entry I have, but that seems redundant and counter productive. I just want to know if I can write it so that I can choose which cell I want to add +1 to without changing the range in the code for every inventory item.

Hi all,

Looking for a way to output all the possible combinations of binary values.

Example:

A1:Z1 can contain a 0 or a 1...26 total options

I want to know all the possible combinations that can be created with a restriction of no more than x (a variable amount) of "1"s available. I.e how many combinations are possible with no more than 8 cells from A1:Z1 having a value of 1.

Thanks!

I am trying to seek and read specific bytes (1 byte at a time at specific locations in the file) from 30GB+ binary files in Excel 2007 (VBA). I have been successful with files < 2GB and I know about the Long limit. I have also read the Microsoft article (http://support.microsoft.com/kb/189981) dealing with this, but I don't quite understand it.

This works fine for files < 2GB and returns the correct value.

	VB:
	
 
intFileNum = FreeFile 
Open "myfilehere.sgy" For Binary Access Read As intFileNum 
Seek intFileNum, 3217 
Get intFileNum, ,bytTemp 
Debug.Print bytTemp 
Close intFileNum 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Using the MS Article as an example with the Random Class, this returns nothing or a ? What am I doing wrong here? Is it because the file is being open as random instead of binary? I'm not sure.

	VB:
	
 Random 
Dim Temp As Variant 
Set F = New Random 
F.OpenFile "myfilehere.sgy" 
F.SeekAbsolute 0, 3217 
Temp.ReadBytes(1) 
Debug.Print Temp 
F.CloseFile 
Set F = Nothing 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you for your time.

Hi! How I could manage drop counter to 0 when condition is met and start count again. Please see attached file. Any suggestions?

I am trying to create a program that does similar things to another program. One thing I need to do is to plot digital data (binary 1s and 0s). Please see the attached samples. The word document shows a sample plot from the program I am trying to imitate. The spreadsheet has all the sample data used to come up with the plot in the word document. In the plot, when the lines are bold, the digital element is picked up (binary 1), and when they are thin, the digital element is not picked up (binary 0). Ideally, the plot that I create in Excel with VBA will look at least somewhat like the sample plot I have provided. Thanks in advance for your help!

I posted this on ExcelForum.com and got a response on how to do it manually. Does anybody have other options, in particular with VBA? I am new to VBA and on a deadline to get this done, otherwise I would try to dig deeper into it myself. One other thing: the digitals to be plotted will come from a listbox where the user picks which digitals to plot (there could be tons of them). I had gotten help on ExcelForum.com to plot voltage and current waveforms from a listbox, so I figured that my answer to plotting the binary numbers is somewhere in between the two posts. Please see the links to my ExcelForum.com posts for the sample documents. Thanks in advance for your help!

http://www.excelforum.com/excel-prog...s-via-vba.html
http://www.excelforum.com/excel-prog...a-listbox.html

I'll try to explain the best I can.


	VB:
	
 Hyperlink) 
    If Target.Address = "/public" Then 
        Range("$C$16") = Range("C16") + 1 
    End If 
    If Target.Address = "/private" Then 
        Range("$F$16") = Range("F16") + 1 
    End If 
    If Target.Address = "/private" Then 
        Range("$I$16") = Range("I16") + 1 
    End If 
    If Target.Address = "/public" Then 
        Range("$L$16") = Range("L16") + 1 
    End If 
    If Target.Address = "/public" Then 
        Range("$R$16") = Range("R16") + 1 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code works fine but here is the thing:

Im trying to create a click counter to track what hyperlinks are clicked in my excel document.
If my excel document is located on the "public" drive, the only click counters that work are the ones located on the "private" drives.
For example: If my excel document containing the code above is located on the "public" drive the only IF statements that work are 2nd and 3rd.

If I copy the document and place it on the "Private" drive the only IF statements that work are the 1st, 4th, and 5th.

Creating a shortcut to files on the "public" drive and placing them on the "private" drive, (and/or Vica Versa), and hyperlinking to them does not work because the document thinks the shortcut may be unsafe.

I have macros referencing a group of merged cells that contain the hyperlink. The macros are placed on a image/picture.

So basically whats happening is:

Someone clicks a picture that has a macro on it, that macro references a group of cells that contain a hyperlink. Once clicked the counter increases by one.

Im just wondering why the click counters only work on some of the hyperlinks, depending on what drive my excel file is located on. If I flip the drives, the ones that previously didnt work, now work, and the ones that previsouly worked, no longer work.

I hope that makes sense.

Let me know if you need any clarification.

Thanks

Esteemed Group!

I am in need of the Excel VBA code, (2007 / 2010), for a binary search tree.

I am searching 300,000 > 400,000 parent records for subsets of 5 to 10 child records and the basic Excel search just takes to long.

I have tried to generate my own VBA / BST - but - so far - I have simply burned off the wings and crashed -

Any / all assistance is greatly appreciated!!

Many Thanks In Advance

Choppork
0156 CDT
November 6, 2011

When we install programs, there is a bar with some kind of counter that indicates the % left to finish the work (10% done, 20% done, etc)

What is the best way to do that using vba for excel? I need to execute several operations in many rowns, and I need to indicate to my user how many rows are left, etc

thx all

Hi Guys,

I've got a little bit of logic problem more than an excel problem. This is what I'm trying to accomplish. There is a business process that takes 30 days. The process is broken up into 6 stages. Each stage is allowed 5 days to be completed. I'm trying to track how long the process takes to happen and flag which stage goes over 5 days.
My current plan is that I have a project start date in a certain cell. I want Stage 1 to automatically start increasing everyday by itself. When stage one is complete, the user will input a zero into stage two. This stop the counting for Stage 2 and Stage 2 will automatically start counting the number of days until the user inputs a zero into the next.

I can do this using C# or Java, using simple loops and counters. I'm just finding it hard to incorporate this into excel. Would Access help my situation better?

This is my thought process in pseudo

if(next stage cell == null)
current date - start date %using Days 360 function
if(next stage cell !=null)% therefore equalling 0
stop counting current stage and hold value
start counting next stage cell. % this is the part i'm having trouble with

I know i'm asking for a lot, but I really just want a few functions that could help, or the green light to move onto another program like Access.

I want to calculate Between the two account (sheet1=ReceiverAccount and SenderAccount) how many times the money transfer was done (archive sheet= ReceiverAccount and SenderAccount) with the counter. Sheet1 shows current data. Sheet2 shows archive data. I need to macro code. Sample files in the following link http://www.2shared.com/file/jxvUqZ5Z/account.html

Thank everyone who helped.

You lose anything saving your file as a binary format? There any bugs or known issues with binary format vs the standard?

Thanks

I am trying to get a row counter in column B, except for it to reset back to 1 after a determined number that is set in another cell value.

For example:

The reset number=5

then column B would look like:

1
2
3
4
5
1
2
3
4
5
1
2
3

Except my range in column B is Range("b2:b4176"), and the range for the reset number is Range("ResetNumber").Value

I just don't know how to code this for it to run within an existing macro. I have already set the ranges. Any help is appreciated. Thanks!

I have a complaint form that is used at the central office. It has a counter that is refreshed to the new number every time the form is opened. It also has today's date on the form. I would like the counter to automatically reset itself to zero on 01/01/??. I have a limited amount of experience on VB coding, so any help is appreciated.

bwedmund

On Column A, I have week number information such as 523, what I want to do is on Column B, generate the filename using the format "CH20" & Column A & Counter, where Counter should start from A up to Z, after the last letter of the alphabet the counter should begin with 1. For example CH20523A, CH20523B... CH20523Z... CH205231, CH205232... CH20523100. Can this be done using formulas or VBA?

My scenario is as follows:-

I need to search a directory where the workbooks have been saved with names such as :-

001 - General.xls @ 14 Sep 2004
002 - General.xls @ 14 Sep 2004
etc.

I then need to automatically increment the counter at the beginning by 1, so the next book in my example above would be saved as:-

"003 - General.xls @ 12 Oct 2004".

The leading zeros are important, I'm afraid.

Here is what I have so far :-


	VB:
	
 Opening() 
    On Error Goto fin 
    If Range("a1") = ("CLAIM_REFERENCE") Then 
        Range("A1").Select 
        ActiveSheet.Paste ' If blank template then paste & cut data in to it
         
         'Insert the sort data macro here
        Application.ScreenUpdating = False 
        Application.StatusBar = "Creating the working copy." 
         '
        Sheets("Sheet1").Select 
        Sheets("Sheet1").Copy Before:=Sheets(2) 
        Sheets("Sheet1 (2)").Name = "working copy" 
         'etc etc rest of code
         
        Application.StatusBar = "Saving the workbook." 
         '
        Mo = Month(Now()) 
        Da = Day(Now()) 
        If Mo = 0 Then 
            Mo = 12 
            Yr = Format(Now(), "YYYY") - 1 
        Else 
            Yr = Format(Now(), "YYYY") 
        End If 
         
        Select Case Mo 
        Case 1 
            MMM = "Jan" 
        Case 2 
            MMM = "Feb" 
        Case 3 
            MMM = "Mar" 
        Case 4 
            MMM = "Apr" 
        Case 5 
            MMM = "May" 
        Case 6 
            MMM = "Jun" 
        Case 7 
            MMM = "Jul" 
        Case 8 
            MMM = "Aug" 
        Case 9 
            MMM = "Sep" 
        Case 10 
            MMM = "Oct" 
        Case 11 
            MMM = "Nov" 
        Case 12 
            MMM = "Dec" 
        End Select 
         '
         ' Save workbook
         '
        ChDir ":INMCULondon BranchClients D-FFelps" ' change to correct directory
        fName = " - General " & " @ " & Da & " " & MMM & " " & Yr & ".xls" 
        ActiveWorkbook.SaveAs FileName:="I:NMCULondon BranchClients D-FFelps" & fName, FileFormat:=xlNormal, _ 
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ 
        CreateBackup:=False 
         
    Else 'If Template already saved as new workbook then do nothing
        Range("A1").Select 
    End If 
    Exit Sub 
fin: 
    MsgBox "There has been an error (Check folder exists?)" 
    ActiveWorkbook.Close False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help greatly appreciated

Thanks

Lee

Is there a faster way to do this? I'm trying to compare files on a DVD with files on my hard drive to confirm the backup before I delete. Everything is working great, but this function I wrote is REALLY SLOW! I wrote this in PHP as well, because there are more powerful compare functions, but the lack of an updateable GUI made the project impossible for a large quanity of files.

Here's the function. As you can see, I'm stuck at a point where I'm looping through the byte array one piece at a time to do the comparison. Is there a way to move all that data into a string, so then two strings could be compared in one shot? I have to do it in chunks, because some files might be very large and I don't want to tie up the memory.


	VB:
	
 
     'This function will NOT compare files larger than 2 gb,
     'or 2,147,483,647 bytes (2^30*2).
     
    Dim objFileSystem 
    Dim z As Long 
    Dim fnumber1 As Integer, fnumber2 As Integer 
    Dim filesize1 As Long, filesize2 As Long 
    Dim BLOCK_SIZE As Long 
    Dim testfail As Boolean 
    Dim bytesRead As Long 
     
    Set objFileSystem = CreateObject("Scripting.FileSystemObject") 
     
    If Not objFileSystem.FileExists(sPath + filename) Then 
        binaryCompare = 4 'Returns code for Source File Does Not Exist
        Exit Function 
    End If 
     
    If Not objFileSystem.FileExists(dPath + filename) Then 
        binaryCompare = 5 'Returns code for Compare File Does Not Exist
        Exit Function 
    End If 
     
     'Set the size of the chunk of data to compare at one time.
     'Not used if the file size is smaller.
    BLOCK_SIZE = (2 ^ 20) * 10 'Ten megs, or 10,485,760 bytes
     
    fnumber1 = 1 
    fnumber2 = 2 
     
    Open sPath + filename For Binary As #fnumber1 
    Open dPath + filename For Binary As #fnumber2 
     
    Redim bdata1(BLOCK_SIZE) As Byte 
    Redim bdata2(BLOCK_SIZE) As Byte 
     
    filesize1 = LOF(fnumber1) 
    filesize2 = LOF(fnumber2) 
     
     'Update progress bar control passed on call.
    With pbObj 
        .Min = 0 
        .Value = 0 
        .Max = filesize1 
    End With 
     
     'Compare sizes of the two files.
     'If the sizes are not equal, what's the point of a comparison?
    If filesize1  filesize2 Then 
        binaryCompare = 1 'Returns code for File Size Mismatch
        Exit Function 
    End If 
     
    Do While filesize1  bytesRead 
        pbObj.Value = bytesRead 
        DoEvents 
         
        If filesize1 - bytesRead < BLOCK_SIZE Then 
             'Read last chunk
            bdata1 = InputB(filesize1 - bytesRead, fnumber1) 
            bdata2 = InputB(filesize1 - bytesRead, fnumber2) 
            bytesRead = filesize1 
        Else 
             'Read a chunk
            Redim bdata1(bytesRead) As Byte 
            bdata1 = InputB(BLOCK_SIZE, fnumber1) 
            bdata2 = InputB(BLOCK_SIZE, fnumber2) 
            bytesRead = bytesRead + BLOCK_SIZE 
        End If 
         
        testfail = False 
         
         'Compare each byte in arrays. VERY SLOW!
         'There HAS TO BE a better way!!!
        For z = 0 To UBound(bdata1) 
            If bdata1(z)  bdata2(z) Then testfail = True 
        Next z 
         
        If testfail = True Then Exit Do 
    Loop 
     
    Close #fnumber1 
    Close #fnumber2 
     
    If testfail Then 
        binaryCompare = 2 'Returns code for Binary Comparison Failure
    Else 
        binaryCompare = 0 'Returns code for Binary Comparison Passed
    End If 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I hope someone can help. I've googled this to death for the last week.
Thanks!
Hop

Hello everyone,

I would like to create an easy-to-use basketball scorebook, where by just clicking on buttons would produce a boxscore. Attached is what I have made so far.

Problem:

How do I have a "counter button," i.e. instead of a separate cell and separate button, can I make them inclusive? When I left-click the button, the number inside the button should increment by 1 and when I right-click the button, the number inside the button should decrement by 1.

Thanks for your help everyone!!

I'm trying to write a macro that will go down row by row trying to find a page break, and when it does so, to add +1 to a counter. I'm writing a Table of Contents and need to identify the different products and what page they are on. Here's the code I have:


	VB:
	
 toc() 
     
    Sheets("CATALOG").Activate 
     
    Dim x As Integer 
    Dim y As Integer 
    Dim z As Integer 
     
    x = 0 
    y = 0 
    z = 0 
     
    Do While Cells(4 + x, 1)  "ENDOFCAT" 
        If Cells(4 + x, 1).HPageBreaks(1).Location = True Then 
            z = z + 1 
        End If 
         
        If Cells(4 + x, 1).Font.Size > 24 And Cells(4 + x, 1).Font.Bold = True Then 
            Sheets("TOC").Cells(1 + y, 1) = Cells(4 + x, 1) 
            Sheets("TOC").Cells(1 + y, 2) = z 
            y = y + 1 
            x = x + 1 
        Else 
            x = x + 1 
        End If 
    Loop 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The issue comes with the line Cells(4 + x, 1).HPageBreaks(1).Location = True

How do I write this line of code so that VBA recognizes what I'm trying to write?

Thanks a lot for your help!

I was reading this post on this site, however i am unable to reply to the thread.
This code is exactly what i was looking for but have the need for it to be able to give the result as a number of bits eg. i would like the result to be dispalyed as 13 bits.
I have tried modifying the code without any success and would like to be able to use it in this form.
Public Function Dec2Base( Num As Long, base As Long, length as long) As String

Has anyone any ideas on how to modify the code to acheive this, as i generally need to convert to binary upto 20 characters.

good day

i need a helo in my code
i tried to copy the sheet to new workbook then delete the inserted rows from row # 7 upto PC (counter )


	VB:
	
 
Sheets(Array(Sheet2.Name, Sheet3.Name)).Copy 
ActiveWorkbook.SaveAs Filename:="d:Documents and SettingsmmajroubDesktopVBAOutput.xls" 
ActiveWorkbook.Close 
 
N = 7 
With Sheets("sheet2") 
    For r = PC To 7 Step N - 1 
        Selection.Rows(r).EntireRow.Delete 
    Next r 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

but it doesn't delete

Hi,

Before I go off barking off down the wrong track, I would like some advice as to the direction I should take for the current project.
The data collected will represent the number of clinical notes correctly completed by Profession and Centre. One row per MRN (Client identifier).

At this stage, I plan to create the data in this format (either thru Excel forms(another challenge) or an Access database)


	VB:
	
 
MRN     Centre     Profession     Yes   No   N/A      Year 
66666   East       Nursing         1                        2006 
55555   East       Nursing         1                        2006 
44444   East       Nursing              1                   2006 
33333   East       OT              1                         2006 
11111   South     Nursing          1                       2006 
22222   South     Nursing          1                       2006 
64545   South     Nursing              1                   2006 
34343   South     OT              1                         2006 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I wish to display the percentage of cnotes filled out for each profession, by various combinations of Centre and Profession. Tracking results across years will be included in future.
I have been tooling around with Pivot charts and am getting some reasonable results. However, for this to work effectively, my users would need to know Pivot charts as well....
I have attempted to use normal charting to no avail, presumably becasue of the binary nature of the Yes, No, N/A data.
I would dprefer to programmatically produce Charts when the Users hit a command button...I have this under control, but am worried about the binary stuff. So, for normal charting to work, do I have to collate the table into Summary data...or is there some mechanism in Charting that can sum for me.
Or, have I got this data format wrong.

Converting from Hexadecimal to decimal in VBA is trivial.


	VB:
	
) 
     ' Purpose : Provide a conversion routine for Hex numbers in VB
     ' Notes   : Only 32 bit support
     
    Dim x#, y# 
     
    x# = Val("&H" & varData) 
    If x# < 0 Then 
        If Len(varData) = 8 Then 
            y# = 4294967296# + x# 
        Else 
             ' Set the 15th bit to zero (counting from bit 0):
            x# = (x# And &H7FFF&) 
             ' Assign it to a LONG integer:
            y# = x# 
             ' Set the 15th bit back to a one:
            y# = (y# Or &H8000&) 
        End If 
    Else 
        y# = x# 
    End If 
     
    ToDecimal = y# 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But how to convert to Binary?

LS

Hi,

I've been mulling over a problem. I'm trying to select a range for editing defined by the consistency of other cells. I've managed to write a do while loop with a counter. I intend to use the counters value to define the number of rows in the range.

I can use activecell with the counter to select the first cell of my range, but have been trying to use a RANGE/CELLS combo to select the range.

Here is the code that selects the cell at the top of the range I want to define


	VB:
	
 valuedependentrange() 
     
    Dim counter As String 
     
    counter = 0 
     
    ActiveCell.Activate 
     
    Do While ActiveCell.Offset(0, 0).Value = ActiveCell.Offset(1, 0).Value 
        counter = counter + 1 
        ActiveCell.Offset(1, 0).Activate 
    Loop 
     
    ActiveCell.Offset(-counter, 2).Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This doesn't help me in selecting the range though, as I need the range number of rows to = counter. I have tried using a RANGE/CELLS combo but can't get it to work.

Any suggestions? Thanks in advance by the way. I have some massive but varied data sets to analyse!

Jim