Edittin Macors in EXCEL

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - Regarding getting references that can be applied towards ISV Competency Hi: We are an ISV that has developed a financial software using .NET. We are currently in the process of certifiying this product for Windows Vista. I had a little confusion over getting customer references for the purposes of getting the ISV Competency: The MSPP Program Guide mentions that we can get references for projects that we have done, but since we are primarily a product company, I am not sure what "projects" means in the context of ISVs (since we don’t do custom projects). Can we simply get references from 3 unique customers of our product and apply them towards getting the Competency Regards, Himanshu Tag: Edittin Macors in EXCEL
  - 2
    - Display information as a whole Hi. I'm having trouble displaying the RFID tag's infromation. The program display like this: PC > Reader: 07 FF B0 01 00 1C 56 PC < Reader: E0 05 40 00 00 5C 2B 34 PC > Reader: 11 FF B0 23 01 E0 05 40 00 00 5C 2B 34 00 01 1F 21 PC < Reader: E0 05 40 00 00 4C 2B 34 PC > Reader: 07 FF B0 01 00 1C 56 PC < Reader: 35 64 64 61 PC > Reader: 11 FF B0 23 01 35 64 64 61 00 01 22 61 PC < Reader: 35 64 64 61 This second portion in red is wrong as it 'loop' back with the tag content in blue. While the first portion in orange should not suppose to be appear in there. That was where the tag content was suppose to be in. The display should display like this: PC > Reader: 07 FF B0 01 00 1C 56 PC < Reader: E0 05 40 00 00 5C 2B 34 PC > Reader: 11 FF B0 23 01 E0 05 40 00 00 5C 2B 34 00 01 1F 21 PC < Reader: 35 64 64 61 This should be the correct arrangement. My program is suppose to loop back these information(tag ID, CRC and content may varies) at a delay of 100mS. Here's the code that controls the display: Code Snippet Option Explicit Dim sbuffer As String Public crc_output As String Public crc_output2 As String Public InvertCRC As String Public buflen As Byte 'Temporary Public variables Public tagIDbuffer As String Public tagCRCbuffer As String Public Sub Display_Msg(sMsg As String) frmMain.txtDisplay.Text = frmMain.txtDisplay.Text + sMsg End Sub Public Function Hex2Str(Hex_String As String) As String Dim iPos As Integer Dim iTemp As Integer Dim b As String Dim g As String Dim sMsg As String 'Tag ID value Dim temp1 As String 'CRC value appended to the relevant tag 'Temporary variables Dim tagID As String Dim tMsg As String 'Convert the tag ID hexadecimal number to string format. For iPos = 8 To Len(Hex_String) - 2 iTemp = Asc(Mid$(Hex_String, iPos, 2)) If iTemp < 17 Then sMsg = sMsg + "0" g = iTemp b = b & Chr$(g) sMsg = sMsg + Hex(iTemp) + " " Next iPos frmMain.txtDisplay.Text = frmMain.txtDisplay.Text & _ "PC -> Reader: 07 FF B0 01 00 1C 56" & vbCrLf frmMain.txtDisplay.Text = frmMain.txtDisplay.Text & _ "PC <- Reader: " & sMsg & vbCrLf sbuffer = Chr$(&H11) + Chr$(&HFF) + Chr$(&HB0) + Chr$(&H23) + Chr$(&H1) + b + Chr$(&H0) + Chr$(&H1) crc_output = CalcCRC16CheckSum(sbuffer, Len(sbuffer)) temp1 = modCRC16.InvertCRC tagIDbuffer = b tagCRCbuffer = modCRC16.crcbuffer frmMain.MSComm1.Output = Chr$(&H11) + Chr$(&HFF) + Chr$(&HB0) + Chr$(&H23) & _ Chr$(&H1) + tagIDbuffer + Chr$(&H0) + Chr$(&H1) + tagCRCbuffer frmMain.txtDisplay.Text = frmMain.txtDisplay.Text & _ "PC -> Reader: 11 FF B0 23 01 " & sMsg & "00 01 " & temp1 & vbCrLf frmMain.txtDisplay.Text = frmMain.txtDisplay.Text & _ "PC <- Reader: " & sMsg & vbCrLf & vbCrLf buflen = Len(sbuffer) frmMain.Text1.Text = Len(sbuffer) End Function So sorry if I throw in a big chunk of code in it. But thanks for helping. Tag: Edittin Macors in EXCEL
  - 3
    - WSS 3.0 "This stock feature has been retired." I just started getting this error today as well, no updates on my computer, but i cannot Print to physical Printers anymore as well, but i can print to file, are either of you seeing this as well Tag: Edittin Macors in EXCEL
  - 4
    - How to activate a listbox selection using Excel VBA? Too keep this simple the code will just work on the active sheet. Create a userform with a listbox and label control. Add the following code. In the listbox will be any cells that have an error. The displayed text and actual formula will be in the listbox. Along with the cells address. When you select an item in the listbox the cell on the sheet will be selected. Also displayed is the cell address. This can be hidden by uncommenting the ColumnWidths property in the userform initialize event. Private Sub ListBox1_Click() Dim strAddress As String strAddress = ListBox1.List(ListBox1.ListIndex, 2) ActiveSheet.Range(strAddress).Activate End Sub Private Sub UserForm_Initialize() Dim ws1 As Worksheet Dim rng As Range Dim diffcount Dim Cell As Range ListBox1.ColumnCount = 3 ' uncomment once you understand how it works. ' ListBox1.ColumnWidths = ";;0" Set ws1 = ActiveSheet On Error Resume Next Set rng = ws1.Cells.SpecialCells(xlCellTypeFormulas, 16) If rng Is Nothing Then MsgBox "No errors on " & ws1.Name Exit Sub Else ListBox1.Clear For Each Cell In rng ListBox1.AddItem Cell.Text ListBox1.List(ListBox1.ListCount - 1, 1) = Cell.Formula ListBox1.List(ListBox1.ListCount - 1, 2) = Cell.Address Next Label1.Caption = rng.Cells.Count & " Cells Contain Errors" End If End Sub Tag: Edittin Macors in EXCEL
  - 5
    - VBA function similar to the Count(DISTINCT) function in SQL I am trying to find a function in VBA which is similar to the count(DISTINCT) function in sql. Can any one help me Tag: Edittin Macors in EXCEL
  - 6
    - Run Time Error 9 when Calling a Userform As you say that is a simple piece of code :) which is not the cause but the entry point to the problem. Can you check the userform itself of any code in either the Initialize or Activate event. Tag: Edittin Macors in EXCEL
  - 7
    - appactivate error How to know if the appactivate has activated a window or if it has not.... Tag: Edittin Macors in EXCEL
  - 8
    - WMI.Net 2.0 Is there any documentation on this new implementation I have done little bit experimenting with it using the reflector tool and able to create a basic instrumented applicatoin with read/write properties and few methods. The new API is much better than old one with a good fine-grained control over various aspects of WMI. I can see this model mimicing the .net configuration model so it would be an easy learning with this model. My quick test shows tha this implementation is less fragile than old System.Management one especially in-terms successive registration/un-registration in WMI repository. It worked quite well. I'm really looking forward to use this in some depth; I would be thankful If you could point me to some documentation on this Thanks, Tag: Edittin Macors in EXCEL
  - 9
    - How to get the filename into the "Insert Picture" dialogue? You can use the .Name property I think to get the name. But you wanted to set the name before showing, which you can not do. Tag: Edittin Macors in EXCEL
  - 10
    - response.setHeader in IE Hi All, I am trying to develop an web application java, thru which i can download an xml file. My code is working fine in FireFox. But the same code is not working in IE 6 & 7, the file contents are displaying in the browser itself. I am not getting any download option. Here is my code response.setContentType("application/xml"); response.setContentType("application/x-download"); response.setHeader("Content-Disposition", "attachement; filename="+ backupFileName); OutputStream os = response.getOutputStream(); try { File fileToDownload = new File(backupFileName); FileInputStream fileInputStream = new FileInputStream(fileToDownload); System.out.println("inputStream is called"); int i = 0; System.out.println("before writing"); while ((i = fileInputStream.read()) != -1) { os.write(i); } fileInputStream.close(); System.out.println("file is written n closed and redirected is false"); redirected = false; } catch (FileNotFoundException fnfe) { System.out.println("FileNotFoundExcpetion"+fnfe.getMessage()); } In the following url http://forum.java.sun.com/thread.jspa messageID=4284031 I had seen that it is a bug is IE 6. How can I resolve my problem. can u guys plz help me out to fix this problem. regards, krish Tag: Edittin Macors in EXCEL
  - 11
    - Suppress Linked View Unique Record Popup no good trying to add an index based on a view which may include several tables! :) Im guessing the overall answer is `no you cant stop it propperly` - theres no overriding function. *HINT HINT OFFICE DEVELOPMENT TEAM :)* How about a `lower level` method of linking a table in code that you can by pass the docmd command with many thanks martin Tag: Edittin Macors in EXCEL
  - 12
    - Setting the Data Format for a Column via VBA I need my datetime stamp to show both, but by default, I guess, when VBA compiles my data from SQL Server, only the date shows. I change this and save the workbook, but the format gets over-written each time the data is placed into the sheet. I hope that there is a way to change the data format for a given column via VBA after I compile by data from SQL Server...Any help on this issue would be appreciated. Thanks! Tag: Edittin Macors in EXCEL
  - 13
    - Compatibility Issue Excel 2000 and Excel 2003 Was the file unzipped into a folder with special privileges For example, if he unloaded it as an admin into a system kind of directory, then tried running the program as a restricted user. But this is starting to stretch it. If you want I'll take a quick look at it. jonxlmvp at peltiertech dot com - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ Tag: Edittin Macors in EXCEL
  - 14
    - IsError function from VBA6.3 to v6.0 I can not reproduce an error using your code and my data. Matching values are found and the correct cell updated. Non matching values get NEW in the cell. Tag: Edittin Macors in EXCEL
  - 15
    - Once copied, can we export as a GIF file? I doubt very much you can do that, or if you can it's a long difficult way to do a simple thing. If your looking to email parts of a spreadsheet your best bet is to format the cells into html/text and display them in the email message. How you go about putting the cells as html/text into an email message is another matter, if you co with copy and paste then the user needs to manually do the paste in Outlook, on the other hand if your sending the email through VBA interop with Outlook objects then all you need to do is create the email and set it's message body and address info. Tag: Edittin Macors in EXCEL
  - 16
    - How to activate a listbox selection using Excel VBA? Too keep this simple the code will just work on the active sheet. Create a userform with a listbox and label control. Add the following code. In the listbox will be any cells that have an error. The displayed text and actual formula will be in the listbox. Along with the cells address. When you select an item in the listbox the cell on the sheet will be selected. Also displayed is the cell address. This can be hidden by uncommenting the ColumnWidths property in the userform initialize event. Private Sub ListBox1_Click() Dim strAddress As String strAddress = ListBox1.List(ListBox1.ListIndex, 2) ActiveSheet.Range(strAddress).Activate End Sub Private Sub UserForm_Initialize() Dim ws1 As Worksheet Dim rng As Range Dim diffcount Dim Cell As Range ListBox1.ColumnCount = 3 ' uncomment once you understand how it works. ' ListBox1.ColumnWidths = ";;0" Set ws1 = ActiveSheet On Error Resume Next Set rng = ws1.Cells.SpecialCells(xlCellTypeFormulas, 16) If rng Is Nothing Then MsgBox "No errors on " & ws1.Name Exit Sub Else ListBox1.Clear For Each Cell In rng ListBox1.AddItem Cell.Text ListBox1.List(ListBox1.ListCount - 1, 1) = Cell.Formula ListBox1.List(ListBox1.ListCount - 1, 2) = Cell.Address Next Label1.Caption = rng.Cells.Count & " Cells Contain Errors" End If End Sub Tag: Edittin Macors in EXCEL
- Next
  - 1
    - TexBox.InputMask error message Hi all, in Access 2000 i have a form with a textbox that i set the InputMask propertie. When i "run" the form and the value i put in the text box aren't "approprite", the Access pop's up a messagebox with: "The value you entered isn't appropriate for the input mask ... , specified for tis field". Please, can someone tell me how can I capture this message or change it in Access VBA or Access option TIA Joao Rodrigues Tag: Edittin Macors in EXCEL
  - 2
    - Refining MS Access data and saving to csv file using VBA code (Answered) Hello, I have a table with these sample data pattern: Col1 Col2 001 2 002 1 002 2 002 1 003 4 003 4 003 3 003 3 003 5 The expected output in the csv file will be exactly like this: 001,2 002,4 002, 002, 003,19 003, 003, 003, 003, In the csv file, the number next to the topmost 002 is the sum of the Col2 where Col1 is 002 and the remaining field next to other 002 rows will be blank, the same process repeats for 003 and so on. Would anyone please help me to code this in VBA k Tag: Edittin Macors in EXCEL
  - 3
    - Email Form from MS Word Are you sure that you have added Microsoft Outlook 9.0 Object Library to your project.. If not please add it through Tools Menu-->References Hope it works Cheers Shasur Tag: Edittin Macors in EXCEL
  - 4
    - Excel 2003 CommandBarButton After copying your code into a Workbook_Open event handler, it successfully added a button to my toolbar upon opening the file. Isn't that what you wanted to happen Tag: Edittin Macors in EXCEL
  - 5
    - User-defined function appears to be preventing Worksheet_Change trigger? Sorry about the dropped ='s Is there anything in the routines that uses ActivityIndex Can you get the workbook back to a working state by simple not using the function Tag: Edittin Macors in EXCEL
  - 6
    - Conditional Formatting Problem/Bug? It should work fine. It seems like you're not applying the .Select property to let the code know how or where to apply the code. Try objxlSheet.Range("A1:A5").Select Then all your with block Adam Tag: Edittin Macors in EXCEL
  - 7
    - Help needed: Retrieve tables in word and copy them to Excel Code Block a = 1 For Each tTable In ActiveDocument.Tables tTable.Range.Copy With ExcelWB.Worksheets(1) .Paste Destination:=.Cells(a, 1) End With a = a + tTable.Rows.Count + 2 Next Tag: Edittin Macors in EXCEL
  - 8
    - Compatibility Issue Excel 2000 and Excel 2003 Was the file unzipped into a folder with special privileges For example, if he unloaded it as an admin into a system kind of directory, then tried running the program as a restricted user. But this is starting to stretch it. If you want I'll take a quick look at it. jonxlmvp at peltiertech dot com - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ Tag: Edittin Macors in EXCEL
  - 9
    - WSS 3.0 "This stock feature has been retired." I just started getting this error today as well, no updates on my computer, but i cannot Print to physical Printers anymore as well, but i can print to file, are either of you seeing this as well Tag: Edittin Macors in EXCEL
  - 10
    - On Error not trapping internet connection problem I want to add a toolbar button to a Word 2003 VBA project to access a web page. The code below runs fine when my internet connection is open. On Error GoTo notFound ThisDocument.FollowHyperlink _ Address:="http://www.chronolator.co.uk/Tutorials/index.htm", _ NewWindow:=True, AddHistory:=True Exit Function ' problem time notFound: Call showMessage(c8Msg5310) However, if I take my internet connection down, the On Error statement is ignored and I get a runtime error panel. The trap does work if the Address is a non-existent local file rather than an http address. Tag: Edittin Macors in EXCEL
  - 11
    - Excel: Can a UDF tell what cell it was invoked from? Just to make the information in this thread a little more useful, here's the code I finished up with... Code Snippet Dim liRow As Integer Dim liColumn As Integer Dim lsAddress as String ' Work out the row and column of the calling cell. ' The caller address field is expected to contain a string like "$H$12", ' but we need the integer equivalent values. lsAddress = Application.Caller.Address liColumn = Range(lsAddress).Column ' get the integer equivalent of $H or $AD... liRow = Range(lsAddress).Row Tag: Edittin Macors in EXCEL
  - 12
    - running a sub from another sub Hey there, I've written 3 subs to log data during import. I want to start all 3 subs by using another script but I can't get it to work. I've tried using the Run method but everytime I try to run the script it comes up with a Run-time error (7952): You made an illegal function call. Does anybody have a solution to my problem Thanks Private Sub Runmultiplesubs() Dim app As Access.Application Dim DB As Database Set app = CreateObject("Access.Application") Set DB = CurrentDb app.CurrentDb app.Run "LogELEC" app.Run "LogGAS" End Sub Tag: Edittin Macors in EXCEL
  - 13
    - Problem with PasteSpecial Hello Everybody, I am facing one strange problem in one of my VBA applications. The application uses the normal "Paste" to paste one copied signature file. I would like to use the "PasteSpecial" feature instead, so that only the text of the signature file gets inserted and not its background shade. My code snippet: Code Snippet With Selection .Range.PasteSpecial datatype:=wdPasteEnhancedMetafile End With The change works fine with a certain defect. Using normal "Paste", if 2 signature files are pasted one after the other, then the 2nd signature file comes in front of the 1st, which is correct. But using "Paste Special", I find that the 2nd signature file gets pasted on top of the 1st! This is not something that I want. When I checked the layouts of the pasted pictures of each method, I found the following: PasteSpecial -> Layout is " In Front of Text " Paste -> Layout is " In Line with Text " I am not sure if this could be the reason for this strange behavior. If so, could anyone of you experts kindly suggest how to change the layout settings in VBA to make PasteSpecial layout the same as Paste. Thanks in advance. cheers, SanK Tag: Edittin Macors in EXCEL
  - 14
    - Passing Function As Argument You can do it like this as VBA does not allow for pointers to functions. I did not know what the values of Left and Right should be. With both at zero the function runs out of stack. Sub Test() MsgBox Find_Zero("Function1", Left, Right) MsgBox Find_Zero("Function2", Left, Right) End Sub Function Function1(Value) As Double Function1 = Value - 10 End Function Function Function2(Value) As Double Function2 = Value End Function Public Function Find_Zero(Function_X As String, Left As Double, Right As Double, Optional Precision As Double = 1 / 1000) Dim Value_at_Mid As Double Dim Mid As Double Mid = (Left + Right) / 2 Value_at_Mid = Application.Run(Function_X, Mid) If Value_at_Mid <= Precision And Value_at_Mid >= -Precision Then Find_Zero = Value_at_Mid Else If Value_at_Mid * Application.Run(Function_X, Left) > 0 Then Left = Mid Else Right = Mid End If Find_Zero = Find_Zero(Function_X, Left, Right) End If End Function Tag: Edittin Macors in EXCEL
  - 15
    - Highlight Row depending on cell - Excel VBA - Hi, got my problem solved Kent Tag: Edittin Macors in EXCEL
  - 16
    - Help Hoe to write a code for a task, such that if i click at particular cell in excel then after clicking it opens a new sheet Tag: Edittin Macors in EXCEL

Ahmad Jamal

Hi

This is ahmad, My question is "How can i move one column to the right "

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 6/2/2007 by Administrator
'

'
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
it should move one column towards right before pasting
ActiveSheet.Paste

End Sub

Re: Edittin Macors in EXCEL

Andy Pope

Hi,

This will paste the range to the right of the current range.
So A1:E5 will be move to F1:J5

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 6/2/2007 by Administrator
'
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
' it should move one column towards right before pasting

Selection.Offset(0, 1).Select

ActiveSheet.Paste

End Sub

Re: Edittin Macors in EXCEL

Ahmad Jamal

Thanx andy ,

It works , I am very glad, it will save a lot of my time , Thanks Again

Regards

Ahmad Jamal