Posts by Rob Xaos

Re: sumif based on the last 2 characters of corresponding cell in another column

At Ozgrid we ask that members do not add [Solved] or similar to threads, because it makes it harder to search for threads associated with the Solver function of Excel.

Re: #Value Error with protected sheet with UserInterFaceOnly True

Mike,

Thanks for that. I'm still not familiar with all the wrinkles to what a UDF can and can not do.

Re: Rewrite recorded Macro for Pivot Table update

Gaurangpatel,

Firstly, Macro help is not an acceptable thread title at Ozgrid. Thread titles should use Search Friendly Key Words only and give a concise description of the problem. They should allow another member to find this thread when searching for a solution to a similar problem.

Secondly, all VBA code must be wrapped with [noparse]

Your code here

[/noparse] tags. I have added them for you this time.

Please take note of these comments as further infractions may affect your posting rights.

Thank you.

Re: #Value Error with protected sheet with UserInterFaceOnly True

A UDF cannot alter the Workbook in any way. It can only return a value to the formula/cell which calls it. See: http://www.ozgrid.com/VBA/Functions.htm

Re: AVAYA CMS Scripting through VBA

tekilaman,

You have posted in what is basically a 4+ year old thread, the only recent posts here should not have been made but were not caught earlier by a Moderator. Ozgrid forum rules are one question (or close follow-ups) per thread, by the original poster only.

Please start your own thread and link to this one if it helps explain your situation.

Thank you.

Re: Read Excel workbook to database

I am a little short of time at the moment so I don't have time to clean these up to be more generic, but you may find the following two bits of code useful:

Option Compare Database
Option Explicit
'------------------------------------------------------------
' Import_FPS
'
'------------------------------------------------------------
Public Sub Import_FPS()
    Dim strFullPathAndFileName As String
 
    On Error GoTo ErrorHandler
 
    strFullPathAndFileName = BrowseFiles("Import FPS Data as .csv")
    If strFullPathAndFileName <> vbNullString Then
        DoCmd.Echo False
        DoCmd.SetWarnings False
        'Create t_Data_FPS_Import as temporary table
        DoCmd.RunSQL _
            "CREATE TABLE tblData_FPS_Import (Data_Set_FPS_ID Long, Time_ID Counter, FPS Long);", False
        'Import .csv file
        DoCmd.TransferText acImportDelim, _
            "FPS Import Specification", _
            "tblData_FPS_Import", _
            strFullPathAndFileName, True, ""
        'Update t_Data_FPS_Import.Data_FPS_ID with Forms!frm_Import_Test!cmbDataSetFPSID
        DoCmd.RunSQL _
            "UPDATE tblData_FPS_Import " & _
            "SET tblData_FPS_Import.Data_Set_FPS_ID=Forms!frmTest_Import_FPS!ctlDataSetFPSID.Value;", False
        'Insert  tblData_FPS_Import into  tblData_FPS
        DoCmd.RunSQL _
            "INSERT INTO tblData_FPS SELECT tblData_FPS_Import.* " & _
            "FROM tblData_FPS_Import;", False
        'Delete temporary t_Data_FPS_Import
        DoCmd.DeleteObject acTable, "tblData_FPS_Import"
    End If
Import_FPS_Exit:
    DoCmd.Echo True
    DoCmd.SetWarnings True
    Exit Sub
ErrorHandler:
    MsgBox Err.Description
    Resume Import_FPS_Exit
End Sub

Option Compare Database
Option Explicit
'
' From: [URL]http://www.tek-tips.com/viewthread.cfm?qid=1289131[/URL]
'
'   Me!TextboxName = BrowseForFile()
'   variable = BrowseForFile()
'
Private Const VER_PLATFORM_WIN32_NT = 2
Private Type OSVERSIONINFO
   dwOSVersionInfoSize As Long
   dwMajorVersion As Long
   dwMinorVersion As Long
   dwBuildNumber As Long
   dwPlatformId As Long
   szCSDVersion As String * 128
End Type
Private Declare Function GetVersionEx Lib "kernel32" Alias "GetVersionExA" _
                        (ByRef lpVersionInformation As OSVERSIONINFO) As Long
Private Declare Function GetFileNameFromBrowseW Lib "shell32" Alias "#63" _
                        (ByVal hwndOwner As Long, _
                         ByVal lpstrFile As Long, _
                         ByVal nMaxFile As Long, _
                         ByVal lpstrInitialDir As Long, _
                         ByVal lpstrDefExt As Long, _
                         ByVal lpstrFilter As Long, _
                         ByVal lpstrTitle As Long) As Long
Private Declare Function GetFileNameFromBrowseA Lib "shell32" Alias "#63" _
                        (ByVal hwndOwner As Long, _
                         ByVal lpstrFile As String, _
                         ByVal nMaxFile As Long, _
                         ByVal lpstrInitialDir As String, _
                         ByVal lpstrDefExt As String, _
                         ByVal lpstrFilter As String, _
                         ByVal lpstrTitle As String) As Long
Public Function BrowseForFile(ByRef strTitle As String) As String
   Dim strSave As String
 
   strSave = Space(255)
   'If we're on WinNT, call the unicode version of the function
   If IsWinNT Then
      GetFileNameFromBrowseW Screen.ActiveForm.Hwnd, _
                                StrPtr(strSave), _
                                255, _
                                StrPtr("c:\"), _
                                StrPtr("txt"), _
                                StrPtr("FPS files (*fps.csv)" & Chr(0) & "*fps.csv" & Chr(0) + _
                                    "All files (*.*)" & Chr(0) & "*.*" & Chr(0)), _
                                StrPtr(strTitle)
   'If we're not on WinNT, call the ANSI version of the function
   Else
      GetFileNameFromBrowseA Screen.ActiveForm.Hwnd, _
                                strSave, _
                                255, _
                                "c:\", _
                                "txt", _
                                "FPS files (*fps.csv)" & Chr(0) & "*fps.csv" & Chr(0) & _
                                "All files (*.*)" & Chr(0) + "*.*" & Chr(0), _
                                strTitle
   End If
 
   BrowseForFile = Trim(Replace(strSave, Chr(0), " "))
End Function
Public Function IsWinNT() As Boolean
   Dim myOS As OSVERSIONINFO
 
   myOS.dwOSVersionInfoSize = Len(myOS)
   GetVersionEx myOS
   IsWinNT = (myOS.dwPlatformId = VER_PLATFORM_WIN32_NT)
End Function

The second piece of code is simply a file browser. The first pulls a CSV file into a temporary table and then updates it onto an existing table.

You should be able to adapt these to suit your exact situation. You may not need to create a temporary table in your case but simply update the new CSV file onto the existing table

Re: Macro paste special - Compile Error

Quote from Ger Plante;530089

It seems I am invisible.

How have you been Batman? Havent seen you around these parts in a while.

Ger

Seems we have two Super Heros in this thread. Batman and the Invisible Man

Re: Speed up slow calculation in vba

Just to chip in here with some further ideas about optimisation.

There are times when

Quote

It has to run using code

but in my experience, people often reach for VBA too soon. VBA is excellent when used correctly and does solve problems that cannot be done even by the most complex of formulas. However if used incorrectly it can bring with it huge performance issues.

I would look again at your calculations and see if there is a way to perform some of the terms of the calculation as formulae. The less that has to be done by VBA the better. These partial terms can be hidden either in hidden columns, rows or even on a separate 'pre-calculation' sheet. Try to take an approach that minimises the amount of processing the VBA is required to do.

A brief skim of the code to me shows no clear reason why it has to be in VBA. You perform a number of calculations which are used as parameters for the financial function MDURATION the result of which is assigned to the ActiveCell. On the surface this looks like it could all be done as a formula.

As a side note I would avoid having your own procedure with the same name as the built-in financial formula and I would avoid having a variable within that procedure also with the same name. Although this compiles and runs it makes it difficult to read.

Re: Receiving "compile error: invalid outside procedure"

Doc Grimm,

Please take note of the changes made to your post. Firstly, any VBA code must be wrapped with [noparse]

Your code here

[/noparse] tags. Secondly please use Search Friendly Key Words only for titles, including Please Help.. and similar words makes it more difficult to search for appropriate threads.

Both the pieces of code you have posted are identical. Neither have a correct procedure header (Sub <procedure name> or Private Sub <procedure name>).

As Cytop mentions the code appears to be attempts at cracking a Worksheet password, you will need to explain in more detail as to how this situation has arisen.

Re: Excel crashes when opening multiple documents

Try my suggestion above about changing the error trapping and see if that gives further insight. Report back if that raises errors and where they occur.

Re: Network/Collaboration Chart?

Your image is a bit small to be legible, so it might be better if you provided a link to the chart on the site you mention. A brief glance says that it may be the kind of thing that could be done in Excel but it would require quite a lot of programming.

In Word it would be horrendous so I will move this thread to the Excel forum where it may get a bit more exposure.

Re: Excel crashes when opening multiple documents

This may be very hard for us to diagnose, given the nature of the problem. If there are more class methods then you really need to upload all of them. Don't worry about the adverts they are always on the first and last posts of a page.

One thing I did notice is that in functions: PreHeating, CW and HW you are declaring Cell as a Range but not setting it to Nothing at the end. Technically Cell is an object so this is probably a small memory leak. If you have anything similar but inside a loop then this may be the problem. If you want to be 100% safe then every declared variable should be set to Nothing before leaving a function/procedure.

Something else to try is to set the VBE to break on all errors: Tools > Options > General > Error Trapping. That may enable you to catch an error in the class module that is causing a problem but not being highlighted until it has killed Excel.

Re: Cell highlighted once select the combo box item.

It would be a good idea to upload a copy of your Workbooks (after removing/disguising any confidential information).

Please use Edit Post > Go Advanced > Manage Attachments

Re: Conditional formatting, Highlighting of row A to row F when of active cell

st3pfi,

Please note that you must use [noparse]

[/noparse] tags when including VBA in your posts. Please see the Tags link in my signature for more detail. I have added the tags for you this time only.

Re: Excel crashes when opening multiple documents

User classes are a common source of memory leaks so would be an idea to include the class.

Is there any error message when Excel crashes?

Re: Excel 2007 - LogIn Logout

You will need to post your code. It is not possible to provide a solution without knowing what it is you are already doing. Be sure to use [noparse]

[/noparse] tags when posting VBA.

Re: RandBetween Number Generator For Statistics

It is unclear to me why you would want to run such basic tests on the Excel random function. When it was revised several years ago by Microsoft, extensive testing was carried out using the DIEHARD series of tests as well as additional tests from NIST.

If you want more details about the algorithm used and the testing it underwent then see: http://support.microsoft.com/kb/828795

The kind of tests you are describing in this thread do not provide any real analyis of the quality of the RANDBETWEEN function.

Re: Read Excel workbook to database

i think the problem is too open ended to provide a solution. It will depend entirely on how the data is configured in each Workbook/Worksheet.

Unless you can describe how there are similarities between the data in the different Workbooks then it is unlikely there will be a generic solution.

Re: Select different ranges using IF formula

You need to explain what function the formula in S6 is performing on the range. Also please confirm that in:

Quote

I then want the same cell (S6) to use cells 'K2 through K7' 'IF' cell S6 is then 26% to 50% etc ....

S6 should be M3 since it is not possible to perform a calculation for a cell based on the value of the same cell.

Re: Countif formula for multiple cells and sheets

I have already asked you in this thread to use code tags correctly. You have not taken note and have once again posted code with incorrect tags.

You must edit your post to correct this otherwise you post will be locked as will the other post you have just started.