dhanush's Profile

1688
Points

Questions
21

Answers
73

  • Asked on January 11, 2019 in Excel.

    Try this alternative solution :

    =CONCATENATE(
       DEC2HEX(RANDBETWEEN(0;4294967295);8);"-";
       DEC2HEX(RANDBETWEEN(0;42949);4);"-";
       DEC2HEX(RANDBETWEEN(0;42949);4);"-";
       DEC2HEX(RANDBETWEEN(0;42949);4);"-";
       DEC2HEX(RANDBETWEEN(0;4294967295);8);
       DEC2HEX(RANDBETWEEN(0;42949);4)
    )
    
    • 1466 views
    • 4 answers
    • 0 votes
  • Use this following procedure On Excel 2010 :

    1. Choose the cell and want to check in case that is used at some place in a formula.
    2. On a Formula Auditing menu, go to Formulas -> Trace Dependents.
    • 429 views
    • 2 answers
    • 0 votes
  • Try this best solution :

    consider the sheet name is “stock”, and consider infront of the cell.

    =COUNTIFS(stock!A:A,"M",stock!C:C,"Yes")
    
    • 431 views
    • 2 answers
    • 0 votes
  • This solution will explain about relative instead of Absolute paths in Excel VBA

    Workbooks.Open FileName:= ThisWorkbook.Path & "\TRICATEndurance Summary.html"
    
    • 1007 views
    • 3 answers
    • 0 votes
  • Asked on January 11, 2019 in Excel.

    This command will explain about quickest way to clear all sheet contents VBA

    Sub clear_sht
      Dim sht As Worksheet
      Set sht = Worksheets(GENERATOR_SHT_NAME)
      col_cnt = sht.UsedRange.Columns.count
      If col_cnt = 0 Then
        col_cnt = 1
    End If
     
      sht.Range(sht.Cells(1, 1), sht.Cells(sht.UsedRange.Rows.count, col_cnt)).Clear
    End Sub
    
    • 671 views
    • 3 answers
    • 0 votes
  • The best solution is here :

    Option Explicit
     
    //***** User defined type
    Public Type MyType
         MyInt As Integer
         MyString As String
         MyDoubleArr(2) As Double
    End Type
     
    //***** Testing MyType as single variable
    Public Sub MyFirstSub()
         Dim MyVar As MyType
     
         MyVar.MyInt = 2
         MyVar.MyString = "cool"
         MyVar.MyDoubleArr(0) = 1
         MyVar.MyDoubleArr(1) = 2
         MyVar.MyDoubleArr(2) = 3
     
         Debug.Print "MyVar: " & MyVar.MyInt & " " & MyVar.MyString & " " & MyVar.MyDoubleArr(0) & " " & MyVar.MyDoubleArr(1) & " " & MyVar.MyDoubleArr(2)
    End Sub
     
    //***** Testing MyType as an array
    Public Sub MySecondSub()
         Dim MyArr(2) As MyType
         Dim i As Integer
     
         MyArr(0).MyInt = 31
         MyArr(0).MyString = "VBA"
         MyArr(0).MyDoubleArr(0) = 1
         MyArr(0).MyDoubleArr(1) = 2
         MyArr(0).MyDoubleArr(2) = 3
         MyArr(1).MyInt = 32
         MyArr(1).MyString = "is"
         MyArr(1).MyDoubleArr(0) = 11
         MyArr(1).MyDoubleArr(1) = 22
         MyArr(1).MyDoubleArr(2) = 33
         MyArr(2).MyInt = 33
         MyArr(2).MyString = "cool"
         MyArr(2).MyDoubleArr(0) = 111
         MyArr(2).MyDoubleArr(1) = 222
         MyArr(2).MyDoubleArr(2) = 333
     
         For i = LBound(MyArr) To UBound(MyArr)
            Debug.Print "MyArr: " & MyArr(i).MyString & " " & MyArr(i).MyInt & " " & MyArr(i).MyDoubleArr(0) & " " & MyArr(i).MyDoubleArr(1) & " " & MyArr(i).MyDoubleArr(2)
         Next
    End Sub
    
    • 486 views
    • 2 answers
    • 0 votes
  • Use this alternative method :

          In case to keep at utilizing a stream for example. Response.OutputStrea, By using the SaveAs(Stream OutputStream) method, so now can make an Excel Package along with an null constructor.

    • 1254 views
    • 3 answers
    • 0 votes
  • Try this alternative solution :

    For the range of interest, by creating a unseen worksheet to keep the previous values .

    Private Sub Workbook_Open()
     
    Dim hiddenSheet As Worksheet
     
    Set hiddenSheet = Me.Worksheets.Add
    hiddenSheet.Visible = xlSheetVeryHidden
    hiddenSheet.Name = "HiddenSheet"
     
    //Change Sheet1 to whatever sheet you're working with
    Sheet1.UsedRange.Copy ThisWorkbook.Worksheets("HiddenSheet").Range(Sheet1.UsedRange.Address)
    End Sub
    

    After workbook is close, Delete it

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
     
    Application.DisplayAlerts = False
    Me.Worksheets("HiddenSheet").Delete
    Application.DisplayAlerts = True
    End Sub
    

    Can also changing Worksheet_Change event

    For Each cell In Target
     
       If Not (Intersect(cell, Range("cell_of_interest")) Is Nothing) Then
         new_value = cell.Value
         // here's your "old" value...
         old_value = ThisWorkbook.Worksheets("HiddenSheet").Range(cell.Address).Value
         Call DoFoo(old_value, new_value)
       End If
     
    Next cell
     
    // Update your "old" values...
    ThisWorkbook.Worksheets("HiddenSheet").UsedRange.Clear
    Me.UsedRange.Copy ThisWorkbook.Worksheets("HiddenSheet").Range(Me.UsedRange.Address)
    
    • 683 views
    • 3 answers
    • 0 votes
  •     The following command is to get the whole row while ExcelRange after it can be either iterated or for LINQ:

    for (var rowNum = 1; rowNum <= sheet.Dimension.End.Row; rowNum++)
    {
        var row = sheet.Cells[string.Format("{0}:{0}", rowNum)];
        // just an example, you want to know if all cells of this row are empty
        bool allEmpty = row.All(c => string.IsNullOrWhiteSpace(c.Text));
        if (allEmpty) continue; // skip this row
        // ...
    }
    
    • 991 views
    • 3 answers
    • 0 votes
  •     UserForm to the VBA project, by using the Non Win32 otherwise by choice add a reference to Microsoft Forms 2 Object Library, now can simplify from sheet or module

    With New MSForms.DataObject
    .SetText "http://zombo.com"
    .PutInClipboard
    End With
    

    • 663 views
    • 3 answers
    • 0 votes