Posts by Leo • 2,002 points
91 posts
-
2
votes1
answer482
viewsQ: How to delete or hide the password entry to access the data of a server with SQL Server from the VBA or Excel macro?
I’m running a database migration service from a server with SQL Server in a company, but requested that I broker the migration in Excel, because they want so in this first phase to advance some…
-
0
votes3
answers962
viewsA: How to make the automatic recalculation of Excel trigger a routine in VBA?
The same can be done for trigger a routine only when there is change in one or more cells specified in a Range. Based on one of the answers to the question How can I run a VBA code each time a Cell…
-
2
votes3
answers962
viewsQ: How to make the automatic recalculation of Excel trigger a routine in VBA?
I have a graphic scheme on Excel which is done by way of conditional formatting (see figure below), ie in the colored cells there is no content, only the background colors are changed based on…
-
2
votes2
answers2802
viewsQ: Excel/VBA file more than duplicates in size for no apparent reason while being saved
I’m working with the Excel and using some programming resources of the VBA via modules (without using forms), and there has been a problem, the file currently has 8MB and I saved continuously with…
-
0
votes1
answer341
viewsA: Is it possible to space two texts in centimeters in the same Excel cell?
The solution I present is not exactly as I would like, but solved the problem simply and directly. I added a TextBox (Activex control) at the end and on each line of the home page that can have text…
-
0
votes1
answer341
viewsQ: Is it possible to space two texts in centimeters in the same Excel cell?
As the example of the figure below. i need to tabulate in a single cell two texts so that from one cell to the other (from the same column) the texts on the right are perfectly aligned (see column…
-
0
votes1
answer102
viewsA: Perform action for each line in an SQL search - VB
There is a VBA solution that I adapted below and that can be adapted also for your case, the reference comes from a question I asked and was answered by Luiz Vieira, see the link below. How to…
-
0
votes3
answers1307
viewsA: Count elements between two quantities
To process the alphanumeric data displayed in the spreadsheet, replace "age" with: ESQUERDA(idade;PROCURAR(" ";idade)-1) This formula takes the text " years" and only the numerical part of the age…
-
0
votes1
answer132
viewsQ: Problems with Batch Backup and Specific Software
I have two problems with backups performed in different ways (they are separate and independent solutions), the first is a "batch" (BAT file) and the second is with SEAGATE DASHBOARD backup software…
-
1
votes1
answer1678
viewsA: Return all values of a matrix with conditions
The problem can be solved like this (in an unconventional way): Let’s say that column "A" contains the "Value 1" which is the customer code and column "B" the "Value 2" which is the product code,…
-
3
votes1
answer100
viewsQ: fmBackStyleTransparent does not work in Excel VBA
I have a problem in VBA of Excel, and why I searched other users as well and are unanswered so far, as in this link. Property Backstyle = fmBackStyleTransparent does not work It turns out that the…
-
6
votes4
answers2266
views -
2
votes2
answers728
viewsA: Find equal ranges in a list
To Solution in the Excel can be made like this: The cell A1 should have the value 4 (four) as it is the amount of items to compare (do not put another value here per hour, see the remarks below) The…
-
3
votes1
answer4840
viewsA: Can you take the button to close forms in VBA/Excel?
The solution I found is on the Tomas Vasquez Sites. DISABLING THE CLOSE BUTTON OF A USERFORM IN VBA This is the code: Option Explicit ' Fonte: Tomas Vasquez Sites ' '…
-
2
votes1
answer4840
viewsQ: Can you take the button to close forms in VBA/Excel?
I want to take the close button of some forms in VBA/ Excel. For example, I am customizing the presentation of some messages to differentiate from VBA options that are unattractive. In addition to…
-
1
votes1
answer3806
viewsA: How to generate Forms without title bar in VBA/Excel?
I found the solution on a national website and adapted it. The reference I took as the basis for the form without the title bar (called by header on the site) was from: Saber Excel. Excel…
-
1
votes1
answer3806
viewsQ: How to generate Forms without title bar in VBA/Excel?
I need to work with some forms without the title bar in VBA/Excel. I found many similar solutions, but all for 32 bits (mainly in old versions of VBA). The indications they gave to adapt to 64 bits…
-
4
votes1
answer5212
viewsA: Use the formula "if" to calculate a certain time interval
The Excel does not work (does operations) with negative hours, you are probably treating the time as a "string" (text format, e.g.: ="00:05"). In this case, even for a "string", the Excel…
-
0
votes1
answer1441
viewsA: VBA doubt - Runtime error '91'
The mistake 91 of VBA is: Variable Unassigned With block object or variable This type of error does not indicate where the problem occurred, so it should be in routine Limperfrmlogin. Depending on…
-
2
votes1
answer1088
viewsA: EXCEL Paint a certain amount of cells
To solve this you will need to create some rules. As there is no reference as you did, the following rules become these: 1) Set the Chart area and minimum and maximum values: Let’s assume the area…
-
1
votes1
answer1139
viewsA: VBA. I try to convert a txt to excel
So I analyzed the command ActiveWorkbook.SaveAs does not contain or does not provide for the separation of text by semicolon. Since you need to have these texts placed in the spreadsheet with the…
-
0
votes1
answer347
views -
0
votes1
answer84
viewsA: Are there security problems when implementing and using "add-ins" in VBA (Excel)?
According to the support of Microsoft on "supplements from "Excel", there may be serious safety issues with their activation. Privacy and Security for Office Supplements…
-
1
votes1
answer116
viewsA: How to find various values from a spreadsheet
You can do it in a "macro" of Excel. The code below in VBA makes the essentials of what you need and is similar to macro. The first step is to sort the datasheet according to the category you need…
-
1
votes1
answer1201
viewsA: How to return the data processing in Matrix passed as parameter for routines in VBA/Excel?
The solution is presented in the code below. The example of the code is simplified to show with small adaptations to the link…
-
-1
votes1
answer84
viewsQ: Are there security problems when implementing and using "add-ins" in VBA (Excel)?
Due to security problems with macros in VBA/Excel, I was wondering if the same occurs (or something similar) when adding a supplement and use it later in projects of VBA. This would impact a user…
-
1
votes1
answer546
viewsA: number even, odd, zero and more than 10 how to find?
As an example, I only used the variable K, can do the same for other variables if applicable. Before the "loop" zeroes the variables" Pares = 0 Impares = 0 Zeros = 0 MaioresQueDez = 0 Place this…
-
2
votes5
answers2700
viewsA: Drawing strings from a weight array
I would do in a way to seek an exact condition, which depending on the quality of the random number generator would be ideal for this case. I would simulate a "roulette", kind of one that turns with…
-
0
votes1
answer1201
viewsQ: How to return the data processing in Matrix passed as parameter for routines in VBA/Excel?
I need to pass separate matrices for a routine that sorts its elements and receive other treatments, and after that I want it to return those ordered and treated values to the original matrix; for…
-
1
votes3
answers199
viewsA: A mathematical method for knowing how many carrys in a sum
Attention, I may have misinterpreted the question and this solution may not present Carrys by mathematical method. The mathematical solution that you want and that I developed (I don’t know if…
-
4
votes3
answers4632
viewsA: What is the difference between one-dimensional and two-dimensional matrix?
One-dimensional matrix, also treated as "vector", stores data sequentially, and each data is stored and retrieved by means of an integer representing its "position" in this "row". By representing a…
-
2
votes1
answer1216
viewsA: Separate Even, Odd and Equal values in Matrix by VBA (Excel)
NOW WITH CODE READY AND TESTED! To solution presented in the code below treats each element of the matrix at a time to identify the numbers pairs and odd contained in it. In the sequence, within the…
-
1
votes1
answer1158
viewsA: Using LOG in VISUALG Functions
The problem is in the comparison, exactly in the SE, you should check if the result is integer otherwise, if you do not have a function relative to "module" (MOD) that usually captures only the…
-
1
votes3
answers199
viewsA: A mathematical method for knowing how many carrys in a sum
If I understand your problem, I believe capturing the expression as "string" and dealing with a counter, two control variables and some auxiliary variables are more efficient. My suggestion is to…
-
5
votes4
answers934
viewsA: How can I decrease program processing time?
As the variation is constant of three values and one PMU, print three variables at once and the text (whereas they are always multiples of 4 and a constant variation as shown in the example). X y z…
-
0
votes2
answers1686
viewsA: VBA/Excel Combobox : Disable Auto-Complete
In the VBA of Excel these properties give the expected result when "set" like this: MatchEntry = fmMatchEntryNone AutoWordSelect = False…
-
2
votes1
answer810
viewsA: Why do ASCII characters differ from Excel for presentation by code in VBA?
The solution was thus obtained: I did a test and put two Labels on the form and one of them changed the source to "Wigdings 3" and the other was left with "Tahoma". By code, changed the font of the…
-
2
votes2
answers4700
viewsA: How to find out if a number is odd or even?
Idem to the operator Mod, can be done like this (whereas it is a matrix): If Matriz(li, col) / 2 - Int(Matriz(li, col) / 2) = 0 Then MsgBox Matriz(li, col) & " é Par" Else MsgBox Matriz(li, col)…
-
0
votes1
answer774
viewsA: Specific Treatment for Groups of Objects inserted in the VBA Toolbox (Excel)
The solution presented below (code and form with the test result) was made using "Controls" to treat the objects of each group. Private Sub CommandButtonTeste_Click() Dim Bandeira As Variant 'Pega o…
-
1
votes1
answer774
viewsQ: Specific Treatment for Groups of Objects inserted in the VBA Toolbox (Excel)
Due to repetitive actions with groups of objects that I create to work as if they were a single object, I grouped some of these objects (right-clicking and selecting group...), and dragged into the…
-
3
votes3
answers1164
viewsA: Calculate Value Present in Javascript
David, The VPL (Net Present Value) is the value of each instalment/tranche (PMT = Payments) "brought" to zero date at interest rate (Rate) for each of the N periods (Nper = amount of periods). VPL =…
-
0
votes1
answer372
viewsA: I need help with a Solidworks macro
Use "Application.Filedialog", see the link down below. https://msdn.microsoft.com/pt-br/library/office/ff836226.aspx I used a button to trigger the routine. Sub CommandButton1_Click()…
-
2
votes2
answers640
viewsQ: Are there security problems by Auto Macro Run when calling Excel?
The link below deals with auto macro execution when starting the Excel I had the purpose of controlling the use of a VBA.…
-
1
votes1
answer64
viewsA: Divide the first record of a column by the last record
Otácio, is it to divide the dates (are numbers internally) or the value of the records? In this case the initial values of the records are zero (blank cell). Regardless of what, assuming you divide…
-
0
votes1
answer418
viewsA: Capture dynamic click checkbox event
Kelly, look at this link that the form you made is not the proper one. How to create events dynamically in VBA? As you’ll see, your routine "Novocheckbox_click" must be part of a "class module". See…
-
0
votes1
answer2823
viewsA: "Build Error" message (Loop without Do)
Try: Do ... ... Loop Until Sheets("Dados Clientes").Cells(0, 1) = "" In front of the DO don’t put anything.
-
1
votes1
answer739
viewsA: Highlight Textbox if the loaded value is equal in the worksheet
@ludehenrique2cia, See that in this part of the code you are comparing the "content" of the Textbox with the "name of the function", unless the name of the function is in the text of the Textbox,…
-
1
votes2
answers433
viewsA: How to locate next values of the same customer
@ludehenrique2cia, note that there are instructions that will not be accessed after the "Exit Sub", as in the excerpt copied below: Exit Sub Row = Row + 1 ... The "Exit Sub" leaves the routine at…
-
0
votes1
answer702
viewsA: Failure to generate TXT file by routine for printing in VBA/Excel
I found the solution, I thought generate a printer that only prints in a text file, I searched and found this link:…
-
0
votes1
answer702
viewsQ: Failure to generate TXT file by routine for printing in VBA/Excel
The form below refers to the code under VBA (Excel) presented below. This code refers to the printing of a spreadsheet on Excel by name "Imprint", as shown in the figure below. By the parameters…